Statistics play a crucial role in the performance of any SQL Server database. They provide valuable information to the query optimizer, helping it make informed decisions on how to execute queries efficiently. However, there may be situations where you need to disable statistics update for a specific table. In this article, we will explore how to do that.

Before we proceed, it’s important to note that disabling statistics update should be considered as a unique solution for specific scenarios and not as a generic approach to performance problems. In most cases, it is recommended to keep auto-create and auto-update statistics enabled for your database.

Let’s consider a scenario where you have a nightly script that updates statistics for all tables in your database. However, you encounter a situation where one particular table’s statistics update is causing performance issues for most of the queries. In such cases, disabling statistics update for that specific table can be a viable solution.

To disable statistics update for a specific table, you can use the following syntax:

UPDATE STATISTICS SchemaName.TableName
WITH FULLSCAN, NORECOMPUTE;

This command will update the statistics for the specified table with a full scan and prevent any further automatic updates.

If you want to re-enable auto-update statistics for the table in the future, you can use the following syntax:

UPDATE STATISTICS SchemaName.TableName
WITH FULLSCAN;

By executing this command, the statistics for the table will be updated with a full scan, allowing automatic updates to occur again.

It’s important to remember that disabling statistics update should be done cautiously and only after thorough analysis of the specific performance issue. It is recommended to consult with a database professional or SQL Server expert before implementing such changes.

For more information on this topic, you can refer to my previous blog post: SQL SERVER – Disable Statistics Update on a Specific Table.

Remember, statistics are vital for SQL Server performance optimization, and it’s crucial to regularly update them to ensure optimal query execution. Disabling statistics update should be considered as a last resort when all other options have been exhausted.

Stay tuned for more articles and videos on SQL Server performance tuning on my YouTube Channel.