Recently I found an interesting situation with one of my clients when working on Comprehensive Database Performance Health Check. It was about one particular statistics not getting auto-updated on the table. After careful investigation, we figured out the cause for not auto-updating statistics. Let us learn about that today.
Real-World Scenario
During the consultancy senior, DBA showed me a very strange scenario that one particular statistics was never getting updated automatically even though the threshold of the statistics updated was met. They always had to manually update that one particular statistics.
Now while it was alright for them to update statistics for that one particular statistics manually, they really wanted to know what is the reason behind the strange behaviour of that one particular statistics.
After careful investigation and spending time understanding their system, we figured out the cause for it. When they originally created their table, they had created an index along with that table. Due to some strange reason, they had included the keyword STATISTICS_NORECOMPUTE = ON while they created the index.
When STATISTICS_NORECOMPUTE is set to ON value Out-of-date statistics are not automatically recomputed. Auto-updating of the statistics is disabled. This was the reason, the table was not getting updated at all when the system was updating the statistics on the same table.
The fix of this particular issue is very simple. Let us see how we can enable auto-updating the statistics if they are disabled.
Auto-Updating Statistics
If you want to enable auto-update for any particular statistics you can simply do that by running the following syntax.
UPDATE STATISTICS SchemaName.TableName WITH FULLSCAN;
Here are a few additional blog posts which are on the similar topics:
- How to Enable Auto Update Statistics and Auto Create Statistics with T-SQL – Interview Question of the Week #108
- SQL SERVER – Enable Automatic Statistic Update on Database
- SQL SERVER – Drop All Auto Created Statistics
- SQL SERVER – Disable Statistics Update on a Specific Table
Reference: Pinal Dave (http://blog.SQLAuthority.com)
