如何排查sql server性能问题 [英] How to troubleshoot sql server performance problem

问题描述

好的，这个一般性问题在过去 6 个月内出现了两次丑陋的头脑(不同的存储过程).我们的内部用户报告了应用程序中的超时错误.我们可以在受控环境中重现应用程序中的问题.所以我们通过使用 sp_who2 检查阻塞的正常步骤.一切看起来都很好，没有阻塞.所以我们做一个 sql trace 来准确地查看过程是如何执行的.我们将其复制到 SQL Management Studio 中的一个新窗口，并执行 sql 跟踪告诉我们 ADO.Net 正在执行的操作，并在几毫秒内完成.我们的应用程序超时是 30 秒.几个月前发生此问题时，我们使用的是 SQL Server 2005.我们现在已升级到 SQL Server 2008 R2.诊断此类问题的下一步是什么?

Ok this general problem has reared its ugly head twice in the past 6 months (different stored procedure). We have our internal users report a timeout error in the application. We can reproduce the problem in the application in a controlled environment. So we go through the normal steps of checking for blocking using sp_who2. Everything looks good with no blocking. So we do a sql trace to see exactly how the procedure is being executed. We copy that to a new window in SQL Management Studio and execute what sql trace was telling us ADO.Net was doing it and it finished in milliseconds. Our applications timeout is 30 seconds. When this problem happened a couple months back we had SQL Server 2005. We now have upgraded to SQL Server 2008 R2. Whats the next step to diagnose a problem like this?

@Martin:感谢您的回复.我会详细阅读你的帖子，让你知道我发现了什么.在此之前，这里是您请求的 SP 中的 sql:

@Martin: Thanks for the response. I will read your post in detail and let you know what i found out. Until then here is the sql in the SP you requested:

Select 
    @Exists=0, 
    @EarnRecId=0,
    @SuppStatusId = 0,
    @SLRecId = 0,
    @EarnRecDS = Null

Select
    @EarnRecId = er.EarningsId,
    @EarnRecDS = Convert(Varchar(26),er.Datestamp, 109),
    @SuppStatusId = s.SuppStatusId,
    @SLRecId = s.SLId
From
    Tracking tr
    Inner Join Supps s On s.SuppId = tr.SuppId
    Inner Join Earnings er On er.EarnRecId = s.SuppId
Where
    tr.ClaimId = @ClaimId
    and er.FiscalYr = @FiscalYr
    And er.EmplyrId In (@EmpId1,@EmpId2)

If @EarnRecId > 0
    Begin
        Set @Exists=1
    End

推荐答案

可能是参数嗅探.

当调用存储过程并且缓存中没有与连接的 set 选项匹配的现有执行计划时，将使用在该调用中传入的参数值编译新的执行计划.

When the stored procedure is invoked and there is no existing execution plan in the cache matching the set options for the connection a new execution plan will be compiled using the parameter values passed in on that invocation.

有时当传递的参数不典型(例如具有异常高的选择性)时会发生这种情况，因此生成的计划将不适合大多数其他具有不同参数的调用.

Sometimes this will happen when the parameters passed are atypical (e.g. have unusually high selectivity) so the generated plan will not be suitable for most other invocations with different parameters.

SSMS 选项 SET ARITH_ABORT 的默认值不同，因此当您在 SSMS 中执行存储过程时，不会遇到相同的问题计划.

SSMS has a different default value for the option SET ARITH_ABORT so will not get handed the same problematic plan when you execute the stored procedure inside SSMS.

下次发生这种情况时，调查该问题的最简单方法可能是让 2 个单独的 SSMS 窗口启用包括实际执行计划"选项，然后一次执行

Next time it happens possibly the simplest way of investigating the issue would be to have 2 separate SSMS windows with the "Include Actual Execution Plan" option enabled and in one do

SET ARITHABORT OFF
EXEC YourProc ...

另外一个

SET ARITHABORT ON
EXEC YourProc ...

假设默认的 ADO.NET 和 SSMS 连接选项，第一个应该使用缓存中的错误计划.

Assuming default ADO.NET and SSMS connection options the first one should use the bad plan from the cache.

如果这对您不起作用，您可以使用探查器查看您需要摆弄哪些其他设置选项来获取错误计划，或者仅使用探查器直接获取执行计划 - 或者您可以从 DMV 中检索它们如下.

If that doesn't work for you you could use profiler to see what other set options you need to fiddle with to get the bad plan or just use profiler to get the execution plans directly - or you can retrieve them from the DMVs as below.

select p.query_plan, *
from sys.dm_exec_requests r
cross apply sys.dm_exec_query_plan(r.plan_handle) p
where r.session_id = <spid of your ADO.NET connection>

例如，您可能会发现有问题的计划正在执行数以万计的单独索引搜索，而好的计划避免了这种情况.

You might find that the problematic plan is doing tens of thousands of individual index seeks for example whereas the good plan avoids this.

这篇关于如何排查sql server性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！