Compare execution time of two similar TSQL queries

Question

I have 2 sql queries. The 2nd one is a modified version of the 1st one and there only little difference between them.

I want to know the execution time of both sqls, so I gave like

--Query 1

set statistics time on
    Select...
    .........
set statistics time off

--Result
(2 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 39 ms.

--Query 2

set statistics time on
    Select...
    .........
set statistics time off

--Result
(2 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

For the 2nd Query, the elapsed time is 0 ms, It should be some where around the execution time of 1st sql. It gave 0 ms ,just because I executed [Query 1] before [Query 2]. How can I view the elapsed time of 2nd query, should I delete statistics or any thing?

"*Advanced Querying including window-functions, dynamic-sql, and query-performance*" can go in http://dba.stackexchange.com — huMpty duMpty, Aug 19 '14 at 11:13
@Used_By_Already: execution time is probably related with *query-performance* — huMpty duMpty, Aug 19 '14 at 11:18
possible duplicate: please see: http://stackoverflow.com/questions/3764011/compare-two-queries note in particular the comment by AdaTheDev; you want to clear chaces between running queries, and if possible run several times in different sequence — Paul Maxwell, Aug 19 '14 at 11:18
@huMpty duMpty it isn't actually about the queries or their performance, it is about how to compare (TSQL) queries. It could go to dba I guess but it's a previously answered question anyway. — Paul Maxwell, Aug 19 '14 at 11:21
check this link http://blog.sqlauthority.com/2013/12/05/sql-server-finding-frequently-running-query-and-elapsed-time-notes-from-the-field-005/ — Hiral Nayak, Aug 19 '14 at 11:21
Just put them in the same batch and do estimate query plan. Look at the relative costs. `` — ta.speot.is, Aug 19 '14 at 11:22

score 4 · Accepted Answer · answered Aug 19 '14 at 11:20

4

The last_execution_time column in sys.dm_exec_query_stats will contain the information. Also it can be captured as the Duration column of the SQL:StmtCompleted event in SQL Profiler.

That being said, there are more ways to compare queries, not just time. Logical Reads, IO and wait stats come to mind. I recommend you read How to analyse SQL Server performance for details how to measure, what to measure and how to compare queries.

answered Aug 19 '14 at 11:20

Remus Rusanu

288,378
40
442
569

Thanks Remus, The link is very useful. – Jithin Shaji Aug 19 '14 at 11:23
Does [execution_count] column gives the execution time in ms? – Jithin Shaji Aug 20 '14 at 11:23
`execution_count` is *Number of times that the plan has been executed since it was last compiled.* as documented in the link – Remus Rusanu Aug 20 '14 at 11:26
last_worker_time = 80 Means, when it was executed last time, it took 80 milli sec. is that right? – Jithin Shaji Aug 20 '14 at 11:34
No, that would be `last_elapsed_time`. I recommend you read more carefully the documentation linked. – Remus Rusanu Aug 20 '14 at 11:35
See http://dba.stackexchange.com/questions/31002/what-is-difference-between-last-worker-time-and-last-elapsed-time-in-dmv-sys-dm for an explanation of `worker` vs. `elapsed` times. – Remus Rusanu Aug 20 '14 at 11:37
I am sorry, I got repeatedly confused with the column last_execution_time. I found out the last_elapsed_time column. Thanks, once again – Jithin Shaji Aug 20 '14 at 11:38

Compare execution time of two similar TSQL queries

1 Answers1