Auto Create Stats Not Enabled
Blitz Result: Auto Create Stats Not Enabled
SQL Server watches queries to figure out what fields users often filter on. When a query could benefit from knowing more about a particular field’s distribution, SQL Server decides to automatically create statistics to support the query. By default, this happens automatically. This part of our SQL Server sp_Blitz® script checks sys.databases looking for databases where auto-create-stats has been turned off.
Is this Always Bad?
Some applications (cough SHAREPOINT) are written very carefully, and the developers hand-craft artisanal statistics and keep them updated just like they like it. If you’re running a vendor app that specifically instructs you to leave this setting off because it runs better that way, follow their installation and performance guides.
But if things get weird and it’s not a well established application, you may want to ask them if they’re really sure you need this off.
SQL Server Really Thinks YOu Need Auto-Created Statistics
One problem with read-only databases was that the auto-create stats feature didn’t work in the old days. Then, in SQL Server 2012, we got temporary statistics for read-only databases. It’s that important.
To Fix the Problem
In SQL Server Management Studio, you can right-click on each database and go into its properties to change Auto Create Stats to Enabled. This takes effect immediately for queries executed from this point forward.
After the change, you can monitor overall system performance looking for improvements, especially around query execution plans.