June 13, 2013 at 11:52 am
Hi all
So, I've got this batch (I hope it is correct to call it a batch, this is just a few variables declarations, queries to assign values to these variables and then just a select statement that returns a data set) open i SSMS that takes 8 sec to execute (this value is taken from the yellow bar under the query window). Now if I turn on "Include Actual Execution Plan" and "Include Client Statistics" and execute the Client Statistics tab pops up and the "Total execution time" has value of 161. Now I always thought this was the time it took the server to execute the hole batch in milliseconds.
I don't know if this is relevant or not but the Client processing time is 1 and Wait time on server replies is 161. The data set returned has 210 rows and and 8 columns, so it ain't big.
Now, if I the execute the same batch with "Include Actual Execution Plan" turned off the "Total execution time" becomes 7844 which is a value I would have expected in the first place.
Can anyone explain what is going on here?
I have always used the "Total execution time" when I'm comparing different queries, is this a bad practice, should I be using something else, if then what?
I'm running SQL Server 2008 R2 on a Windows Server 2008 R2 Datacenter.
Thx in advance
June 13, 2013 at 1:56 pm
It takes time for SQL to run the batch - that's the execution time. It also takes significant time for SSMS to take the execution plan and translate it into a nice, GUI format and show it to you - there's the biggest difference.
If you want to compare individual statements, try using SET STATISTICS TIME ON and SET STATISTICS IO ON. They will show you the time and I/O required by each and every statement. I find that they give you a fine level of detail as to what's taking the time. Don't forget to turn them off when you're done.
The query plan, on the other hand, shows you what table scans, index seeks and scans are being used, where the bulk of your rows are being moved to other steps, and a bunch of other stuff, which is a very useful set of information to have. For more information on execution plans, I'd recommend Grant Fritchey's book: http://www.sqlservercentral.com/articles/books/94937.
June 14, 2013 at 2:46 am
I'm with Ed. The Client Statistics are really only useful if you want to measure round-trip time, from the client to the server and back to the client. They're just not as useful to measuring query performance as getting STATISTICS_IO and STATISTICS_TIME.
Just remember that you need to work off both measurements. Neither by itself gives you a sufficient picture of performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply