Quick Scan Report – Auto Create Statistics Not Enabled

What this check looks for

Databases where is_auto_create_stats_on = 0 in sys.databases.

Why it matters

Statistics are how the query optimizer knows how many rows to expect, and every plan decision follows from that number.

Index choice, join type, join order, memory grant, whether to go parallel: all of them are chosen from an estimated row count. With AUTO_CREATE_STATISTICS on, the optimizer notices a column being filtered on, has no statistics for it, and creates them itself, automatically, as a lightweight single column statistic named _WA_Sys_.... It costs almost nothing and it happens without anybody asking.

With it off, the optimizer has nothing, so it falls back on fixed guesses. Those guesses do not resemble any real data. The consequences compound through the plan:

• Wrong index chosen, or a scan where a seek was available.
• Wrong join type. A nested loop join chosen for an estimated 1 row that turns out to be 500,000 rows is the classic catastrophic plan, and it runs for hours.
• Wrong memory grant. Too little and the query spills to tempdb; too much and it starves everything else, which has its own check on this report.
• Wrong parallelism decision, in either direction.

The symptom is characteristic and hard to chase: a query that is far slower than its shape suggests, with an execution plan whose estimated rows and actual rows disagree by orders of magnitude, and no missing index suggestion that helps.

It is almost never a deliberate choice. The setting exists for a narrow case: an application that manages every statistic itself, which in practice means a handful of vendor products that explicitly require it. Far more often it was copied from a template, inherited from model, or set by an installer that had a reason two decades ago.

How to confirm it yourself

SELECT [name],
       [is_auto_create_stats_on],
       [is_auto_update_stats_on],
       [is_auto_update_stats_async_on],
       [compatibility_level]
  FROM sys.databases WITH (NOLOCK)
 WHERE [is_auto_create_stats_on] = 0
 ORDER BY [name];

Check is_auto_update_stats_on in the same breath. Auto create without auto update is statistics that are made once and never refreshed, which is its own problem, and the two are usually turned off together.

What statistics exist in an affected database, and how stale they are:

SELECT OBJECT_SCHEMA_NAME(s.[object_id]) AS [schema_name],
       OBJECT_NAME(s.[object_id])        AS [table_name],
       s.[name]                          AS [statistic],
       s.[auto_created],
       sp.[last_updated],
       sp.[rows],
       sp.[rows_sampled],
       sp.[modification_counter]
  FROM sys.stats AS s WITH (NOLOCK)
 CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.[stats_id]) AS sp
 WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
 ORDER BY sp.[last_updated];

A database with auto_created = 0 on every row is the signature: the only statistics present are the ones that came with an index, and every non-indexed column being filtered on is a guess.

How to fix it

Turn it on. It is online, immediate, and it takes no locks:

ALTER DATABASE [YourDatabase] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [YourDatabase] SET AUTO_UPDATE_STATISTICS ON;

Check whether the vendor really requires it off before changing a database you do not own. This is one of the few settings where a genuine requirement occasionally exists, and it will be documented if it is real. “We always turn it off” is not a requirement.

Then help it catch up. Turning the setting on does not create the missing statistics retroactively; it creates them the next time the optimizer wants one. To get there faster, update statistics across the database once:

EXEC sp_updatestats;

or, more deliberately, with a full scan on the tables that matter:

UPDATE STATISTICS [dbo].[YourTable] WITH FULLSCAN;

Expect plans to change, and that is the point. Some queries will get faster immediately. A few may get slower, because they were accidentally benefiting from a bad estimate. On SQL Server 2016 and later, Query Store makes that visible and reversible, and it is worth turning on before making this change on a busy production database.

Fix model too, so new databases do not arrive with it off.

How long it takes

About half an hour for the setting and an initial statistics update. Watching for plan changes afterwards is worth a day of attention on a busy instance.

Related reports

Related checks

Frequently asked questions

Does creating statistics automatically slow queries down? The creation is a sampled read of one column and takes milliseconds to seconds. It happens once. The bad plans it prevents cost far more.

Our vendor says to turn it off. Ask them to point at the documentation. A small number of products genuinely manage their own statistics; most such instructions are folklore carried forward.

We update statistics nightly, so do we need it? Updating maintains statistics that exist. It does not create one for a column that has never had one, which is what this setting does.

What are the WA_Sys statistics? Those are the automatically created ones. A database with none, on tables that are queried, is this finding.