search
Categories
Sponsors
VirtualMetric Hyper-V Monitoring, Hyper-V Reporting
Archive
Blogroll

Badges
MCSE
Community

Cozumpark Bilisim Portali
Posted in Windows Server | No Comment | 1,744 views | 25/11/2015 15:00

This is a query of mine, which gives you detailed information about indexes on one query:

SELECT SO.NAME AS TableName, PS.object_id AS ObjectId, SI.NAME AS IndexName, PS.index_id AS IndexId, SUM(PS.RESERVED_PAGE_COUNT*8) TotalStorage, SUM(PS.USED_PAGE_COUNT*8) UsedStorage, SUM((PS.RESERVED_PAGE_COUNT - PS.USED_PAGE_COUNT)*8) FreeStorage,
SUM(CASE WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT ELSE 0 END) AS ROW_COUNT,
MAX(round(Avg_Fragmentation_In_Percent, 2)) AS 'AvgFragmentationInPercent',
MAX(fragment_count) AS FragmentCount,
MAX(ips.page_count) AS PageCount,
MAX(ius.user_scans) AS UserScans, MAX(ius.user_seeks) AS UserSeeks, MAX(ius.user_lookups) AS UserLookups
FROM
SYS.DM_DB_PARTITION_STATS PS
INNER JOIN sys.dm_db_index_physical_stats(5,NULL,NULL,NULL,'LIMITED') AS ips
ON ps.object_id = ips.object_id AND
       ps.index_id = ips.index_id
INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID
LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID
AND SI.INDEX_ID = PS.INDEX_ID
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = 5 AND ps.object_id = ius.object_id AND ps.index_id = ius.index_id
WHERE
SO.IS_MS_SHIPPED = 0
GROUP BY so.name, ps.object_id, si.name, ps.index_id
ORDER BY UserLookups DESC

You can also add Schema column if you have different db schemeas.