SQL Server Index Key Performance Indicators
It's common for PFE's to get involved with performance tuning issues, and for SQL Server, indexes are a critical part of maximum performance. I recently ran into a performance problem that was due entirely to the statistics being outdated on an index, so I refreshed my memory a bit about them and came up with a script to get some Key Performance Indicators for them. I've attached the script, which lists all the indexes for a database, but in case you want to know the basic idea without having to download and open the script, it's based on the following query:
SELECT i.index_id, index_name = i.name, index_type = i.type_desc,
p.index_depth, p.avg_fragmentation_in_percent, allocation_type = p.alloc_unit_type_desc,
statistics_date = STATS_DATE(i.object_id, i.index_id)
FROM sys.indexes i
INNER JOIN sys.dm_db_index_physical_stats (db_id('AdventureWorks'), object_id(N'AdventureWorks.Sales.SalesOrderDetail'),null,null,'detailed') p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE i.index_id > 0
ORDER BY i.index_id, p.index_level