Friday, August 26, 2011

How to check AUTO_CREATE_STATISTICS is enabled for database

use master
go
SELECT name AS 'Name',
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO
-- Also you can check list of statistics created on your database
use Mydb
go
SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

When updating statistics with UPDATE STATISTICS or sp_updatestats, Microsoft recommend keeping
AUTO_UPDATE_STATISTICS set to ON so that the query optimizer continues to routinely update statistics.