You are here: Home / Blog / posts / Programming Magic / How-to add timing print out statements to SQL scripts

How-to add timing print out statements to SQL scripts

by Ken Taylor published Apr 20, 2011 08:50 AM, last modified Jan 28, 2014 11:45 AM
This wiki describes how to add timing print out statements to SQL scripts and stored procedures.

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:

Initial statements:

    DECLARE @StartTime datetime = (SELECT GETDATE())
    DECLARE @TempTime datetime = (SELECT @StartTime)
    DECLARE @EndTime datetime = (SELECT @StartTime)
    PRINT '--Procedure started at: ' + CONVERT(varchar(20),@StartTime, 114)

Intermediate statements:

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)

Final statements:

   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)