question

champ avatar image
champ asked

total time in executing a query

i want to get the total time taken by sql server 2005 in executing a query
sql-server-2005sqlserver
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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-
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Our very own @Fatherjack blogged on this problem here: and here
2 Likes 2 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I didn't see that you'd already posted that. I deleted my post.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.