SQL存储过程执行时间之谜
我试图找出为什么 SQL Server 存储过程执行缓慢，所以我放入了一些粗略的计时器，如下所示：
Create Procedure DoStuff
As Begin
Declare @Stopwatch datetime
Set @Stopwatch=GetDate()
Print char(13) + 'Task A'
/* Perform Task A */
Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatch = GetDate()
Print char(13) + 'Task B'
/* Perform Task B */
Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatch = GetDate()
Print char(13) + 'Task C'
-- Perform Task C
Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatch = GetDate()
End
Exec DoStuff
我得到这样的结果：
Task A 0 Task B 80 Task C 100
所以我认为该过程将花费 180 毫秒来执行。然而，该过程需要 3000+ 毫秒才能执行；在“客户端统计”中，我得到
Client processing time: 12 Total execution time: 3105 Wait time on server replies: 3093
“Whataccounts for the extra ~2800 ms?”
I'm trying to figure out why a SQL Server stored procedure is executing slowly, so I've put in some crude timers, like this:
Create Procedure DoStuff
As Begin
Declare @Stopwatch datetime
Set @Stopwatch=GetDate()
Print char(13) + 'Task A'
/* Perform Task A */
Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatch = GetDate()
Print char(13) + 'Task B'
/* Perform Task B */
Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatch = GetDate()
Print char(13) + 'Task C'
-- Perform Task C
Print DateDiff(ms, @Stopwatch, GetDate()); Set @Stopwatch = GetDate()
End
Exec DoStuff
I'm getting something like this:
Task A 0 Task B 80 Task C 100
So I would think the procedure would take 180 ms to execute. However, the procedure takes 3000+ ms to execute; in Client Statistics, I get
Client processing time: 12 Total execution time: 3105 Wait time on server replies: 3093
What accounts for the extra ~2800 ms?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这是检查个人时间的粗略方法。更好的方法是在 SQL Profiler 中运行跟踪并监视 SP:Completed< /a> 和 SP:StmtCompleted 事件。
SP:Completed 在存储过程完成时记录，给出总时间。
当存储过程中的每个语句完成时，将记录 SP:StmtCompleted。因此，这将为您提供存储过程每个单独部分的时间。
值得使用这种方法，重新检查数字，然后从那里开始。
It is a crude way to check the individual times. A better way would be to run a trace in SQL Profiler and monitor the SP:Completed and SP:StmtCompleted events.
SP:Completed is recorded when the sproc completes, giving the overall time.
SP:StmtCompleted is recorded when each statement within a sproc completes. So this will give you the times for each individual part of the sproc.
It'd be worth using this approach, rechecking the figures and then going from there.
如果您打开“显示执行计划”选项，也请尝试将其关闭。这确实会影响总执行时间的数字——它通常会导致总执行时间的非常误导性的结果。
Also try turning off the "Show Execution Plan" option if you turn it on. That really throws off your numbers for total execution time -- it can often lead to a very misleading result for total execution time.
我敢打赌，你的程序每次运行时都会重新编译，这很花时间。读一下：http://www.sql-server-performance .com/articles/per/optimizing_sp_recompiles_p1.aspx
将 @Stopwatch 设为输入参数并传入 GETDATE()，然后将其作为 OUTPUT 参数传回。然后，您可以打印另外两个时间间隔，即从 EXECUTE 到实际运行的时间，以及从 RETURN 到调用者再次获得控制权的时间。
I'll bet that your procedure is recompiling each time it is running, which is taking the time. give this a read: http://www.sql-server-performance.com/articles/per/optimizing_sp_recompiles_p1.aspx
Make @Stopwatch an input parameter and pass in GETDATE(), then pass it back out as an OUTPUT parameter. You can then print another two time intervals, the time from EXECUTE to actual run, and the time from RETURN until the caller has control again.
它可能是表上的一个块，或者由于某种原因等待资源。
您可以查看管理实用程序，看看是否存在锁定或等待，这取决于它所显示的内容。
或者，它也可以是用于回复 SP 的入站和出站通信的网络通信。
It could be a block on the table, or a wait for resources for some reason.
You could look at the management utilities and see if there are locks or waits going on, which is based on what it is showing.
OR, it could be network communication for replies inbound and outbound communication to the SP as well.
如果您在 SSMS 中执行事务性，那么事务性是一个不知情的猜测。但您应该查看执行计划以了解更多有关真正原因的信息。这可能有很多原因。
http://msdn.microsoft.com/en-us/library/ms178071。 ASPX
Transactionality if you're executing it in SSMS is an uninformed guess. But you should look at the execution plan to learn more about the real cause. It could be a number of things.
http://msdn.microsoft.com/en-us/library/ms178071.aspx