Find most used tables & Most used Indexes & Unused Index & Missing Index Script

--get most used tables

SELECT  

    db_name(ius.database_id) AS DatabaseName, 

    t.NAME AS TableName, 

    SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed 

FROM sys.dm_db_index_usage_stats ius 

INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id 

WHERE database_id = DB_ID('ACTIVEQUOTE') 

GROUP BY database_id, t.name 

ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

--get most used indexes

SELECT  db_name(ius.database_id) AS DatabaseName, t.NAME AS TableName, i.NAME AS IndexName, i.type_desc AS IndexType, ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id WHERE database_id = DB_ID('MyDb') ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

-- Get top 25 Unused Indexes

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

-- Get top 25 missing Index

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