SQL Server 2005存储过程性能问题

I have the following issue: when a stored proc is called from my application, every now and then (like 1 time out of 1000 calls), it takes 10-30 seconds to finish. Typically, the sproc runs in under a second. It's a fairly simply proc with a single select that ties together a couple of tables. All the table names are set with a (NOLOCK) hint, so it probably isn't locking. The indexes are all in place too, otherwise it would be slow all the time.

我有以下问题:当从我的应用程序中调用存储过程时,不时(如1000次调用中的1次),需要10-30秒才能完成。通常情况下,sproc在一秒钟内运行。这是一个相当简单的proc,只有一个选择将几个表连接在一起。所有表名都设置了一个(NOLOCK)提示,因此可能没有锁定。索引也都到位了,否则它会一直很慢。

The problem is that I can't replicate this issue in SSMS (as it always runs subsecond) no matter how many times it runs the sproc, yet I see the problem when I point the profiler to the user who's running my app. The query plan in SSMS seems correct, yet the problem persists.

问题是我不能在SSMS中复制这个问题(因为它总是在亚秒运行),无论它运行多少次sproc,但是当我将探查器指向正在运行我的应用程序的用户时,我看到了问题。 SSMS中的查询计划似乎是正确的,但问题仍然存在。

Where do I go from here? How do I debug this issue?

我从哪里去?我该如何调试此问题?

8 个解决方案

#1

Autogrows on the database? Check for messages in the SQL error logs.

自动增长数据库?检查SQL错误日志中的消息。

Page splits due to inserted records? Check table fragmentation with DBCC SHOWCONTIG

由于插入记录而导致页面拆分?使用DBCC SHOWCONTIG检查表碎片

Antivirus scans? Don't.

防病毒扫描?别。

Out of date statistics? Don't rely on auto-update statistics on tables that change a lot.

过时的统计数据?不要依赖于更改很多的表的自动更新统计信息。

Don't rule out a problem on the client end, or the networking between them.

不要排除客户端上的问题或它们之间的网络连接。

Run profiler with a filter on duration, only capturing events with duration > 10 seconds, look for patterns in parameters, clients, time of day.

运行具有持续时间过滤器的探查器,仅捕获持续时间> 10秒的事件,查找参数,客户端,时间中的模式。

#2

Some options:

What does profiler or SET STATISTICS xx ON say? Is there simply resource starvation, say CPU

分析器或SET STATISTICS xx ON说什么? CPU说,是否只是资源匮乏?
The engine decides statistics are out of date. Are the tables changing by 10% row count change (rule of thumb). To test:

引擎决定统计数据已过期。表格是否按行数改变10%(经验法则)。去测试:
```
SELECT
    name AS stats_name, 
    STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM
    sys.stats 
WHERE
    object_id IN (OBJECT_ID('relevanttable1'), OBJECT_ID('relevanttable2'))
```
What else is happening on the server? example: Index rebuild: not blocking, just resource intensive.

服务器上还发生了什么?示例:索引重建:不阻塞,只是资源密集型。

Usually I'd suggest parameter sniffing but you say the parameters are the same for every call. I'd also expect it to happen more often.

通常我建议参数嗅探,但你说每个调用的参数是相同的。我也期望它更频繁地发生。

#3

I would set up a trace in SQL Server Profiler to see what SET options settings your application is using for the connection, and what settings are being used in SSMS. By SET options settings, I mean

我将在SQL Server Profiler中设置一个跟踪,以查看应用程序用于连接的SET选项设置,以及SSMS中正在使用的设置。通过SET选项设置,我的意思是

ARITHABORT
ANSI_NULLS
CONCAT_NULL_YIELDS_NULL
//etc

Take a look at MSDN for a table of options

查看MSDN以获取选项表

I have seen the problem before where the set options used between SSMS and an application were different (in that particular case, it was ARITHABORT) and the performance difference was huge (in fact, the application would time out for certain queries, depending on the parameter values).

我之前看到的问题是SSMS和应用程序之间使用的set选项不同(在特定情况下,它是ARITHABORT)并且性能差异很大(实际上,应用程序会因特定查询而超时,具体取决于参数值)。

This would be where I would recommend starting an investigation. By setting up a trace, you'll be able to see which particular calls are taking longer and the parameters that are being used.

这是我建议开始调查的地方。通过设置跟踪,您将能够看到哪些特定呼叫花费的时间更长以及正在使用的参数。

#4

On the runs that are slow is there anything different about the parameters passed to the proc?

在缓慢的运行中,传递给proc的参数有什么不同吗?

#5

Are you absolutely sure it's the database query, and not some other adjacent logic in your code? (i.e. have you put timestamped "trace" statements immediately before and after?)

您是否完全确定它是数据库查询,而不是代码中的其他相邻逻辑? (即你之前和之后是否立即加上带时间戳的“追踪”陈述?)

#6

Russ' suggestion makes the most sense to me so far as it sounds like you've looked into profiler to verify that the plan is optimized and so on.

Russ的建议对我来说是最有意义的,因为它听起来你已经调查了剖析器以验证计划是否已经优化等等。

I'd also watch for data-type coercion. i.e. I've seen similar problems when a varchar(60) parameter is being compared against and index with varchar(80) data. In some cases like that, SQL Server loses its mind and forces scans instead of seeks - though, I believe that in cases like that, you usually see this kind of thing happening in the execution plan.

我还会关注数据类型的强制。也就是说,当varchar(60)参数与varchar(80)数据进行比较和索引时,我发现了类似的问题。在某些情况下,SQL Server会失去理智并强制扫描而不是搜索 - 尽管如此,我相信在这种情况下,您通常会在执行计划中看到这种情况。

Sadly, another potential culprit (and I'm a bit leery of throwing it out because it might be a red herring) is hyper-threading. I've seen it do VERY similar things in the past [1].

可悲的是,另一个潜在的罪魁祸首(我有点怀疑因为它可能是一个红色的鲱鱼而抛出它)是超线程的。我已经看到它在过去做过非常类似的事情[1]。

1 http://sqladvice.com/blogs/repeatableread/archive/2007/02/13/Burned-again-by-HyperThreading-on-SQL-Server-2000.aspx

#7

Recompile the Stored Proc then see what happens. This actually helps.

重新编译存储过程,然后看看会发生什么。这实际上有帮助。

#8

I have also similar performance problem. Adding WITH RECOMPILE to SP helped.

我也有类似的性能问题。将WITH RECOMPILE添加到SP有帮助。

This is not the solution I've looked for but I didn't find better so far...

这不是我寻找的解决方案,但到目前为止我找不到更好的...

See: Slow performance of SqlDataReader

请参阅:SqlDataReader的性能降低

#1

Autogrows on the database? Check for messages in the SQL error logs.