SQL SERVER – Not Auto-Updating Statistics with STATISTICS_NORECOMPUTE

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.

SQL SERVER - Not Auto-Updating Statistics with STATISTICS_NORECOMPUTE auto-updating-statistics-800x234

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:

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About: admin