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, 2011 at 12:59 AM in Default

avatar image

champ
31 6 6 7

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

5 answers: sort voted first

You probably want to use `set statistics time`

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

more ▼

answered Aug 12, 2011 at 01:06 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

(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, 2011 at 01:11 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

I didn't see that you'd already posted that. I deleted my post.

Aug 12, 2011 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, 2011 at 02:37 AM

avatar image

Håkan Winther
16.5k 36 45 57

(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, 2011 at 03:42 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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.

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:

x2018
x994
x95

asked: Aug 12, 2011 at 12:59 AM

Seen: 3024 times

Last Updated: Aug 12, 2011 at 01:27 AM

Copyright 2016 Redgate Software. Privacy Policy