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

Question

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

User · Answer

Click on Statistics icon to display and then run the query to get the timings and to know how efficient your query is

User · Answer

even better  this will measure the average of n iterations of your query  Great for a more accurate reading   declare  tTOTAL int   0 declare  i integer   0 declare  itrs integer   100  while  i  lt   itrs begin declare  t0 datetime   GETDATE    --your query here  declare  t1 datetime   GETDATE    set  tTotal    tTotal   DATEDIFF MICROSECOND  t0  t1   set  i    i   1 end  select  tTotal  itrs

User · Answer

One simplistic approach to measuring the  elapsed time  between events is to just 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  you could grab those date 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    That s just one approach  You can also get elapsed times for queries using SQL Profiler

User · Answer

DECLARE  StartTime datetime DECLARE  EndTime datetime SELECT  StartTime GETDATE      -- Write Your Query   SELECT  EndTime GETDATE    --This will return execution time of your query SELECT DATEDIFF MS  StartTime  EndTime  AS  Duration in millisecs    You can also See this solution

User · Answer

Another way is using a SQL Server built-in feature named Client Statistics which is accessible through Menu   Query   Include Client Statistics   You can run each query in separated query window and compare the results which is given in Client Statistics tab just beside the Messages tab   For example in image below it shows that the average time elapsed to get the server reply for one of my queries is 39 milliseconds     You can read all 3 ways for acquiring execution time in here  You may even need to display Estimated Execution Plan ctrlL for further investigation about your query

User · Answer

If you want a more accurate measurement than the answer above   set statistics time on   -- Query 1 goes here  -- Query 2 goes here  set statistics time off   The results will be in the Messages window   Update  2015-07-29    By popular request  I have written a code snippet that you can use to time an entire stored procedure run  rather than its components  Although this only returns the time taken by the last run  there are additional stats returned by sys dm exec procedure stats that may also be of value   -- Use the last elapsed time from sys dm exec procedure stats -- to time an entire stored procedure   -- Set the following variables to the name of the stored proc -- for which which you would like run duration info DECLARE  DbName NVARCHAR 128   DECLARE  SchemaName SYSNAME  DECLARE  ProcName SYSNAME N TestProc    SELECT CONVERT TIME 3  DATEADD ms ROUND last elapsed time 1000 0 0  0           AS LastExecutionTime FROM sys dm exec procedure stats WHERE OBJECT NAME object id database id   ProcName AND        OBJECT SCHEMA NAME object id database id   SchemaName OR  SchemaName IS NULL  AND        DB NAME database id   DbName OR  DbName IS NULL

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

Examples related to performance

Examples related to sql-server-2005