Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

What are the most accepted ways to time your queries, whether they are ad hoc or stored procedures?

Is it more accurate to use SQL Profiler than SET STATISTICS TIME ON?

share|improve this question

2 Answers

up vote 3 down vote accepted

In either case you are getting the run time from the server, so you should get the same values from either.

share|improve this answer
-1 not the same because of black box udfs – gbn Sep 2 '11 at 4:53
+1 as Set Statistics time on will report timing issues from black box udfs (sqlinthewild.co.za/index.php/2009/04/29/…) – Andrew Bickerton Sep 2 '11 at 7:33
We are looking at run time here which is included in both. Things like IO and CPU load aren't accounted for but run time is as the counter starts when the command starts and finishes when it is done. – mrdenny♦ Sep 5 '11 at 0:25
up vote 4 down vote

Profiler is easier to understand (all in one line) and will capture hidden IO, CPU etc in UDFs (scalar and multi-statement TVFs)

SET STATISTICS TIME ON and SET STATISTICS IO ON will not show you IO and CPU from scalar UDFs and multi-statement TVFs: only the "outer" query statistics

From SO:

And no doubt some folk will disagree with me, so here is Adam Machanic on the subject too

share|improve this answer
+1 as good points on scalar functions and how set statistics io on doesn't report them (didn't know, thanks!), however set statitistics time will show you the total time taken (just not be easily explained by set statistics profile/io) – Andrew Bickerton Sep 2 '11 at 7:30
@Andrew Bickerton: I rarely use CPU time: mostly IOs so had forgotten/overlooked. And I avoid udfs with table access too where I can – gbn Sep 2 '11 at 10:35
@gbn that last link you posted, I don't understand why a TVF is much more efficient then a scalar UDF. To me that is not intuitive. Why would it be THAT much more hits for a scalar UDF?? – Thomas Stringer Sep 2 '11 at 12:50
@Surfer513: there are 2 kinds of TVF and one scalr. The UDFs with BEGIN..END must run to completion. The inline TVFs expand into the outer query – gbn Sep 2 '11 at 13:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.