June 5, 2013 at 10:31 am
Hi All,
I'm checking the performance of 2 scripts, they get the same data.
When I check the execution time with set statistics time on;
I get the following results:
SQL Server Execution Times:
CPU time = 546 ms, elapsed time = 662 ms.
SQL Server Execution Times:
CPU time = 937 ms, elapsed time = 1030 ms.
But in SSMS status bar it's showing me 00:00:02 seconds.
And it seems that the query with the shorter execution times is taking longer to show.
Now can someone explain to me why the status bar shows 2 seconds when the elapsed time is 1030 ms?
Thanks...
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
June 5, 2013 at 10:39 am
The elapsed time is per query, not cumulative. So the elapsed time is the ~1000 ms plus the ~660 ms; I'm guessing this is rounded up in the indicator in the SSMS window.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 6, 2013 at 10:00 am
Matthew Darwin (6/5/2013)
The elapsed time is per query, not cumulative. So the elapsed time is the ~1000 ms plus the ~660 ms; I'm guessing this is rounded up in the indicator in the SSMS window.
What is displayed in the status bar of the SSMS window is a client-side timer that shows the elapsed time from when the user clicks Execute until all results are displayed on the screen - it includes the time required for the data to traverse the network and for SSMS to generate the grid display, which for large data sets can be significant. The messages displayed by SET STATISTICS TIME ON show server-side timings - how long it actually takes SQL Server to complete the batch.
Jason Wolfkill
June 6, 2013 at 10:53 am
I like to treat the SSMS time shown in the status bar as a general guide for how long a whole process takes and nothing more. I much prefer the granularity that SET STATISTICS TIME ON gives us for assessing the performance of individual statements and trying to figure out how to make things run faster.
In looking at the whole picture, I'd say that about 90% of the time, I use SET STATISTICS IO ON at in concert with TIME ON. Not only do you get to see the time something takes, but any disk bottlenecks that might exist. In many cases, this will point you to problem areas where, after you see it, it'll be one of those "DOH!" moments where you're asking yourself why you didn't see that before.
These two settings also help me to compare the performance of individual statements when considering two different approaches to specific problems.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply