How to Enable Auto Update Statistics and Auto Create Statistics with T-SQL – Interview Question of the Week #108

Question: How can I generate T-SQL to enable auto-create and auto-update statistics across many databases?

Nitin wrote to me after watching my GroupBy performance session. He had enabled the automatic statistics options on one important database, seen an improvement, and now faced a much less exciting job: checking more than a hundred databases. I loved the question because it asks for an inspectable shortcut, not a magic switch.

AUTO_CREATE_STATISTICS lets the optimizer create useful single-column statistics when needed. AUTO_UPDATE_STATISTICS lets it refresh statistics that may have become stale after data changes. First see which online user databases have either option off. This query generates statements for review; it does not change a database:

SELECT d.name AS DatabaseName,
       N'ALTER DATABASE ' + QUOTENAME(d.name)
         + N' SET AUTO_CREATE_STATISTICS ON;' AS ProposedCommand
FROM sys.databases AS d
WHERE d.database_id > 4
  AND d.state_desc = N'ONLINE'
  AND d.is_auto_create_stats_on = 0
UNION ALL
SELECT d.name AS DatabaseName,
       N'ALTER DATABASE ' + QUOTENAME(d.name)
         + N' SET AUTO_UPDATE_STATISTICS ON;' AS ProposedCommand
FROM sys.databases AS d
WHERE d.database_id > 4
  AND d.state_desc = N'ONLINE'
  AND d.is_auto_update_stats_on = 0
ORDER BY DatabaseName, ProposedCommand;

QUOTENAME matters. A database name may contain spaces or punctuation; a generated ALTER DATABASE statement must quote the identifier. The original quick script concatenated name directly, which would fail for such names. Review the output and run the statements only on databases you intend to change. An availability-group secondary or an application with special maintenance requirements deserves separate handling.

After executing the reviewed statements, check the actual settings:

SELECT name,
       is_auto_create_stats_on AS AutoCreateOn,
       is_auto_update_stats_on AS AutoUpdateOn
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Enabling these options can improve estimates when statistics were missing or stale, but it does not promise the same performance improvement in every database. Measure the workload before and after, and look at individual query plans where the estimates still need work.

The Performance Tuning Practical Workshop also covers how statistics affect estimates. Thanks to Nitin for turning a good webinar question into a practical script.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.