DECLARE @FILL_FACTOR TINYINT = 90, @FILE_GROUP sysname = 'PRIMARY'; -- verify if FILL FACTOR has a correct value IF @FILL_FACTOR NOT BETWEEN 1 AND 100 BEGIN RAISERROR ('FILL FACTOR parameter must be valued between 1 and 100. Given value is %d', 16, 1, @FILL_FACTOR); RETURN; END; -- verifiy if FILE GOUP name exists IF NOT EXISTS(SELECT * FROM sys.filegroups WHERE name = @FILE_GROUP) BEGIN RAISERROR ('FILE GROUP %s does not exists in this database.', 16, 1, @FILE_GROUP); RETURN; END; -- verify that the scanning depth of the indexing diagnosis is sufficient DECLARE @SQL_START DATETIME = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info); DECLARE @SQL_START_STR CHAR(21) = CONVERT(CHAR(19), @SQL_START, 121); IF @SQL_START > DATEADD(day, -31, GETDATE()) BEGIN RAISERROR ('The server restarted on %s which is insufficient for a good indexing diagnosis. Try again in a few days', 16, 1, @SQL_START_STR); RETURN; END; WITH TXC AS (-- candidate indexes SELECT ROW_NUMBER() OVER(ORDER BY statement, equality_columns, inequality_columns) AS N, object_id, statement, equality_columns, inequality_columns, included_columns, COALESCE(equality_columns + N', ' + inequality_columns, equality_columns, inequality_columns) AS KEY_COLS, 1 + LEN(included_columns) - LEN(REPLACE(included_columns, N',', N'')) AS NB_INC, (SELECT COUNT(*) FROM sys.columns AS c WHERE is_computed = 0 AND is_filestream = 0 AND c.object_id = mid.object_id) as NB_COL FROM sys.dm_db_missing_index_details AS mid WHERE database_id = DB_ID() ), AKX AS (-- keys of already indexes SELECT STUFF((SELECT N', ' + N'[' + name + N']' FROM sys.index_columns AS ic JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.index_id BETWEEN 1 AND 999 AND ic.is_included_column = 0 AND ic.object_id = T.object_id AND ic.index_id = i.index_id ORDER BY ic.key_ordinal FOR XML PATH (N'')), 1, 2, N'') AS K, i.object_id, i.name AS INDEX_NAME, i.index_id FROM (SELECT DISTINCT object_id FROM TXC) AS T JOIN sys.indexes AS i ON T.object_id = i.object_id ), XSK AS ( -- finding existing similar indexes SELECT N, INDEX_NAME, K, index_id, LEN(KEY_COLS) - LEN(REPLACE(KEY_COLS, K, '')) AS SIMILARITY1, LEN(K) - LEN(REPLACE(K, KEY_COLS, '')) AS SIMILARITY2, RANK() OVER(PARTITION BY N ORDER BY LEN(KEY_COLS) - LEN(REPLACE(KEY_COLS, K, '')) DESC) AS R1, RANK() OVER(PARTITION BY N ORDER BY LEN(K) - LEN(REPLACE(K, KEY_COLS, '')) DESC) AS R2 FROM TXC LEFT OUTER JOIN AKX ON TXC.object_id = AKX.object_id AND REPLACE(REPLACE(KEY_COLS, N'[', N''), N']', N'') LIKE REPLACE(REPLACE(K, N'[', N''), N']', N'') + N'%' OR REPLACE(REPLACE(K, N'[', N''), N']', N'') LIKE REPLACE(REPLACE(KEY_COLS, N'[', N''), N']', N'') + N'%' ) -- ginving the diagnosis SELECT TXC.object_id, statement, NB_INC, NB_COL, equality_columns, inequality_columns, included_columns, TXC.N, KEY_COLS AS CANDIDATE_KEY_COLUMNS, K AS KEY_INDEX_SIMILAR, STUFF((SELECT N', ' + N'[' + name + N']' FROM sys.index_columns AS ic JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.index_id = XSK.index_id AND ic.is_included_column = 1 AND ic.object_id = TXC.object_id ORDER BY ic.index_column_id FOR XML PATH (N'')), 1, 2, N'') AS INCLUDED_COLS_SIMILAR, INDEX_NAME AS INDEX_NAME_SIMILAR, index_id AS INDEX_ID_SIMILAR, N'CREATE INDEX X_' + REPLACE(CAST(NEWID() AS NVARCHAR(36)), N'-', N'_') + N'_' + CONVERT(CHAR(8), GETDATE(), 112) + N' ON ' + statement + N' (' + KEY_COLS + ') ' + CASE WHEN included_columns IS NULL THEN N'' ELSE N' INCLUDE(' + included_columns + ') ' END + N' WITH(FILLFACTOR = 90);' AS SQL_CREATE_INDEX FROM TXC LEFT OUTER JOIN XSK ON TXC.N = XSK.N AND (R1 = 1 OR R2 = 1) ORDER BY statement, KEY_COLS;