May 9, 2011 at 11:51 pm
Folks,
While learning performance tuning concepts, at some places the experts use 'Set statistics io on' command to see elapsed time for each operation as well as CPU time involved.
My question is that when I run this for a stored procedure, I get multiple entries for CPU and time involved for each operation. The last line shows biggest numbers of CPU and time involved. Does it mean that I should be looking only on the last statement that the 'Messages' pane display in management studio. Is it a sum of all the smaller and big queries and represent the total CPU and time involved?
May 10, 2011 at 12:53 am
because your SP is using multiple queries in it,it tells you statistic for every query
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 10, 2011 at 1:31 am
Syed Jahanzaib Bin hassan (5/10/2011)
because your SP is using multiple queries in it,it tells you statistic for every query
Thanks but what if I want to know the total CPU time spent. do I need to add each one of the CPU times to get it for the whole procedure or just reading the last statement where CPU times is mentioned and looks like it is automatically the added CPU times of all the previous queries.
May 10, 2011 at 2:16 am
I'm not sure if we're very clear here - set statistics io on show io statistics not cpu. using this in SSMS is a good quick way to see if a query or proc is generating excessive io - or whatever. This output does not provide a total so if there are lots of statements and you want the total profiler is better.
With profiler you can see the cpu and io for each statement as well as the overall total. Note that you must not mix this type of tuning with SSMS and profiler as the io will be calculated different; use one mrthod or the other.
Using stats io in SSMS is useful in different ways, e.g. you run a query which performs 3000 io on a table - a plan may indicate a seek but you're not convinced. compare to a select * to see if it's actually a scan. ( if a select * returns 3000 io then your other query is a scan - hope that makes sense )
btw cpu time is sometimes shown as cpu cycles don't get that mixed up with duration in time.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 10, 2011 at 3:35 am
check with
SP_WHO2
check with also
Select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.status != 'sleeping'
order by sp.cpu desc
paste your sp on the query analyzer on SSMS and then click on display estimated plan ,if you are using temp table in this sp then click on Actual Execution Plan button on SSMS(SQL Server Management Studio)
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 10, 2011 at 3:55 am
colin.Leversuch-Roberts (5/10/2011)
I'm not sure if we're very clear here - set statistics io on show io statistics not cpu. using this in SSMS is a good quick way to see if a query or proc is generating excessive io - or whatever. This output does not provide a total so if there are lots of statements and you want the total profiler is better.With profiler you can see the cpu and io for each statement as well as the overall total. Note that you must not mix this type of tuning with SSMS and profiler as the io will be calculated different; use one mrthod or the other.
Using stats io in SSMS is useful in different ways, e.g. you run a query which performs 3000 io on a table - a plan may indicate a seek but you're not convinced. compare to a select * to see if it's actually a scan. ( if a select * returns 3000 io then your other query is a scan - hope that makes sense )
btw cpu time is sometimes shown as cpu cycles don't get that mixed up with duration in time.
You are right. I meant to write Set statistics time on' which gives CPU information .When we execute this command before executing a stored procedure, there are so many rows but in the last row, the cpu times shown looks like a sum of all the cpu times for the whole procedure. Am i correct here?
May 10, 2011 at 7:10 am
I always use profiler as the overall exection time of a complex proc is meaningless without the breakdown for each statement.
It's actually a much better route to follow - as you'll often find there's just one statement taking almost all the time.
The other point about stats time in SSMS is that I think it shows time to client which might not be what you need, as I say I don't use this for tuning so I'm not 100% sure here.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 10, 2011 at 7:16 am
colin.Leversuch-Roberts (5/10/2011)
I always use profiler as the overall exection time of a complex proc is meaningless without the breakdown for each statement.It's actually a much better route to follow - as you'll often find there's just one statement taking almost all the time.
The other point about stats time in SSMS is that I think it shows time to client which might not be what you need, as I say I don't use this for tuning so I'm not 100% sure here.
+1.
Moreover, the only real figure that matters is cpu time because this is where the work is being done (From Gail). The rest of the time is spent on waiting. Reads is fine for tuning too, but cpu is king!
May 10, 2011 at 2:37 pm
ah you've never met my development team! Only they could could generate 18 million page reads looking a person's name up < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 10, 2011 at 3:05 pm
I use the SSMS stats to start tuning before I go into traces. Actually, I use it before I even convert a script into a proc. Gives me the time and io stats for each statement, to the accuracy I usually need, and I can often get enough data from these to tune, without having to set up traces and all that. It's just more convenient and takes less time, and it's usually enough.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 11, 2011 at 3:53 am
It's interesting to read how different people approach the same thing. My first port of call is nearly always the query plan. I do wonder if we're a bit at cross puposes with duration and cpu thougth? there's a actual elapsed time for exection and there's the cpu cycles ( or ticks ) used. the cpu cycles should remain constant across servers whilst the elapsed time may vary.
For example a proc which runs with say 1000 page reads and 570 cpu cycles may execute in 10ms on my prod box and 100ms on a dev box.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 11, 2011 at 5:23 am
GSquared (5/10/2011)
I use the SSMS stats to start tuning before I go into traces. Actually, I use it before I even convert a script into a proc. Gives me the time and io stats for each statement, to the accuracy I usually need, and I can often get enough data from these to tune, without having to set up traces and all that. It's just more convenient and takes less time, and it's usually enough.
Ditto here, I was in the mindset of the "whole" system makeover when I wrote this. So maybe a little out of context !:hehe:
May 11, 2011 at 5:28 am
colin.Leversuch-Roberts (5/11/2011)
It's interesting to read how different people approach the same thing. My first port of call is nearly always the query plan. I do wonder if we're a bit at cross puposes with duration and cpu thougth? there's a actual elapsed time for exection and there's the cpu cycles ( or ticks ) used. the cpu cycles should remain constant across servers whilst the elapsed time may vary.For example a proc which runs with say 1000 page reads and 570 cpu cycles may execute in 10ms on my prod box and 100ms on a dev box.
We're saying the same thing. Duration is not the most accurate measure of performance. It may help spot issues but not so much beyond that.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply