December 30, 2014 at 9:48 am
What are the best practices to keep the statistics up to date? Specifically column statistics.
1. Do you usually update column statistics (with specific sampling) as nightly job or let SQL update them as it needs?
2. Same question for index statistics with fragmentation < 30%. (We rebuild indexes with frag. >= 30 and that process updates statistics with full scan so no need to update them twice.)
3. Also, do you need any kind of statistics maintenance on system database?
These are just few questions to get start the discussion. I would really appreciate any reply.
December 30, 2014 at 11:06 am
As you have mentioned correctly - the statistics will be updated automatically if an index will be rebuild.
BUT...
There are different problems in the systems which forces a different maintenance for statistics maintenance.
One of the most occuring problems we have in VLDB is the "Ascending Key Problem".
http://support.microsoft.com/kb/2952101/en-us
https://www.simple-talk.com/sql/database-administration/statistics-on-ascending-columns/
This problem caused us a manual update every two hours.
There are different workarounds but - as always - it depends and not all confirmed as "best practise" by Microsoft!
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
December 30, 2014 at 12:48 pm
Thanks for the reply Uwe Ricken. You are right, there is no "one size fits all" solution for this. Purpose of my questions is basically to know "what not to do". We already have nightly job setup by former DBA to rebuild/reorganize indexes based on fragmentation and update ALL column statistics with FULL SCAN. And I am trying to understand if we really need to do that. All of them are staging database, we have separate process to maintain reporting database.
I think I should start reducing the frequency of statistics update and check for performance decrease.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply