April 9, 2010 at 12:56 am
Hi All,
This is my first post - and I am quite new to the DBA type role. I've got a query about updating statistics I was hoping you could help me with.
One of the database servers I am aiding in management of is having issues after an sp_updatestats is run across all the databases - after a while we get high CPU and resources, etc. After running sp_updatestats again on one of the larger databases it calms down. It seems to be triggered by the original sp_updatestats run.
Is there any bugs that might be causing this? The version run is SQL2005 Enterprise, 64 bit Edition, 9.00.3239.00
I have read that upping the resampling size could help create a more accurate stats update - but we also have auto update statistics and autocreate statistics set to true, and auto update statistics asynchronously set to false. From other posts I have read, if I was to run a FULL sample - it will only work for the one run - then the next set would be a smaller set as required - so the sp_updatestats even if we select the resample option would still be from the smaller set.
Has anyone experienced anything like this, and have any suggestions?
April 9, 2010 at 2:58 am
Firstly - I'd personally have async set to on. With this option, a copy of the old stats are held while the new stats are being generated and used by SQL Server to service requests that would otherwise be blocked.
Also, when you run the update, queries in cache which are affected by the update (basically, rely on the updates stats) will be recompiled to take advantage of the new stats when next run which will have an impact for a while
April 9, 2010 at 3:09 am
Why are you running sp_updatestats on the database? why these cant be done off peak hours. If you really require more complex index maintenance (if the size is big) try any of the scripts available in scripts section.
April 9, 2010 at 5:40 am
Thanks for the info about the async option - I will look into that further.
I am actually doing the sp_updatestats after hours - however it seems a few hours later that the issues arise - and I need to perform the updatestats again for it to fix itself. I have it setup on a nightly cycle once a night to perform the stats upate outside of normal business hours. It almost seems like that the first set of stats (thats the nightly cycle after hours) causes a bad update - then eventually causes high cpu / system resources. Once I execute another manual sp_updatestats when the issue is detected it is resolved until the next time the scheduling occurs.
Could a data load at a similar time the sp_updatestats is run or just afterwards cause the issue that I'm seeing? Is there any checks you can advise to be done after the scheduled check to prove if it is caused by a bad stats update?
April 9, 2010 at 6:18 am
sp_updatestats uses a sampling methodology to build the statistics for the table. This means that it's not looking at all the data available to build the statistics. Most of the time, depending on the distribution of the data that is being sampled, this is not an issue. But in some cases this causes problems.The best way around it is to identify those edge cases and then run UPDATE STATISTICS WITH FULL SCAN on the table or index that is giving you problems.
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply