语句调优 章节问题定位和解决方法 参数嗅探的自动化处理调用SQL Agent

语句调优 问题定位和解决方法 参数嗅探的自动化处理调用SQL Agent

摘自《SQL2012实施与管理实战指南》

--语句调优 -问题定位和解决方法 参数嗅探的自动化处理
--18章 参数嗅探 P813

--客户是一个世界知名的赌场，系统需要7*24小时运行
--存储过程非常多

--客户希望索引的动作不需要人来干预而是自动完成的，很显然，上面提到的所有方法都
--不能符合客户的要求

--1、用exec（）的方式运行动态SQL
--2、使用本地变量（local variable）
--3、query hint+“recompile”
--4、query hint+指定join运算
--5、query hint+optimize for
--6、plan guide

--DMV：sys.[dm_exec_procedure_stats] 返回缓存存储过程的聚合性能统计信息。
--该视图为每个缓存的存储过程执行计划都返回一行，行的生存期和存储过程保持缓存状态的时间一样长。
--在从缓存中删除存储过程时，也将该视图中删除对应行

--我们可以通过执行如下语句来监控所有的存储过程的执行：

SELECT 
p.[name] AS spname,
qs.[last_elapsed_time]/1000 AS 'lastexectime(ms)',
([total_elapsed_time]/[execution_count])/1000 AS 'avgexectime(ms)',
[min_elapsed_time]/1000 AS 'minexectime(ms)',
[max_elapsed_time]/1000 AS 'maxexectime(ms)',
([total_worker_time]/[execution_count])/1000 AS 'avg',
qs.[execution_count] AS 'execcount',
qs.[cached_time] AS 'lastcachedtime',
qs.[last_execution_time] AS 'lastexectime',
([total_logical_writes]+[total_logical_reads])/[execution_count] AS 'avglogicalios',
[min_logical_reads] AS 'minlogicalreads',
[max_logical_reads] AS 'maxlogicalreads',
[min_logical_writes] AS 'minlogicalwrite',
[max_logical_writes] AS 'maxlogicalwrite'
FROM sys.[dm_exec_procedure_stats] qs,sys.[procedures] p
WHERE p.[object_id]=qs.[object_id]
ORDER BY [last_execution_time] DESC

--我们通过SQL Agent来定期执行上面的脚本，这样我们就可以获得所有存储过程的执行其概况，
--比如我们只关心最后一次执行时间最长的50个语句（只需简单修改上面的脚本即可）

--获得语句后我们可以对比最后执行时间和最大执行时间（或平均执行时间）

--只要最后的执行时间超过我们预期的值（如上述客户的系统中，所有的语句都应该在5秒内完成
--我们就可以通过sp_recompile来指示SQLSERVER重新编译该存储过程）

--上面的逻辑可以通过TSQL脚本实现。通过SQL Agent的任务来定期调用这段脚本，就可以自动对所有突然
--变慢的语句进行强制重编译，让他们获得新的执行计划

--这种方法的缺陷
--1、一旦发生参数嗅探，那么那一次执行的语句因为我们还没有感知到参数嗅探，所以不会重新编译，那次
--执行的性能也就会会非常的差，但是后续的执行应该会得到改善

--2、需要客户对自己系统所执行的存储过程有一定的了解，知道怎样让TSQL语句去判断，什么样的语句执行是不正常的