Database Reference
In-Depth Information
Status of Auto Create Statistics
You can verify the current setting for auto create statistics by running a query against the
sys.databases
system table.
SELECT is_auto_create_stats_on
FROM sys.databases
WHERE [name] = 'AdventureWorks2012';
A return value of 1 means enabled, and a value of 0 means disabled.
You can also verify the status of specific indexes using the
sp_autostats
system stored procedure, as shown in
the following code. Supplying any table name to the stored procedure will provide the configuration value of auto
create statistics for the current database under the
Output
section of the global statistics settings.
USE AdventureWorks2012;
EXEC sys.sp_autostats
'HumanResources.Department';
Figure
12-30
shows an excerpt of the preceding
sp_autostats
statement's output.
Figure 12-30.
sp_autostats
output
A return value of
ON
means enabled, and a value of
OFF
means disabled. This stored procedure is more useful
when verifying the status of auto update statistics, as explained earlier in this chapter.
Status of Auto Update Statistics
You can verify the current setting for auto update statistics, and auto update statistics asynchronously, in a similar
manner to auto create statistics. Here's how to do it using the function
DATABASEPROPERTYEX
:
SELECT DATABASEPROPERTYEX('AdventureWorks2012', 'IsAutoUpdateStatistics');
Here's how to do it using
sp_autostats
.
USE AdventureWorks2012;
EXEC sp_autostats
'Sales.SalesOrderDetail';
Analyzing the Effectiveness of Statistics for a Query
For performance reasons, it is extremely important to maintain proper statistics on your database objects. Issues with
statistics can be fairly common. You need to keep your eyes open to the possibility of problems with statistics while
analyzing the performance of a query. If an issue with statistics does arise, then it can really take you for a ride.
In fact, checking that the statistics are up-to-date at the beginning of a query-tuning session eliminates an easily fixed
problem. In this section, you'll see what you can do should you find statistics to be missing or out-of-date.
