How-to add timing print out statements to SQL scripts
While working in the lab late one night I wondered how I could best determine how long individual stored procedures were taking to run when called from an external "wrapper" script. Once I had this data I could then use it to determine which stored procedures were taking longer than anticipated and might require performance tuning.
To do this I added a set of statements to the beginning that initialize the time keeping variables, a set in the middle that record the intermediate time(s), and a set at the end that record the overall execution time.
Sample timing segment statements are are follows:
DECLARE @StartTime datetime = (SELECT GETDATE()) DECLARE @TempTime datetime = (SELECT @StartTime) DECLARE @EndTime datetime = (SELECT @StartTime) PRINT '--Procedure started at: ' + CONVERT(varchar(20),@StartTime, 114)
Note: These statements would be repeated at the end/beginning of every new inner procedure call.
SELECT @StartTime = (@EndTime) SELECT @EndTime = (GETDATE()) PRINT '--Intermediate stored procedure executed in: ' + CONVERT(varchar(20),@EndTime - @StartTime, 114)
SELECT @StartTime = (@TempTime) SELECT @EndTime = (GETDATE()) PRINT '--Procedure ended at: ' + CONVERT(varchar(20),@EndTime, 114) PRINT '--Procedure executed in: ' + CONVERT(varchar(20),@EndTime - @StartTime, 114)