How to accurately measure the time taken by a stored procedure/function
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)
- 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.
精彩评论