Created
June 12, 2019 10:05
-
-
Save giammin/7f86e4a1d146356e369ef51e492b5d24 to your computer and use it in GitHub Desktop.
index sql server script, missing and unused
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Missing Index Script | |
-- Original Author: Pinal Dave | |
-- https://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/ | |
SELECT TOP 25 | |
dm_mid.database_id AS DatabaseID, | |
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, | |
dm_migs.last_user_seek AS Last_User_Seek, | |
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], | |
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' | |
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') | |
+ CASE | |
WHEN dm_mid.equality_columns IS NOT NULL | |
AND dm_mid.inequality_columns IS NOT NULL THEN '_' | |
ELSE '' | |
END | |
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') | |
+ ']' | |
+ ' ON ' + dm_mid.statement | |
+ ' (' + ISNULL (dm_mid.equality_columns,'') | |
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns | |
IS NOT NULL THEN ',' ELSE | |
'' END | |
+ ISNULL (dm_mid.inequality_columns, '') | |
+ ')' | |
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement | |
FROM sys.dm_db_missing_index_groups dm_mig | |
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs | |
ON dm_migs.group_handle = dm_mig.index_group_handle | |
INNER JOIN sys.dm_db_missing_index_details dm_mid | |
ON dm_mig.index_handle = dm_mid.index_handle | |
WHERE dm_mid.database_ID = DB_ID() | |
ORDER BY Avg_Estimated_Impact DESC | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Unused Index Script | |
-- Original Author: Pinal Dave | |
-- https://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/ | |
SELECT TOP 25 | |
o.name AS ObjectName | |
, i.name AS IndexName | |
, i.index_id AS IndexID | |
, dm_ius.user_seeks AS UserSeek | |
, dm_ius.user_scans AS UserScans | |
, dm_ius.user_lookups AS UserLookups | |
, dm_ius.user_updates AS UserUpdates | |
, p.TableRows | |
, 'DROP INDEX ' + QUOTENAME(i.name) | |
+ ' ON ' + QUOTENAME(s.name) + '.' | |
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement' | |
FROM sys.dm_db_index_usage_stats dm_ius | |
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id | |
AND dm_ius.OBJECT_ID = i.OBJECT_ID | |
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID | |
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id | |
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID | |
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p | |
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID | |
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 | |
AND dm_ius.database_id = DB_ID() | |
AND i.type_desc = 'nonclustered' | |
AND i.is_primary_key = 0 | |
AND i.is_unique_constraint = 0 | |
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment