开发者

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

开发者 https://www.devze.com 2023-01-25 09:40 出处:网络
I have the following options 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)

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

精彩评论

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