A stopwatch for SQL Server

readme.md

STOPWATCH is used to simply measure queries, especially long, complex ones. This is not a replacement for set statistics time on but it's a handy way to measure query behavior in a way that feels comfortable to C# developers, especially. STOPWATCH uses the system time to calculate elapsed durations.

Note: STOPWATCH uses SQL Server's session_context to persist the start time.

Example usage

EXEC STOPWATCH_START
SELECT * FROM USERS
EXEC STOPWATCH_READ

Example output

Start: 2021-03-23 17:50:52.2300000 

(1 row affected)
Delta: 00:00:00.1200000 

EXEC STOPWATCH_START [@print], [@message]

Use STOPWATCH_START to trigger the moment in time to base elapsed duration.

1. print: (optional/default=false): prints the current time when called.
2. message: (optional/default=null): A custom message to append to time.

Syntax

DECLARE @print BIT = 1;
DECLARE @message VARCHAR(50) = 'Jerry Nixon';
EXEC STOPWATCH_START @print, @message;

Result Time is logged and printed with custom message.

---

EXEC STOPWATCH_READ [@message]

Use STOPWATCH_READ to print the elapsed duration.

1. message: (optional/default=null): A custom message to append to the elapsed duration.

Syntax

DECLARE @message VARCHAR(50) = 'Jerry Nixon';
EXEC STOPWATCH_READ @message;

Result Elapsed duration is printed with custom message.

stopwatch.sql

CREATE OR ALTER PROC STOPWATCH_START
  @print BIT = 1 
  , @message NVARCHAR(1000) = NULL
AS
BEGIN
  DECLARE @starttime VARCHAR(50) = SYSDATETIME();
  EXEC sp_set_session_context N'start_time', @starttime;
  IF (@print = 1) 
  BEGIN
    SET @message = CONCAT('Start: ', @starttime, ' ', @message);
    RAISERROR (@message, 0, 1) WITH NOWAIT;
  END
END

GO

CREATE OR ALTER PROC STOPWATCH_READ 
  @message NVARCHAR(1000) = NULL
AS
BEGIN
  DECLARE @starttime DATETIME2(7) = CONVERT(DATETIME2(7), SESSION_CONTEXT(N'start_time'))
  DECLARE @emptydate DATETIME2 = CAST('1900-01-01 00:00:00.0000000' as datetime2);
  SET @message = CONCAT('Delta: ', CONVERT(time, DATEADD(ms, DATEDIFF(ms, @starttime, SYSDATETIME()), @emptydate)), ' ', @message)
  RAISERROR (@message, 0, 1) WITH NOWAIT;
END