我有一个针对sql Server 2008 R2运行的查询,它连接和/或左连接大约12个不同的“表”.数据库相当大,有许多表超过5000万行和大约300个不同的表.这是一家大型公司,在全国拥有10个仓库.所有仓库都读写数据库.所以它非常大而且非常繁忙.
我遇到问题的查询看起来像这样：
select t1.something,t2.something,etc.
from Table1 t1
inner join Table2 t2 on t1.id = t2.t1id
left outer join (select * from table 3) t3 on t3.t1id = t1.t1id
[etc]...
where t1.something = 123
请注意,其中一个连接位于非相关子查询上.
问题是从今天早上开始,没有任何变化(我或团队中的任何人都知道)到系统,通常需要大约2分钟才能运行的查询,开始花费一个半小时来运行 – 当它运行时跑了.数据库的其余部分正好嗡嗡作响.我已经从它通常运行的sproc中取出了这个查询,并且我在SSMS中使用具有相同慢度的硬编码参数变量运行它.
奇怪的是,当我采用非相关子查询并将其抛入临时表,然后使用它而不是子查询时,查询运行正常.另外(这对我来说是最奇怪的)如果我将这段代码添加到查询的末尾,那么查询运行得很好：
and t.name like '%'
我从这些小实验中得出结论(可能是错误的),减速的原因是由于sql的缓存执行计划是如何设置的 – 当查询稍有不同时,它必须创建一个新的执行计划.
我的问题是：当一个曾经快速运行的查询突然在半夜开始缓慢运行时除了这一个查询之外没有其他任何因素受影响,我该如何解决它以及如何防止它在将来发生？我如何知道sql在内部做什么使它变得如此慢(如果运行错误的查询,我可以得到它的执行计划,但它不会运行 – 也许预期的执行计划会给我一些东西？)？如果这个问题与执行计划有关,那么我如何让sql不要认为真正糟糕的执行计划是个好主意呢？
此外,这不是参数嗅探的问题.之前我已经看过了,这不是它,因为即使我在SSMS中对变容器进行硬编码,我的性能仍然很慢.
解决方法
When a query that used to run fast suddenly starts running slowly in the middle of the night and nothing else is affected except for this one query,how do I troubleshoot it…?
您可以首先检查执行计划是否仍在缓存中.检查@L_301_0@,sys.dm_exec_procedure_stats和sys.dm_exec_cached_plans.如果仍然缓存了错误的执行计划,您可以对其进行分析,还可以检查执行统计信息.执行统计信息将包含逻辑读取,cpu时间和执行时间等信息.这些可以给出强烈的迹象表明问题是什么(例如,大扫描与阻塞).有关如何解释数据的说明,请参阅Identifying problem queries.
Also,this is not a problem with parameter sniffing. I’ve seen that before,and this is not it,since even when I hard-code the varaibles in SSMS,I still get slow performance.
我不相信. SSMS中的硬编码变量并不能证明过去的错误执行计划没有针对偏斜的输入进行编译.请阅读Parameter Sniffing,Embedding,and the RECOMPILE Options,获取有关该主题的非常好的文章. Slow in the Application,Fast in SSMS? Understanding Performance Mysteries
是另一个很好的参考.
I’ve concluded (perhaps incorrectly) from these little experiments that the reason for the slow-down is due to how sql’s cached execution plan is set up — when the query is a little different,it has to create a new execution plan.
这很容易测试. SET STATISTICS TIME ON将向您显示编译与执行时间. SQL Server:Statistics性能计数器还将揭示编译是否是一个问题(坦率地说,我发现它不太可能).
但是,您可能会遇到类似的问题：查询授予门.有关详细信息,请阅读Understanding SQL server memory grant.如果您的查询在某个时刻请求大量授权没有可用的内存,则它必须等待,并且它们看起来都将“缓慢执行”到应用程序.分析wait info stats将揭示是否是这种情况.
有关测量内容和查找内容的更一般性讨论,请参阅How to analyse SQL Server performance
