February 13, 2011 at 8:52 pm
I have a proc which takes 2secs to finish when there is minimal load on the server( night times) and takes 30 secs during day times. Mentioned below are my findings:
i) did my best adding indexes...etc...but the point is if it takes less than 2 secs at night, i do not think it is issue with the proc?
ii) During day time our CPU usage on avg is 80% and procs takes 30 secs to completed.
iii) Where else should i look. Should i look for procs using most CPU?
February 13, 2011 at 9:57 pm
i would like to see :-
1) If the query is using select * or select required columns
2) any filter condition (like where) is uisng the proper candidate for filter clause.
3) fregmentation of index which is being used in query.
4) stats of objects used in query.
You can generate the graphical execution plan and can see the cost of query to further understand its slowness.
----------
Ashish
February 13, 2011 at 10:04 pm
What does this query do?
My first instinct is to tell you to look at the waitstate on the proc while it runs slow during the day. Next thing I'd look into is if you're getting blocked by other processes by their locks.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 13, 2011 at 10:05 pm
Please refer below link
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
This would be useful so that you could get the relevant help.
M&M
February 13, 2011 at 11:41 pm
sqldba_icon (2/13/2011)
I have a proc which takes 2secs to finish when there is minimal load on the server( night times) and takes 30 secs during day times. Mentioned below are my findings:i) did my best adding indexes...etc...but the point is if it takes less than 2 secs at night, i do not think it is issue with the proc?
ii) During day time our CPU usage on avg is 80% and procs takes 30 secs to completed.
iii) Where else should i look. Should i look for procs using most CPU?
80% is kind of high. It typically means that you have a lot of code that isn't, ummmm..... optimized. We might be able to get your code to run faster but if it has to wait for CPU time because of other queries, there's not much that can be done except optimize those other queries, as well.
One thing you can check for is to make sure you don't have a virus scanner running on the MDF or LDF files. You should also check the fragmentation of the tables and the "age" of the statistics.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 5:43 am
If you can, while this procedure is running, run a select statement against sys.dm_exec_requests. You should be able to see the procedure (if not, combine it with sys.dm_exec_sql_text and use the proc name to filter the results). This will show you if the query is waiting on something, which is very likely, and what it's waiting on.
I'm with Jeff though, it sounds like you've got some systemic problems going on here.
"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
February 14, 2011 at 8:26 am
perform waitstats and IO stall analysis during day. Likely issues are too little RAM, too slow IO, BLOCKING, too little CPU power.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2011 at 9:46 pm
I will try to get some waitstats.
February 14, 2011 at 9:54 pm
You might also want to do a server side trace and find out what's using most of the CPU time.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2011 at 5:53 am
You should also look at disk contention (waits mentioned above). Disk contention will cause high cpu as processes are waiting for the disk and queuing up. SQL Server may not be the only application using your disks.
Steve
February 15, 2011 at 7:34 am
Steve-3_5_7_9 (2/15/2011)
You should also look at disk contention (waits mentioned above). Disk contention will cause high cpu as processes are waiting for the disk and queuing up. SQL Server may not be the only application using your disks.Steve
Actually this is exactly backwards. Slow disks or significant IO contention will lead to LOWER CPU usage because of the 'waiting' for data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 15, 2011 at 7:48 am
Thanks. I don't know what I'm talking about. I thought I experienced this but after looking through my notes, I cannot find anything; so just disregard me.
February 15, 2011 at 9:56 am
Steve-3_5_7_9 (2/15/2011)
Thanks. I don't know what I'm talking about. I thought I experienced this but after looking through my notes, I cannot find anything; so just disregard me.
I hate those days. If it makes you feel any better I did that about 2 weeks ago. :hehe:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply