开发者

How to accurately measure the time taken by a stored procedure/function

I have the following options

  1. Use the 'Include Client Statistics' option in SSMS . (I take the 'Wait time on server replies' to be the time taken by the stored procedure)
  2. Capture the start and end time and take the diff.

This is what i do ,

DECLARE @startTime DATETIME
    SET @startTime = GETUTCDATE()

   EXEC MyStoredProc

  PRINT 'Execution Time : ' +  CAST(DATEDIFF(ms,@startTime,GETUTCDATE()) AS NVARCHAR(20)) + ' milliseconds'

But I sometimes see a big difference between the values I get from both the methods 开发者_Python百科for the same stored procedure that I am beginning to think that I am doing something wrong here. Which method should I use? or is there a better way to time the stored proc?


Make sure you run the following to get a good baseline everytime:

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO

Stolen from here.

Otherwise you might be pulling cached data.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜