May 7, 2010 at 3:10 am
All,
I fine tuned a procedure from 3 minutes to 50 seconds(i am looking the procedure to fine tune more)
I have to see the usage of CPU before tuning the procedure.
I also want to see the usage of CPU after tuning the procedure.
karthik
May 7, 2010 at 3:12 am
SET STATISTICS TIME ON
-- Gianluca Sartori
May 7, 2010 at 3:23 am
Also profiler can give you that information.
-- Gianluca Sartori
May 7, 2010 at 6:48 am
I am looking for some advanced level. Thats is, if more than one process is running when we execute the procedure how much of cpu utilized by this procedure? i am looking the cpu usage percentage ( before & after tuning the query)
karthik
May 7, 2010 at 6:52 am
100%. SQL tries to run a query as fast as possible, so it will try to use a CPU to 100% for the duration of the query. If it doesn't, it means there's something preventing the query from running as fast as it could (blocking, waits) and that's not a good thing.
What you want to do is to reduce the CPU time used, not the CPU %, and the CPU time used is given by statistics time and SQL profiler.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 7:02 am
GilaMonster (5/7/2010)
100%. SQL tries to run a query as fast as possible, so it will try to use a CPU to 100% for the duration of the query. If it doesn't, it means there's something preventing the query from running as fast as it could (blocking, waits) and that's not a good thing.What you want to do is to reduce the CPU time used, not the CPU %, and the CPU time used is given by statistics time and SQL profiler.
Totally agree.
Statistics time can be a little less reliable than profiler for fast queries, but, since you're measuring a 50 seconds procedure, this shouldn't apply.
If you don't like profiler, you can always get it from DMVs (sys.dm_exec_requests and sys.dm_exec_sessions).
-- Gianluca Sartori
May 7, 2010 at 9:25 am
I don't have the access to open 'profiler'.
I want to prepare a report saying that this much of cost has been reduced after tuning the procedure.
let us take the current one ( 3 min to 50 sec).
should i say execution time improved for 75% (approx) ?
karthik
May 7, 2010 at 9:32 am
If you can't use profiler, go with DMVs or SET STATISTICS TIME ON.
As far as the percent improvement is concerned, it's simple maths, nothing that a "Senior Software Engineer" can't afford. 😛
-- Gianluca Sartori
May 7, 2010 at 9:56 am
karthikeyan-444867 (5/7/2010)
I don't have the access to open 'profiler'.
Why not? Insufficient permissions? If you're testing your optimisations on a dev system, there's no reason you shouldn't have ALTER TRACE rights. If you don't, use STATISTICS TIME. All that requires (afaik) is permission to run the procedure which, if you've been tuning and testing it, you should have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 10:07 am
afaik? = "As far as I know?"
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 7, 2010 at 10:21 am
You can also take a look at sys.dm_exec_query_stats. You will need to know the sql_handle for the query you are running though. You would want to look at the elapsed time columns and the worker time columns.
You may be able to correlate that through sys.dm_exec_connections (most_recent_sql_handle).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 7, 2010 at 10:27 am
CirquedeSQLeil (5/7/2010)
You can also take a look at sys.dm_exec_query_stats. You will need to know the sql_handle for the query you are running though. You would want to look at the elapsed time columns and the worker time columns.
Just bear in mind that's cumulative since the plan was first cached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 10:27 am
The Dixie Flatline (5/7/2010)
afaik? = "As far as I know?"
Yup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 10:31 am
GilaMonster (5/7/2010)
CirquedeSQLeil (5/7/2010)
You can also take a look at sys.dm_exec_query_stats. You will need to know the sql_handle for the query you are running though. You would want to look at the elapsed time columns and the worker time columns.Just bear in mind that's cumulative since the plan was first cached.
There are cumulative results, but one may also see numerous entries in that view. There is a last_worker_time and a total_worker_time. One may be able to create a standard deviation given an avg, min, max and total with total execution count. This might be viable for producing statistical data back to the business.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 7, 2010 at 10:44 am
CirquedeSQLeil (5/7/2010)
There are cumulative results, but one may also see numerous entries in that view.
Yes, you may, but they are not historical.
A procedure with 3 statements will have 3 entries in that DMV, one for each statement in the stored proc. They'll have different statement_start_offset and statement_end_offsets which can be used, along with sys.dm_exec_sql_text, to get the statement that the stats refer to.
Edit: The stats are tied to a plan in cache so if, due to different user settings, there are two plans for a procedure in the cache, you'll see 'duplicated' stats entries, but they are from different plans and may, due to the different settings, have different performance stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply