Is there any safe way to ‘update statistics’ without causing major slow downs?
As far as a safe way to update statistics. It is best to rebuild statistics in the off hours time as it will have some impact on the system. You can do a couple of things to help:
- Reduce the sample percentage. A full scan will scan the entire table. 25% will only touch a quarter of the table, speeding things up. You will want to test this because too small of a sampling percentage can throw off how the index is used. I found on one system that I worked on that a 25% scan ratio was great for all but one table, so for that one table I did a full scan and for all the others I scanned 25% of the table to improve performance.
- Only rebuild statistics for tables that have changed. There is an only modified option on the Ola hallagren maintenance plan to rebuild statistics. Or if you are using your own plan, just rebuild those that have changes.
- Don’t use the standard SQL Server maintenance plan, or the old school xp_sqlmaint to rebuild statistics. There just isn’t enough control with these tools.
If you need help with your statistics maintenance, or SQL Server performance, Stedman Solutions can help.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!