April 9, 2010 at 2:24 am
Hii friends
I have one server with 4 CPU , 8GB Ram
6GB is dedicated to sql server
now we have scenario where some stored procedure get fired after every 5 sec.
but sometime in a trading hrs those SPs get blocked by each other & CPU utilization goes beyond 90% when i fire SP_Updatestats on database after completing the same,everything works fine and CPU utilzn. also comes down to 40%
my question is why should i fire sp_updatestats in trading hrs (As in my Database option auto update & auto create stats is kept ON )?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
April 9, 2010 at 4:55 am
sanketahir1985 (4/9/2010)
Hii friendsI have one server with 4 CPU , 8GB Ram
6GB is dedicated to sql server
now we have scenario where some stored procedure get fired after every 5 sec.
but sometime in a trading hrs those SPs get blocked by each other & CPU utilization goes beyond 90% when i fire SP_Updatestats on database after completing the same,everything works fine and CPU utilzn. also comes down to 40%
my question is why should i fire sp_updatestats in trading hrs (As in my Database option auto update & auto create stats is kept ON )?
first thing you need to do is find the cause of the blocking.
perhaps you could set transaction isolation level read uncommitted in som of the procs that read data only (although this depends ont he proc themselves and what they do)
it may also be that autoupdate stats is causing the issue. on large databases DBAs often turn these setting off as they can start updating stats at crucial moments witout thir knowledge
do you have details of the blocking ?
MVDBA
April 9, 2010 at 5:57 am
sanketahir1985 (4/9/2010)
when i fire SP_Updatestats on database after completing the same,everything works fine and CPU utilzn. also comes down to 40%
But how did you find out that firing SP_Updatestats will help you ? Did you do some workaround on those Sps before opting this solution?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 9, 2010 at 6:32 am
I'd need more details to be sure, but it sounds like you're seeing execution plans change during the day. Updating the stats causes the plan to recompile. I'd suggest getting a copy of the plan while it's running well and get another when it starts to run badly. Then you can compare the two.
Another possibility is that your statistics are aging badly during the day causing the plan being used to start performing poorly based on the statistics available. I've run into this myself. We put a targeted UPDATE STATISTICS in place that ran on a scheduled basis to ensure that the stats were completely up to date.
I'd be very cautious about implementing NO LOCK until you have a full and complete understanding of what exactly a dirty read means. It's not just that you get a record that reads 'Albequerque' when it used to read 'New York' but you can, due to page splits & other operations, get extra rows or miss rows of data in your result sets.
Same thing with turning off statistics updates. If you've got a highly volatile system, updating statistcs more often can be a better solution than simply turning them off. It really depends on what's happening.
"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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply