Since I’ve rewritten sp_helpindex a few times, I have a few blogs posts in this category. Each time I do an update I’ll make the NEW version titled USE THIS.

To use my version of sp_helpindex, you need TWO scripts. One script is version-specific and the other works on versions 2005, 2008/R2 and 2012. All versions need this generic base procedure to produce the detailed output.

Step 2: Setup the replacement procedure for sp_helpindex. This IS version specific:

On SQL Server 2008, use: sp_SQLskills_SQL2008_helpindex.sql (12 kb) to create sp_SQLskills_SQL2008_helpindex. (NOTE: This does run on SQL Server 2012 but if your table has a columnstore index, it will generate an error.)

25 Responses to (OLD): New SQL Server 2012 rewrite for sp_helpindex

Thanks, Kim! This is a great tool! I really appreciate what you’ve done with it. I use it almost every single day. It’s much easier and faster to explain to people why making a unique index, whenever possible, really matters, why the clustered index keys are don’t have to be included, etc. Thank you as always!!!

I have downloaded sp_SQLskills_SQL2008_helpindex.sql. The problem in this is that it does not return information for Spatial Indexes. Therefore we had to write our own code in this SP. It will be good for all if you make some changes so as to have information about Spatial Indexes. Thanks in advance.

Yeah, I had considered adding a bunch of additional columns but it really depends on what you’re trying to do. I usually get the usage stats from persisted copies of the usage DMVs and then I try to better analyze over a business cycle. So, I don’t see as much benefit in getting the picture of the usage stats when I’m just looking at the internals. But, I guess you’re taking it a bit further from the duplicate/redundant (or not used) side of things.

So, you can definitely add those… just not sure if I’m going to in my next rev. But, might make a good “option” to add.

I too opened and ran the scripts in Master. I can actually see both SP in the Master DB under system stored procedures, however when I try eo execute, error that it is an invalid objectname. so I try to just modify the one is system SP with right click on the object and modify. The script opens up and it evens says: invalid object name.
I followed the steps in the order suggested, even closed my connection to the SQL server and reconnected. No change

I ran both scripts in the order suggested. I can see the object in master DB system stored procs but when I try to execute it says invalid object. If I right click on the object and modify, the script opens and it says invalid object. Never seen this before. How can it show in the list of proc but be invalid?

I realize this was a while back for you but I had a similar issue (I think). What happened to me is that sp_SQLskills_ExposeColsInIndexLevels was created under my schema. The ID I ran it under is not a sysadmin and has access through an AD group (no specific user in master) so it defaulted to my schema. Try running it again but modify the code to specify CREATE PROCEDURE dbo.sp_SQLskills_ExposeColsInIndexLevels. That did it for me.

Yes. You’ll need to modify the code for XML. I just don’t generally like or recommend XML indexes (you’re often better partially shredding the data and then using the shredded column with relational indexes). So… I didn’t account for them. I’ll try to account for them (and spatial) in the next version.

Many people have modified this to include all sorts of stuff… I haven’t directly done this though as compression can be different down to the partition-level and that’s not really the main focus of this script. So, it’s unlikely that I’ll add that one myself. To me, I think partition-level index/compression/fragmentation details might be a different (and very cool) sproc! :)