Dynamic Management Views and Dynamic Management Functions - Microsoft SQL Server 2012 Performance Tuning - page 151

,MID.inequality_columns
,MID.included_columns
,MIGS.avg_user_impact As ExpectedPerformanceImprovement
,(MIGS. user_seeks + MIGS. user_scans) * MIGS.avg_total_user_
cost * MIGS.avg_user_impact As PossibleImprovement
FROM sys.dm_db_missing_index_details AS MID
INNER JOIN sys.dm_db_missing_index_groups AS MIG
ON MID.index_handle = MIG.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS
ON MIG.index_group_handle = MIGS.group_handle
GO

4. To retrieve the index usage details, type and run the query as shown in the

following script:
USE AdventureWorks2012
GO

--Retrieving Index Usage Information
SELECT
O.Name AS ObjectName
,I.Name AS IndexName
,IUS.user_seeks
,IUS.user_scans
,IUS.last_user_seek
,IUS.last_user_scan
FROM sys.dm_db_index_usage_stats AS IUS
INNER JOIN sys.indexes AS I
ON IUS.object_id = I.object_id AND IUS.index_id = I.index_id
INNER JOIN sys.objects AS O
ON IUS.object_id = O.object_id
GO

5. To retrieve the information related to index fragmentation, type and run the query as

shown in the following script:
USE AdventureWorks2012
GO

--Retrieving Index Fragmentation Details.
SELECT
O.name AS ObjectName
,I.name AS IndexName
,IPS.avg_page_space_used_in_percent AS AverageSpaceUsedInPages
,IPS.avg_fragmentation_in_percent AS AverageFragmentation
,IPS.fragment_count AS FragmentCount