Auto Create Statistics – Do the Stats Tell the Story?

When we query for data, we don’t always think about the magic that goes into efficiently returning results. One vital piece to this magic is statistics. Statistics in SQL Server are histograms that are used by the query optimizer to determine an optimal execution plan when executing a query. Let’s take a look at the different ways to check your statistics settings and make sure statistics are being automatically created.

Is My Database Creating Statistics?

We can check in SSMS to find out if a database is automatically creating statistics. In Object Explorer, right click on a database, select Properties, and click into the Options page.

In the first section, we’ll see Auto Create Statistics. In this case, it is already set to true.

Gotta Check ‘Em All

The steps above show how to check one database but what if you need to check multiple databases? Following the steps above for every database would be a bit tedious. The better option is to run a query to check in sys.databases:

SELECT name, is_auto_create_stats_on
FROM sys.databases;
GO

If you see a 1 for is_auto_create_stats_on, then this feature is enabled.

We can turn AUTO_CREATE_STATISTICS on with a script and verify the change from sys.databases. Note that in the screenshot above, StackOverflow2013 has a 0 for is_auto_create_stats_on. Let’s run the following to make that a 1:

ALTER DATABASE StackOverflow2013 
SET AUTO_CREATE_STATISTICS ON;
GO

Can You Create Your Own?

Since there’s an option for stats to be created automatically, that must mean there’s a way to create them yourself, right?

You can create your own statistics but that does not necessarily mean you should. It’s only recommended in certain situations. Microsoft says to consider creating statistics if:

Microsoft also recommends leaving AUTO_CREATE_STATISTICS turned on even if you’re rolling up your sleeves to create your own.

Odds are that this setting is already enabled and you have nothing to change. If you’re working on a new database or one that’s new to you, it might be worth adding this to your checks to see if anyone tried to get too cute before you came along.

Thanks for reading!