x

total time in executing a query

i want to get the total time taken by sql server 2005 in executing a query
more ▼

asked Aug 12 '11 at 12:59 AM in Default

champ gravatar image

champ
31 5 6 7

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

You probably want to use [set statistics time][1]

Be careful though, there are different 'times'

  • query cpu time - time spent in the cpu
  • query duration - actual time taken between starting the query and it completing, including returning any results to your client
[1]: http://msdn.microsoft.com/en-us/library/ms190287.aspx-
more ▼

answered Aug 12 '11 at 01:06 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.6k 43 49 76

(comments are locked)
10|1200 characters needed characters left

As @Kev wrote, you can use the set statistics time.

If you are executing the query from SSMS, you can also tick the Include Client Statistics, which contains also execution times and other useful information.
more ▼

answered Aug 12 '11 at 01:11 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

I didn't see that you'd already posted that. I deleted my post.
Aug 12 '11 at 04:47 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

You can also get "grand" total execution time for the query from sys.dm_exec_query_stats. The query below works with SQL 2005 and later and will give you total/max/min/avg execution time, number of execution, total physical and logical reads:

       SELECT 
       QS.sql_handle,         
       ROW_NUMBER() OVER(PARTITION BY qs.sql_handle ORDER BY statement_start_offset) AS statement_no,    
       qs.execution_count,
       qs.total_physical_reads,
       qs.total_logical_reads,
       qs.total_logical_writes,
       qs.last_execution_time,
       sql_total_elapsed_time_s=convert(money,qs.total_elapsed_time)/1000000,
       sql_max_elapsed_time_s=convert(money,qs.max_elapsed_time)/1000000,
       sql_last_elapsed_time_s=convert(money,qs.last_elapsed_time)/1000000,
       sql_min_elapsed_time_s=convert(money,qs.min_elapsed_time)/1000000,
       sql_avg_time_s=(convert(money,qs.total_elapsed_time)/qs.execution_count)/1000000,            
       SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
         ((CASE statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE QS.statement_end_offset END 
          - QS.statement_start_offset)/2) + 1) AS statement_text
    FROM sys.dm_exec_query_stats AS QS
    CROSS APPLY .sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE DBID=DB_ID(@DBName ) OR @DBname IS NULL
more ▼

answered Aug 12 '11 at 02:37 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(comments are locked)
10|1200 characters needed characters left

In it's simplest form my preference is

USE [adventureworks]
go

DECLARE @StartTime DATETIME 
DECLARE @EndTime DATETIME 
DECLARE @results TABLE ( [SalesOrderID] INT )

SET @StartTime = GETDATE()


INSERT  INTO @results
        ( salesorderid
        )
        SELECT TOP 100
            [sod].[SalesOrderID]
        FROM
            [Sales].[SalesOrderDetail] AS sod
        ORDER BY
            [sod].[UnitPrice]


SET @EndTime = GETDATE()



SELECT
    DATEDIFF(ss, @StartTime, @endtime) AS [seconds] ,
    DATEDIFF(ms, @StartTime, @endtime) AS [milli seconds] ,
    DATEDIFF(mcs, @StartTime, @endtime) AS [micro seconds]

go
Getting deep details from the dmvs as @Hakan Winther shows is the better option if you want to do proper analysis
more ▼

answered Aug 12 '11 at 03:42 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x672
x84

asked: Aug 12 '11 at 12:59 AM

Seen: 1861 times

Last Updated: Aug 12 '11 at 01:27 AM