SQL SERVER – Disable Statistics Update for a Single Table

Statistics are very important for any SQL Server database as they are the primary driver of the SQL Server Performance. I discuss this topic in detail in the Comprehensive Database Performance Health Check. I help my client to set them up most efficiently for their database and server so they do not face performance problems. Recently I had encountered an interesting issue with one table where we had to disable statistics update for a single table. Let us learn how we can do that.

Disable Statistics Update

If you have attended my training or any consulting engagement that you know that I prefer to keep the auto-create and auto-update statistics on for your database. They are very vital and critical for the database. Lots of people usually keep it disable and run the nightly script to update that. While that method worked in the past and often has success now it is not something that works in modern times. I have seen on many occasions that not working as per the expectation.

Recently, one of my clients had an interesting situation when they wanted to run the nightly script one of the table always updated with the statistics which was not working for most of the queries. It was a very interesting and unique situation for me to work with as well and I must admit that it looked like a bug to me.

In any case, after experimenting, we figured out that it would be best to disable auto-update statistics or any statistics update for the table. We were able to do that for a specific table using the following syntax.

Note: Please note that this is a unique situation and you should not see as a generic solution to your performance problems. Statistics updates are very critical and one should for sure continue to do that.

UPDATE STATISTICS SchemaName.TableName
    WITH FULLSCAN, NORECOMPUTE;

If you want to auto-update the statistics in the future you can use the following syntax:

UPDATE STATISTICS SchemaName.TableName 
     WITH FULLSCAN;

Here is my previous blog post on the same topic: SQL SERVER – Disable Statistics Update on a Specific Table. I frequently create videos on SQL Server Performance Tuning on my YouTube Channel.

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