Tibor Karaszi

Fed up with hunting physical index details?

I am. I find myself endlessly hunting for index information when working against the various SQL Servers I come in contact with. And, sure, the information is there. You just need to go and get it. This generally means that I start with sp_helpindex. Then some SELECT from sys.indexes. Then some more against sys.partitions and sys.allocation units (we want space usage stats as well). And perhaps general usage stats (sys.dm_index_usage_stats). (I sometimes might even use the GUI (SSMS) reports and index dialog - but you might already know that I'm not much of a GUI person.)

The good news with all this is that I learn to use these catalog and dynamic management views. Bad news is that it is kind of ... boring to do the same thing again and again.

This is why wrote sp_indexinfo. You might have your own index information procedures (which you wrote yourself or found on the Internet). If not, you are welcome to use this one. I aim to improve it over time, so suggestions are welcome. Possible improvements include:

Make it a function. Functions are nice since we can order the results, aggregate, and basically do whatever we want to when we SELECT from the function. But for this I need to find out how we install a user-defined global system function - there's no supported way to do this. I'm not sure I want to go there...
Reurn missing index information as well. For this we probably want two resultsets, and only return missing index information when we targeted *one* table (no wildcards). If we do this, then function is out since a function can only return *one* result set.

If you care to give it a spin, please let me know. I just wrote the procedure, so I haven't tested it much yet. If you do find bugs, please leave a comment and I will incorporate into the source (let me know if you want to be acknowledged). Any comments are welcome.

You find the proc at: http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

Published Monday, July 07, 2008 3:17 PM by TiborKaraszi

Filed under: Performance, indexes

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

	Sebastien Hinse said: This is a useful proc. Information I would recommend adding include: filegroup placement, partition scheme, partition number (for sorting), partition range settings (left/right) and partition boundary values. July 7, 2008 9:41 AM
	James Luetkehoelter said: Very nice! The addition of partitions make it more it even more difficult to track things down (rather than rebuilding everything, which isn't an option for a large system). One thing I find people obsessing about is the number of different counters that show up with DBCC SHOWCONTIG or sys.dm_db_index_physical_stats is that people spent a significant amount of time trying to decide how fragmented something is. I always advise people to just use Scan Density (DBCC SHOWCONTIG) or Avg Fragmentation in Percent (sys.dm_db_index_physical_stats). Have you run into the same sort of thing, or would you disagree with my recommendations? July 7, 2008 9:41 AM
	jamiet said: Hiya Tibor, I'll definitely take a look - it could be very useful to me. One request. Please could you post a new blog entry if you update it, that's the only way I'll definitely know if it gets updated. (I don't 100% trust email notifications) cheers Jamie July 7, 2008 9:42 AM
	Denis Gobo said: Very nice Tibor, I already added it to my del.icio.us page July 7, 2008 12:19 PM
	TiborKaraszi said: Thanks all. Yes, Jamie, I'll post a note here whenever I update the proc. Thanks for the reminder. Sebastien, You have location already. This is either filegroup name or partition scheme name depending on whether the allocation is partitioned or not. I'll think about the whole pertition aspect some more. James, yes, I agree that it is in general better to stick with only one "thing" to focus on when you look at fragmentation. When I teach, for instance, I nowadays only mention Avg Frag in Percent and "the rest you can read about in BOL". I know that Paul Randal had issues with Scan Density in 2000 because false frag reporing if several files in filegroup - I don't think I've suffered from this myself... I'll collect input for a couple of days and then do a v1.01. :-) July 7, 2008 2:23 PM
	TiborKaraszi said: I just updated it to version 1.01. Instead of saying here whats new, I'll write a new blog (as promised to Jamie). July 8, 2008 3:58 AM
	Tibor Karaszi said: (See my initial blog post for general information abou this proc.) I just updated sp_indexinfo a bit: July 8, 2008 4:20 AM