Thanks to Darren Myher (https://DarrenMyher.com) for a few intelligent tweaks as well as some useful formatting for this revised version.
UPDATED 12/9/24 for SQL Azure
UPDATED 4/23/2025 because people have WEIRD naming conventions
--Use
this SQL Script to identify indexes that if added could help improve
performance.
--IMPORTANT:
Do not BLINDLY implement the script recommendations. For example, if the
SAME TABLE is mentioned multiple times, then
-- it may be better to COMBINE the recommendations of multiple statements into a single one.
-- see later posting on this blog for detail
DECLARE @Edition varchar(50)
SET @Edition = CONVERT(varchar(50), SERVERPROPERTY('Edition'))
DECLARE @bEnterpriseEdition bit
IF LEFT(@Edition, 9) in ('Enterpris', 'Developer', 'SQL Azure')
SET @bEnterpriseEdition = 1
SELECT
'CREATE INDEX [IX_'
+ REPLACE(obj.Name, ' ', '') +'_'
+ replace(replace(replace (equality_columns, '[', ''),']',''),', ','_')
+ '] ON '
+ sch.name COLLATE SQL_Latin1_General_CP1_CI_AS + '.' + QuoteName(obj.name)
+ ' ('
+ equality_columns
+ CASE WHEN inequality_columns IS NULL
THEN ''
ELSE ',' + inequality_columns
END
+ ')'
+ CASE WHEN included_columns IS NOT NULL
THEN ' INCLUDE (' + ISNULL(included_columns,'') + ') '
ELSE ''
END
+ CASE WHEN @bEnterpriseEdition = 1
THEN ' WITH (online = ON)'
ELSE ''
END
+ ' -- ' + CONVERT (varchar, avg_user_impact) + '% anticipated impact'
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig
ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
JOIN sys.objects obj
ON obj.object_id = mid.object_id
JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
WHERE
database_id = db_id()
AND avg_user_impact > 60
AND equality_columns IS NOT NULL
ORDER BY
obj.name
, equality_columns
, inequality_columns
, datalength(included_columns)
No comments:
Post a Comment