[SOLVED] Measure the time it takes to execute a t-sql query

Measure the time it takes to execute a t-sql query

I have two t-sql queries using SqlServer 2005. How can I measure how long it takes for each one to run?

Using my stopwatch doesn't cut it.

Solution

One simplistic approach to measuring the "elapsed time" between events is to grab the current date and time.

In SQL Server Management Studio

SELECT GETDATE();
SELECT /* query one */ 1 ;
SELECT GETDATE();
SELECT /* query two */ 2 ; 
SELECT GETDATE();

To calculate elapsed times, we can stuff the datetime values into variables, and use the DATEDIFF function:

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;
SET @t1 = GETDATE();

SELECT /* query one */ 1 ;

SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;
SET @t1 = GETDATE();

SELECT /* query two */ 2 ;

SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;
SET @t1 = GETDATE();

That doesn't exactly measure query execution time. It gives us a measure between the GETDATE() calls, which includes the execution time for the intervening query. For more precise measurements and statistics, we can use SQL Profiler.