Labels

Friday, December 30, 2011

How to measure stored procedure execution time?

DECLARE @i int = 0, @TestLimit int = 10
DECLARE @Timing TABLE ( Timing int) 

WHILE (@i < @TestLimit)
BEGIN
      DECLARE @RC int, @ManagerID int =1
      DBCC DROPCLEANBUFFERS
      DECLARE @Start datetime = getdate()
      EXECUTE @RC = [AdventureWorks2008].[dbo].[uspGetManagerEmployees] @ManagerID
            INSERT @Timing
      SELECT ExecutionMsec = datediff (millisecond, @Start, getdate())
      SET @i=@i+1
END

SELECT ExecutionMsec = avg(Timing) FROM @Timing
Go

No comments:

Post a Comment