Working with SQL Server Statistics #SQL

sql_statistics.sql

-- Просмотреть свойства статистики
SELECT s.*
FROM sys.stats s
JOIN sys.objects o ON s.[object_id] = o.[object_id]
WHERE o.is_ms_shipped = 0

-- Скрипт по автоматическому обновлению устаревшей статистики
-- Критерий устаревания статистики в каждой конкретной ситуации может быть свой. В данном примере — 1 день
DECLARE @DateNow DATETIME
SELECT @DateNow = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
    SELECT '
    UPDATE STATISTICS [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] [' + s.name + ']
        WITH FULLSCAN' + CASE WHEN s.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'
    FROM sys.stats s WITH(NOLOCK)
    JOIN sys.objects o WITH(NOLOCK) ON s.[object_id] = o.[object_id]
    WHERE o.[type] IN ('U', 'V')
        AND o.is_ms_shipped = 0
        AND ISNULL(STATS_DATE(s.[object_id], s.stats_id), GETDATE()) <= @DateNow
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL
EXEC sys.sp_executesql @SQL


-- В некоторых случаях слишком частное обновление статистики для больших таблиц может заметно снижать производительность 
-- базы данных, поэтому далее приводится модифицированная версия  скрипта, которая для больших таблиц обновляет статистику реже
DECLARE @DateNow DATETIME
SELECT @DateNow = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
    SELECT '
    UPDATE STATISTICS [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] [' + s.name + ']
        WITH FULLSCAN' + CASE WHEN s.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'
    FROM (
        SELECT 
              [object_id]
            , name
            , stats_id
            , no_recompute
            , last_update = STATS_DATE([object_id], stats_id)
        FROM sys.stats WITH(NOLOCK)
        WHERE auto_created = 0
            AND is_temporary = 0
    ) s
    JOIN sys.objects o WITH(NOLOCK) ON s.[object_id] = o.[object_id]
    JOIN (
        SELECT
              p.[object_id]
            , p.index_id
            , total_pages = SUM(a.total_pages)
        FROM sys.partitions p WITH(NOLOCK)
        JOIN sys.allocation_units a WITH(NOLOCK) ON p.[partition_id] = a.container_id
        GROUP BY 
              p.[object_id]
            , p.index_id
    ) p ON o.[object_id] = p.[object_id] AND p.index_id = s.stats_id
    WHERE o.[type] IN ('U', 'V')
        AND o.is_ms_shipped = 0
        AND (
              last_update IS NULL AND p.total_pages > 0 -- never updated and contains rows
            OR
              last_update <= DATEADD(dd, 
                CASE WHEN p.total_pages > 4096 -- > 4 MB
                    THEN -2 -- updated 3 days ago
                    ELSE 0 
                END, @DateNow)
        )
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL
EXEC sys.sp_executesql @SQL