Auto Update or Auto Create Statistics Disabled

SQL Server relies on auto create statistics and auto update statistics to provide accurate data distribution insights to the Query Optimizer. If either of these features is disabled, the database’s ability to generate efficient query plans can be compromised, resulting in suboptimal performance.

Why Auto Create or Auto Update Statistics Are Important

  • Efficient Query Plans: With accurate statistics, SQL Server can create more efficient query plans, speeding up query execution.
  • Improved Performance: Automatically generated and updated statistics ensure the optimizer works with up-to-date information, which leads to better overall performance.
  • Minimized Manual Intervention: Enabling these features reduces the need for manual statistics updates.

How to Check Auto Create and Auto Update Statistics Settings To check the current statistics settings for a database, you can use this query:

SELECT name, is_auto_create_stats_on, is_auto_update_stats_on 
FROM sys.databases
WHERE name = 'WhippetWorks';

Suggested Action

Ensure auto create statistics and auto update statistics are enabled to improve query performance. Here’s how to enable them:

How to Enable Auto Update or Auto Create Statistics for a Database

USE [master]
GO
ALTER DATABASE [WhippetWorks] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [WhippetWorks] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT;
GO

How Aireforge Detects Disabled Statistics

Aireforge Advisor scans your database configurations and flags any instances where auto create statistics or auto update statistics is disabled. This ensures that the Query Optimizer has up-to-date statistics for efficient query planning. Aireforge provides a script to fix these settings automatically if they’re turned off.