Monday, June 06, 2011

Auto create stats turned on sql database

If you have a query execution issues first thing you should look at your database  to check auto_create and auto_update stats is turned on or off?

select name, is_auto_create_stats_on ,is_auto_update_stats_on

from sys.databases
where name = 'AdventureWorks2008' -- your db name here

0 - turned off
1 - turned on

The following query will list out all dbs

SELECT name AS "DbName",

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