sqlserver性能调优之实战

　　当数据库面临压力时，通过性能监视器收集服务器和数据库的运行数据，使之与性能基线对比，判断数据库存在cpu、内存或io方面的压力。一般各方面的压力都是集中出现，内存的少，导致数据需要更多的交换出内存空间，意味着IO的增加，随之而来的pu运算压力就会攀升。下面介绍一下我在实际工作中解决这方面问题的常规方法。

第一步：对数据库索引碎片进行清理。通过自动化脚本重新生成碎片超过30%的索引。30%之下的索引一般不处理。并记录下清理的前索引的状态。二次清理时可先行对比。对于短期内碎片化严重的索引：

　　　　　　a、可适度降低填充因子

　　　　　　b、uniqueidentifier形成的索引建议使用newsequetialid()函数生成。该函数只能与表的defualt约束一起使用，可用临时表的方式代替select newid().

第二步：查询缓存计划中未参数化的sql语句。

　　　　例如：select * from table_A join table B on a.id=b.id where b.id>100  

　　　　　　　select * from table_A join table B on a.id=b.id where b.id>200 

上述两个sql语句仅仅参数有变化，但是执行时都消耗cpu去编译执行计划。

上述同类未参数化的sql语句可能在数据库中有成千上万的存在。如果未开启即席查询优化选项将造成大量内存的浪费。内存中需要缓存sql语句和缓存计划。开启即席查询对内存也存在一定的消耗。主要是语句如果执行两次，将解除即席查询优化。强烈建议开启即席查询优化功能。

　　　　

--查找缓存计划重复次数
SELECT  text,CP.size_in_bytes,CP.usecounts,nums=row_number() over(partition by left(text,round(len(text)/3,0)) order by CP.bucketid)
into #temp_plans
from    sys.dm_exec_cached_plans AS CP
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS ST
        cross apply sys.dm_exec_query_plan(CP.plan_handle)
where   CP.usecounts > 0
--此处参数的设置来源于非即席查询的最小缓存内存，未超过这个内存量的sql语句应复用了参数化执行计划。如果没有复用，就需要缓存执行计划，内存量将超过该区间。
        and (cp.size_in_bytes<16384 or cp.size_in_bytes>24576) 
        and text not like '(@%'  --去掉自动参数化的缓存计划

解决未参数化查询的办法：

　　　　a、使用参数化sql语句：exec

sp_executesql N'select * from WORKITEM t where t.WORKITEMID = @wid', N'@wid varchar(36)', @wid='b1e337b3-9b2a-4463-9692-7a738ebba205'

　　　　b、开启参数化强制选项。该选项开启后可能因参数嗅探，引发严重的性能问题，实为双刃剑。

 

 

 　　　　c、使用计划指南强制参数化特定的sql语句。

--计划指南会清除与之相应的缓存计划,即席查询经优化后除外(开启即席查询配置)
--计划指南可参数化查询，缩减查询的编译时间
--生成指南模板
declare @stmt nvarchar(max)
declare @params nvarchar(max)
exec sys.sp_get_query_template
N'select top 10 [MzGHDUID],[JzID],[Flag_CFW],[JzOpID],[JzDepID],[JZComplete],[StartDT],[Enddt],[ISPS],[PSQR],[SubHospitalID],[JzOpName],[JzDepName],[Name],[Sex],[Jzh],[Hyzt],[Hzxz],[ShortJzh],[GHOPtime],[Je],[YPJe],[MzInvoiceUID],[TY_YFOpID],[TY_YFOpName],[TY_YFOpTime],[TY_YSOpTime],[TYStatus],[FromJzID],[SickInfoID],[CFCompleted],[CFType],[CFTypeName],[IsKJY],[fzyy],[DBRName],[DBRXB],[DBRNL],[DBRSFZ],[DPCompleted],[GRUID],[PassFlag],[HBName],[HBType],[sHBType],[ZFFlag] 
from MZInvoice.DoctorJzView 
where GRUID=''{E3948977-DDA5-4C13-84FE-5BB620DBB721}'' 
and StartDt<''2022-11-13 21:21:31'' 
order by StartDt desc',@stmt output,@params output
select @params,@stmt

--创建指南

exec sys.sp_create_plan_guide @name = 'firstguid'           -- sysname
                            , @stmt = N'select top 10 [MzGHDUID] , [JzID] , [Flag_CFW] , [JzOpID] , [JzDepID] , [JZComplete] , [StartDT] , [Enddt] , [ISPS] , [PSQR] , [SubHospitalID] , [JzOpName] , [JzDepName] , [Name] , [Sex] , [Jzh] , [Hyzt] , [Hzxz] , [ShortJzh] , [GHOPtime] , [Je] , [YPJe] , [MzInvoiceUID] , [TY_YFOpID] , [TY_YFOpName] , [TY_YFOpTime] , [TY_YSOpTime] , [TYStatus] , [FromJzID] , [SickInfoID] , [CFCompleted] , [CFType] , [CFTypeName] , [IsKJY] , [fzyy] , [DBRName] , [DBRXB] , [DBRNL] , [DBRSFZ] , [DPCompleted] , [GRUID] , [PassFlag] , [HBName] , [HBType] , [sHBType] , [ZFFlag] from MZInvoice . DoctorJzView where GRUID = @0 and StartDt < @1 order by StartDt desc'            -- nvarchar(max)
                            , @type = N'template'            -- nvarchar(60)
                            , @module_or_batch = null -- nvarchar(max)
                            , @params = N'@0 varchar(8000),@1 varchar(8000)'          -- nvarchar(max)
                            , @hints = N'option(parameterization forced)'           -- nvarchar(max)

第三步：跟踪数据库查找耗时长的查询

　　在使用sqlserver profiler跟踪数据库时可设置duration列筛选器，过滤耗时短的查询。注意部分查询耗时长可能是因为锁的原因，在等待资源而非本身有问题。

还可以通过查询sqlserver动态管理视图的定位高消耗的查询。比如：大量的读写。下面代码可以通过变更排序列来定位不同高消耗的查询。

select  top 20 a.text,a.querytext, cpu_time=sum(a.total_worker_time)/sum(a.execution_count),--cpu耗时us
        psc_read=sum(a.total_physical_reads)/sum(a.execution_count),--物理平均读取次数
        lgc_write=sum(a.total_logical_writes)/sum(a.execution_count),--逻辑平均写入次数
        lgc_read=sum(a.total_logical_reads)/sum(a.execution_count),--逻辑平均读取次数
        平均耗时s=(sum(a.total_elapsed_time)/sum(a.execution_count)/1000/1000), 
        max(a.max_elapsed_time)/1000/1000,--单次最大执行时间
        sum(a.execution_count)--总执行次数
from (
select  CP.total_worker_time,CP.total_physical_reads,CP.total_logical_writes,CP.total_logical_reads,CP.total_elapsed_time,CP.execution_count,CP.max_elapsed_time
,substring(st.text,cp.statement_start_offset/2+1,(case when cp.statement_end_offset=-1 then len(convert(nvarchar(max),st.text))*2
else cp.statement_end_offset end -CP.statement_start_offset)/2) as querytext,ST.text

from sys.dm_exec_query_stats AS CP
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS ST
) a
group by a.text,a.querytext
order by 平均耗时s desc

 

常用的查询优化方法：

　　1、增加索引，减少表扫描。该方法使用较大，典型的以空间换时间的办法。可通过重复执行有问题的查询，观察实际执行计划找到表扫描的点。ssms会有增加索引的提示，仅供参考。

　　2、增加索引提示，并行度，强制连接顺序等提示，优化执行计划。sqlserver面对复杂的查询有时无法做出正确的选择，提示可有效地控制执行计划的生成。

执行计划的控制可大致分为：查询提示（option），连接提示（loop join,merge join，hash join）,表提示（with）

　　3、减少数据表关联。用临时表进行拆解。过多的表关联将加大编译执行计划的负担，导致生成不理想的计划。sqlserver优化器需要在优化时间和计划效率两方面权衡，不可能无限制的去探索最优的执行计划。

　　4、避免对索引列的运算，如datediff(day,usedate,@date)=0。虽然userdate存在索引，优化器还是可能存在运算而方式使用。

　　5、避免使用低效的in,exsist等子查询；建议通过join 或者left join 等关联符代替。

　　6、重新生成统计信息。可开启新的统计信息更新阈值配置。详情见微软官网：https://learn.microsoft.com/zh-cn/sql/relational-databases/statistics/statistics?view=sql-server-ver16

　　7、将堆表转变成聚集索引表，以优化查询计划中的lookup；

　　8、重写业务逻辑。去掉非必要的查询。这是非常好的办法，从数据流程反观业务流程，有时能获得出其不意的优化方案，使得业务流程更简洁，数据查询更高效。