January 25, 2007 at 9:02 am
I have a simple script:
select getdate()
go
-- my query here
go
select getdate()
go
The time difference between these two getdate() statements is usually 30 ms when I display result in a text and about 100 ms if i display results in a grid.
If I SET STATISTICS TIME ON, it displays CPU time = 0 ms, elapsed time = 3 ms.
Does that mean that SQL Server actually spent 3 ms to execute this query on server side and another 27 ms to fetch the results back to my SSMS client tool accross the network and populate result pane for text results, and about 97 sec for the same thing for populating the grid ?
January 25, 2007 at 9:20 am
Mark
as far as I know this is exactly the case. Building up the grid takes a lot longer than results in text. The 3 ms is the real execution time. How long it takes for the client to present the data depends on a lot of things like formatting and the number of rows, but in most cases even a 100 ms is no problem for the end user.
While performance tuning only the execution time on the server should be considered (3ms). Because that's where you want the resources (IO, CPU, Locks) to become available again for other processes.
Markus
[font="Verdana"]Markus Bohse[/font]
January 26, 2007 at 5:38 am
What you usually see in the STATISTICS is the time on the server. It doesn't include time to move the data to your machine or time to display the graphics. By splitting the statements with the GO command, you're getting the differences there. Try this
SELECT GETDATE()
EXEC yourproc
SELECT GETDATE()
GO
It'll be closer to what you see in the STATISTICS. However, I'm a lazy SOB, so I just turn on the STATISTICS in all cases when I'm interested in timings.
"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
January 26, 2007 at 2:16 pm
If you turn on "Include Client Statistics" it will show you the total time and the time spent processing the returned data by the client. The difference between the two is the amount of time the server spent processing it.
The coolest part of the Client Statistics, is that it keeps the execution statistics for you and compares them to the previous executions. So if you are tweaking the query, you can see if there were improvements in your execution immediately.
To turn it on/off in SQL Server Management Studio, click Query (in the top menu) --> Include Client Statistics. Immediately below it in the menu is Rest Client Statistics. This resets the statistics that it is tracking.
January 29, 2007 at 5:34 am
timing of queries at the client is of dubious value unless you're interested in network stats. Use profiler to time your queries. The getdate() example is pretty worthless, I suggest you work with a proper query and result set - this will enable you to get more meaningful results at your client ( if you so wish ) - It can be tricky sometimes, but finding queries that take 20 or 30 seconds to run ( not select * ) can be a better place to start, especially if you want to start investigating performance and tuning.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 29, 2007 at 6:19 am
I agree. Best measure is either to use the STATISTICS TIME or Profiler.
Interesting point, don't use both because you'll get confused. We found that there are small differences between the two numbers. I don't recall the precise reason for the difference, but it wasn't earth shattering, just a slight discrepancy. So if you're interested in getting comparisons, use one or the other and then you'll be doing apples to apples.
"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
January 30, 2007 at 6:00 am
there are usually ( sometimes very large ) differences in io counts between qa and profiler, I only ever use times in profiler as I'm usually looking at running systems. Other than that I usually use QA/management studio
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply