如何为数据库启用AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS

Question

我有一个项目数据库，该数据库维护来自不同环境(dev、test、prod、predprod.)的不同数据库，我需要检查AUTO_CREATE_STATISTICS和AUTO_UPDATE_STATISTICSis是否针对每个DB设置为OFF/ON，以及它的OFF是否将其更改为ON。

ALTER DATABASE  DB
SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE  DB
SET AUTO_UPDATE_STATISTICS ON

我怎样才能做到这一点？

Answer 1

这使用动态SQL查询。它不包括系统数据库。检查is_auto_create_stats_on或is_auto_update_stats_on 0 OFF并将其更改为ON

declare @sql    nvarchar(max);

; with 
db as
(
    select  name, is_auto_create_stats_on, is_auto_update_stats_on 
    from    sys.databases
    where   name    not in ('master', 'tempdb', 'model', 'msdb')
    and     (
                is_auto_create_stats_on = 0
            or  is_auto_update_stats_on = 0
            )
)
select  @sql    = isnull(@sql + char(13), '')
                + 'ALTER DATABASE ' + quotename(db.name) + ' '
                + 'SET ' + o.opt + ' ON;'
from    db 
        cross apply
        (
            select  opt = 'AUTO_CREATE_STATISTICS'
            where   is_auto_create_stats_on = 0

            union all

            select  opt = 'AUTO_UPDATE_STATISTICS'
            where   is_auto_update_stats_on = 0
        ) o

-- print out for verification before execute
print   @sql
exec    sp_executesql @sql

您可以在实际执行之前屏蔽sp_executesql并验证结果。

Answer 2

DECLARE @SQL    NVARCHAR(MAX) = ''

-- getting the commands list
SET @SQL = (
    SELECT '' + x.cmd
    FROM (
        select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_CREATE_STATISTICS ON;' AS cmd
        from sys.databases
        WHERE database_id > 4
            AND is_auto_create_stats_on = 0
        UNION ALL
        select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_UPDATE_STATISTICS ON;'
        from sys.databases
        WHERE database_id > 4
            AND is_auto_update_stats_on = 0
    ) x
    FOR XML PATH('')
);

IF @SQL IS NOT NULL BEGIN
    SELECT @SQL;

--uncomment to execute
--  EXEC sp_executesql @SQL;
END