Індекси, яких не вистачає в БД

SELECT migs.group_handle, mid.*, -- Повний індекс з INCLUDE 'CREATE NONCLUSTERED INDEX ' + QUOTENAME(IndexNames.FullIndexName) + ' ON ' + mid.statement + ' (' + IndexColumns.KeyColumnsSql + ')' + CASE WHEN mid.included_columns IS NULL THEN '' ELSE ' INCLUDE (' + mid.included_columns + ')' END AS create_index_script, -- Індекс тільки з основними ключовими полями, без INCLUDE 'CREATE NONCLUSTERED INDEX ' + QUOTENAME(IndexNames.KeyOnlyIndexName) + ' ON ' + mid.statement + ' (' + IndexColumns.KeyColumnsSql + ')' AS create_index_script_without_include, migs.avg_total_user_cost, migs.user_seeks FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle CROSS APPLY ( SELECT -- Поля ключа у вигляді SQL: -- [DepartmentId], [Status], [DateOfApprove] COALESCE(mid.equality_columns, '') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END + COALESCE(mid.inequality_columns, '') AS KeyColumnsSql, -- Поля ключа у вигляді частини назви: -- DepartmentId_Status_DateOfApprove REPLACE( REPLACE( REPLACE( REPLACE( COALESCE(mid.equality_columns, '') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + COALESCE(mid.inequality_columns, ''), '[', '' ), ']', '' ), ', ', '_' ), ',', '_' ) AS KeyColumnsName, -- INCLUDE-поля у вигляді частини назви: -- Amount_DocumentType_DebtWriteOffMode CASE WHEN mid.included_columns IS NULL THEN NULL ELSE REPLACE( REPLACE( REPLACE( REPLACE( mid.included_columns, '[', '' ), ']', '' ), ', ', '_' ), ',', '_' ) END AS IncludeColumnsName ) AS IndexColumns CROSS APPLY ( SELECT -- Назва без INCLUDE 'IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' + IndexColumns.KeyColumnsName AS KeyOnlyIndexNameLong, -- Повна назва з INCLUDE 'IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' + IndexColumns.KeyColumnsName + CASE WHEN IndexColumns.IncludeColumnsName IS NULL THEN '' ELSE '_Include_' + IndexColumns.IncludeColumnsName END AS FullIndexNameLong ) AS LongNames CROSS APPLY ( SELECT -- Обрізаємо до 128 символів, якщо потрібно. -- В кінці залишаємо group_handle для унікальності. CASE WHEN LEN(LongNames.KeyOnlyIndexNameLong) <= 128 THEN LongNames.KeyOnlyIndexNameLong ELSE LEFT( LongNames.KeyOnlyIndexNameLong, 128 - LEN(CONVERT(varchar(20), migs.group_handle)) - 1 ) + '_' + CONVERT(varchar(20), migs.group_handle) END AS KeyOnlyIndexName, CASE WHEN LEN(LongNames.FullIndexNameLong) <= 128 THEN LongNames.FullIndexNameLong ELSE LEFT( LongNames.FullIndexNameLong, 128 - LEN(CONVERT(varchar(20), migs.group_handle)) - 1 ) + '_' + CONVERT(varchar(20), migs.group_handle) END AS FullIndexName ) AS IndexNames ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;