Performance tuning of SQL query by correcting indexes

-- Missing Index Script-- Original Author: Pinal Dave (C) 2011SELECT TOP 25
dm_mid.database_id dm_mid.database_id 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,''),', ','_'),'[',''),']','') +CASEWHEN 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_StatementFROM sys.dm_db_missing_index_groups dm_migINNER JOIN sys.dm_db_missing_index_group_stats dm_migsON dm_migs.group_handle = dm_mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details dm_midON dm_mig.index_handle = dm_mid.index_handleWHERE dm_mid.database_ID = DB_ID()ORDER BY Avg_Estimated_Impact DESC

GO




-- Unused Index Script
-- Original Author: Pinal Dave (C) 2011SELECT 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_iusINNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id ANDdm_ius.OBJECT_ID = i.OBJECT_IDINNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_IDINNER JOIN sys.schemas s ON o.schema_id = s.schema_idINNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_IDFROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) pON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_IDWHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0AND i.is_unique_constraint = 0ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups)ASCGO


-- Find Unused indexes
USE AdventureWorks
GODECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),INDEXNAME = I.NAME,I.INDEX_IDFROM SYS.INDEXES IJOIN SYS.OBJECTS OON I.OBJECT_ID = O.OBJECT_IDWHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1AND I.INDEX_ID NOT IN (SELECT S.INDEX_IDFROM SYS.DM_DB_INDEX_USAGE_STATS SWHERE S.OBJECT_ID = I.OBJECT_IDAND I.INDEX_ID = S.INDEX_IDAND DATABASE_ID = @dbid)ORDER BY OBJECTNAME,I.INDEX_ID,INDEXNAME ASCGO