SQL SERVER – Disable Statistics Update on a Specific Table

Every single day there is a new experience when it is about SQL Server Performance Tuning. Just earlier today when we were fixing the performance of a database during Comprehensive Database Performance Health Check, we found a unique requirement to disable statistics updates on a table.

Scenario in Brief

During the SQL Server Performance Health Check scenario, we found that there was a table that always performed very well when it statistics of it were updated with the full scan. The table was huge and almost nearing the size of the TB. As it was very big it was very difficult to update the statistics on it all the time. Whenever auto-updated fired with the sampled statistics update, it started to give poor performance.

This presented a unique situation to the client as they were updating the table quite frequently and that was frequently crossing the threshold which was leading to trigger auto-update.

The requirement for the user was to disable statistics update.

Disable Statistics Update

If you want to stop auto-update for any particular statistics you can simply do that by running the following syntax.

UPDATE STATISTICS SchemaName.TableName
    WITH FULLSCAN, NORECOMPUTE;  

The above syntax will fully update statistics and also will disable the future statistics update by auto-update statistics. To re-enable the AUTO_UPDATE_STATISTICS option behavior on the table, we have to run the run UPDATE STATISTICS again without the NORECOMPUTE option or run sp_autostats. Here is the script for it.

UPDATE STATISTICS SchemaName.TableName 
     WITH FULLSCAN; 

The same client of mine is also using the MongoDB. Here are some of the relevant blog posts on MongoDB.

• Foundations of Document Databases with MongoDB – Video Course
• SQL Terms vs MongoDB Terms
• MongoDB Compass – Missing a Schema Section
• MongoDB Fundamentals – Getting Started – Day 1 of 6
• MongoDB Fundamentals – CRUD: Creating Objects – Day 2 of 6
• MongoDB Fundamentals – CRUD: Reading Objects – Day 3 of 6
• MongoDB Fundamentals – CRUD: Updating Objects – Day 4 of 6
• MongoDB Fundamentals – CRUD: Deleting Objects – Day 5 of 6
• MongoDB Fundamentals – Mapping Relational SQL – Day 6 of 6

Reference: Pinal Dave (https://blog.sqlauthority.com)