I have the following SQL script to use for our Microsoft Dynamics NAV Database (MS SQL Server). It looks for missing indexes in the database. We have the problem that the script also finds some tables which already have this index just missing a long list of "included columns" (most of the time 70%+ of the columns of the table). But we do not want to add those with like 10+ included columns. So I tried to filter out already existing indexes by using db name, table name and equality columns. Sadly I couldn't get it working and also not with the help of Chat GPT. So maybe someone can help me here :)
USE [master]
GO
SET NOCOUNT ON
GO
-- >> Missing Indexes <<
-- Filter-Values
declare @threshold_occurrence int, @threshold_impact int, @threshold_age int, @threshold_include int
set @threshold_occurrence = 10 -- Avg. Proposals per Day online; change on demand
set @threshold_impact = 90 -- Percent; change on demand
set @threshold_age = 14 -- last proposal date; change on demand
set @threshold_include = 10 -- maximum number of included columns used in CREATE; change on demand
--
declare @daysonline int
select @daysonline = datediff(dd, create_date, getdate()) + 1 from sys.databases where name = 'tempdb'
declare @dbname sysname, @tsql nvarchar(max)
declare db_cur cursor fast_forward for
select [name] from sys.databases
where [state] = 0
--and [name] not in ('master', 'model', 'msdb', 'tempdb', 'SSI') -- exclude databases
and [name] in ('XXXX') -- include databases
order by [name]
open db_cur
fetch next from db_cur into @dbname
while @@FETCH_STATUS = 0
begin
set @tsql =
'
USE [' + @dbname + ']
SELECT mig.index_handle, migs.user_seeks, migs.last_user_seek, migs.user_scans, migs.last_user_scan, migs.avg_user_impact,
CONVERT(decimal(18,2), migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01)) AS [index_advantage],
db_name(mid.database_id) as db, object_name(mid.object_id) as object, mid.equality_columns, mid.inequality_columns, mid.included_columns,
''CREATE INDEX [ssi_'' + convert(varchar, mig.index_handle) + ''_'' + replace(replace(replace(convert(varchar(30), getdate(), 120), ''-'', ''''), '':'', ''''), '' '', ''_'') + ''] ON ['' + OBJECT_SCHEMA_NAME(mid.[object_id]) + ''].['' + object_name(mid.object_id) + ''] '' + ''('' +
CASE WHEN mid.equality_columns is not null THEN mid.equality_columns ELSE '''' END +
CASE WHEN mid.equality_columns is null AND mid.inequality_columns is not null THEN mid.inequality_columns ELSE '''' END +
CASE WHEN mid.equality_columns is not null AND mid.inequality_columns is not null THEN '', '' + mid.inequality_columns ELSE '''' END + '')'' +
CASE WHEN (mid.included_columns is not null) AND ( (SELECT LEN(mid.included_columns) - LEN(REPLACE(mid.included_columns, '','', ''''))) < ' + convert(varchar(5), @threshold_include) + ' )
THEN '' INCLUDE ('' + mid.included_columns + '') ''
ELSE ''''
END + '' WITH (MAXDOP = 64, ONLINE = OFF, DROP_EXISTING = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);'' COLLATE DATABASE_DEFAULT as tsql
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)
WHERE (mid.database_id = db_id())
AND ((migs.user_seeks / ' + convert(varchar(5), @daysonline) + ') >= ' + convert(varchar(5), @threshold_occurrence) + ')
AND (migs.avg_user_impact >= ' + convert(varchar(5), @threshold_impact) + ')
AND (datediff(dd, migs.last_user_seek, getdate()) <= ' + convert(varchar(5), @threshold_age) + ') -- propsed within period
ORDER BY object_name(mid.object_id), replace(replace((mid.equality_columns + mid.inequality_columns), '', '', ''''), '','', ''''), mid.included_columns
COLLATE DATABASE_DEFAULT;
'
--print @tsql
exec sp_executesql @tsql
fetch next from db_cur into @dbname
end
close db_cur
deallocate db_cur
GO
I tried merging it with this script which finds existing indexes of a table:
DECLARE @TableName NVARCHAR(50) = 'Cronus$Sales Price'
;WITH CTE_INDEX_DATA AS (
SELECT
SCHEMA_DATA.name AS schema_name,
TABLE_DATA.name AS table_name,
INDEX_DATA.name AS index_name,
STUFF((SELECT ', ' + COLUMN_DATA_KEY_COLS.name + ' ' + CASE WHEN INDEX_COLUMN_DATA_KEY_COLS.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END -- Include column order (ASC / DESC)
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_KEY_COLS
ON T.object_id = INDEX_DATA_KEY_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS
ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_KEY_COLS
ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
FOR XML PATH('')), 1, 2, '') AS key_column_list ,
STUFF(( SELECT ', ' + COLUMN_DATA_INC_COLS.name
FROM sys.tables AS T
INNER JOIN sys.indexes INDEX_DATA_INC_COLS
ON T.object_id = INDEX_DATA_INC_COLS.object_id
INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS
ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
INNER JOIN sys.columns COLUMN_DATA_INC_COLS
ON T.object_id = COLUMN_DATA_INC_COLS.object_id
AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
WHERE INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
FOR XML PATH('')), 1, 2, '') AS include_column_list,
INDEX_DATA.is_disabled -- Check if index is disabled before determining which dupe to drop (if applicable)
FROM sys.indexes INDEX_DATA
INNER JOIN sys.tables TABLE_DATA
ON TABLE_DATA.object_id = INDEX_DATA.object_id
INNER JOIN sys.schemas SCHEMA_DATA
ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
WHERE TABLE_DATA.is_ms_shipped = 0
AND INDEX_DATA.type_desc IN ('NONCLUSTERED', 'CLUSTERED')
--AND INDEX_DATA.name NOT LIKE '%$%'
AND [TABLE_DATA].[object_id] = (select object_id from sys.objects where name = @TableName)
)
SELECT
[DUPE1].*
,(SELECT COUNT(*) FROM CTE_INDEX_DATA DUPE2
WHERE DUPE1.schema_name = DUPE2.schema_name
AND DUPE1.table_name = DUPE2.table_name
AND (DUPE1.key_column_list LIKE LEFT(DUPE2.key_column_list, LEN(DUPE1.key_column_list)) OR DUPE2.key_column_list LIKE LEFT(DUPE1.key_column_list, LEN(DUPE2.key_column_list)))
AND DUPE1.index_name <> DUPE2.index_name) AS [CountOfSimilarIndexes]
FROM CTE_INDEX_DATA DUPE1
--WHERE EXISTS /*Einschränkung auf Indexe, die in ähnlicher Form noch mal vorliegen*/
--(SELECT * FROM CTE_INDEX_DATA DUPE2
-- WHERE DUPE1.schema_name = DUPE2.schema_name
-- AND DUPE1.table_name = DUPE2.table_name
-- AND (DUPE1.key_column_list LIKE LEFT(DUPE2.key_column_list, LEN(DUPE1.key_column_list)) OR DUPE2.key_column_list LIKE LEFT(DUPE1.key_column_list, LEN(DUPE2.key_column_list)))
-- AND DUPE1.index_name <> DUPE2.index_name)
ORDER BY DUPE1.table_name, DUPE1.key_column_list
- ChatGpt: Not working scripts
- Merging with existing script: resulting in errors/not working
- trying to change the script myself: resulting in errors/not working
- google/stackoverflow = index creation is not working good, no solution found...
