SQLServer : Script to check and create missing multi-column stats on a table

 

Statistics are critical for SQL Server performance. By default, SQL Server creates statistics for columns  However, SQL Server isn’t yet quite smart enough to automatically create multi-column stats objects that might be helpful. 

So the below script compares the details provided by the missing index DMVs against the existing sys.stats and sys.stats_columns views to provide a list of potential new stats.

;WITH suggested_indexes AS

(

    SELECT ObjectName = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name)

        , Columns = STUFF((

            SELECT N', ' + QUOTENAME(mic.column_name)

            FROM sys.dm_db_missing_index_columns(mig.index_handle) mic 

            WHERE mic.column_usage = N'EQUALITY' 

                OR mic.column_usage = N'INEQUALITY' 

            ORDER BY mic.column_name FOR XML PATH(N'')

            ), 1, 2, '')

    FROM sys.dm_db_missing_index_groups mig

        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

        INNER JOIN sys.objects o ON mid.object_id = o.object_id

        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

    WHERE mid.database_id = DB_ID()    

)

, existing_stats AS

(

SELECT ObjectName = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name)

    , StatsName = s.name

    , AutoCreated = s.auto_created

    , UserCreated = s.user_created

    , StatsColumns = STUFF((SELECT N', ' + QUOTENAME(c.name)

        FROM sys.stats_columns sc 

            INNER JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id

        WHERE s.object_id = sc.object_id AND s.stats_id = sc.stats_id 

        ORDER BY sc.stats_column_id

        FOR XML PATH(N'')

        ), 1, 2, N'')

    , FilterDefinition = CASE WHEN s.has_filter = 1 THEN s.filter_definition ELSE N'' END

FROM sys.stats s

    INNER JOIN sys.objects o ON s.object_id = o.object_id

    INNER JOIN sys.schemas sch ON o.schema_id = sch.schema_id

WHERE o.is_ms_shipped = 0

)

SELECT *

    , CreateStmt = N'CREATE STATISTICS [stats_name_' 

       + CONVERT(nvarchar(30), CONVERT(bigint, CRYPT_GEN_RANDOM(4))) 

       + N'] ON ' + si.ObjectName + N'(' + si.Columns + N') WITH FULLSCAN;'

FROM suggested_indexes si

WHERE NOT EXISTS (SELECT 1

    FROM existing_stats es

    WHERE es.ObjectName = si.ObjectName

        AND es.StatsColumns = si.Columns

    )

GROUP BY si.ObjectName

    , si.Columns

ORDER BY si.ObjectName

    , si.Columns;