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;
