SQL Server Index Key Performance Indicators

Published Mar 23 2019 11:22 AM 143 Views
Microsoft
First published on TECHNET on Jan 14, 2010

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


Index_KPI.sql

%3CLINGO-SUB%20id%3D%22lingo-sub-383580%22%20slang%3D%22en-US%22%3ESQL%20Server%20Index%20Key%20Performance%20Indicators%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383580%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20TECHNET%20on%20Jan%2014%2C%202010%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EIt's%20common%20for%20PFE's%20to%20get%20involved%20with%26nbsp%3Bperformance%20tuning%20issues%2C%20and%20for%20SQL%20Server%2C%20indexes%20are%20a%20critical%20part%20of%20maximum%20performance.%20I%20recently%20ran%20into%20a%20performance%20problem%20that%20was%20due%20entirely%20to%20the%20statistics%20being%20outdated%20on%20an%20index%2C%20so%20I%20refreshed%20my%20memory%20a%20bit%20about%20them%20and%20came%20up%20with%20a%20script%20to%20get%20some%20Key%20Performance%20Indicators%20for%20them.%20I've%20attached%20the%20script%2C%20which%20lists%20all%20the%20indexes%20for%20a%20database%2C%20but%20in%20case%20you%20want%20to%20know%20the%20basic%20idea%20without%20having%20to%20download%20and%20open%20the%20script%2C%20it's%20based%20on%20the%20following%20query%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESELECT%20i.index_id%2C%20index_name%20%3D%20i.name%2C%20index_type%20%3D%20i.type_desc%2C%20%3CBR%20%2F%3E%20p.index_depth%2C%20p.avg_fragmentation_in_percent%2C%20allocation_type%20%3D%20p.alloc_unit_type_desc%2C%20%3CBR%20%2F%3E%20statistics_date%20%3D%20STATS_DATE(i.object_id%2C%20i.index_id)%20%3CBR%20%2F%3E%20FROM%20sys.indexes%20i%20%3CBR%20%2F%3E%20INNER%20JOIN%20sys.dm_db_index_physical_stats%20(db_id('AdventureWorks')%2C%20object_id(N'AdventureWorks.Sales.SalesOrderDetail')%2Cnull%2Cnull%2C'detailed')%20p%20ON%20i.object_id%20%3D%20p.object_id%20AND%20i.index_id%20%3D%20p.index_id%20%3CBR%20%2F%3E%20WHERE%20i.index_id%20%26gt%3B%200%20%3CBR%20%2F%3E%20ORDER%20BY%20i.index_id%2C%20p.index_level%20%3CBR%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fmsdnshared.blob.core.windows.net%2Fmedia%2FTNBlogsFS%2Fprod.evol.blogs.technet.com%2Ftelligent.evolution.components.attachments%2F01%2F7622%2F00%2F00%2F03%2F30%2F57%2F42%2FIndex_KPI.sql%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Index_KPI.sql%20%3C%2FA%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383580%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20TECHNET%20on%20Jan%2014%2C%202010%20It's%20common%20for%20PFE's%20to%20get%20involved%20with%26nbsp%3Bperformance%20tuning%20issues%2C%20and%20for%20SQL%20Server%2C%20indexes%20are%20a%20critical%20part%20of%20maximum%20performance.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383580%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerSecurity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 11:22 AM
Updated by: