(OLD): New SQL Server 2012 rewrite for sp_helpindex

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 1: Setup sp_SQLskills_ExposeColsInIndexLevels

Create this procedure first: sp_SQLskills_ExposeColsInIndexLevels.sql (7 kb).

This is what gives us the tree/leaf definitions. And, this works for all versions: 2005, 2008/R2, and 2012.

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

On SQL Server 2005, use: sp_SQLskills_SQL2005_helpindex.sql (11 kb) to create sp_SQLskills_SQL2005_helpindex.

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.)

On SQL Server 2012, use: sp_SQLskills_SQL2012_helpindex.sql (12 kb) to create sp_SQLskills_SQL2012_helpindex.

Step 3: Setup a hot-key combination

Optionally, setup this procedure to be invoked through a keyboard shortcut using Tools, Options, Environment/Keyboard. I usually make it Ctrl+F1 and I described how to do this here.

The Output

On SQL Server 2012, the output will look like the following (index_id 5 is a columnstore index):

index_id is_disabled index_name index_description index_keys included_columns filter_definition columns_in_tree columns_in_leaf
1 0 [member_ident] clustered, unique,   primary key located on PRIMARY [member_no] NULL NULL [member_no] All columns   “included” – the leaf level IS the data row.
2 0 [member_corporation_link] nonclustered located   on PRIMARY [corp_no] NULL NULL [corp_no],   [member_no] [corp_no],   [member_no]
3 0 [member_region_link] nonclustered located   on PRIMARY [region_no] NULL NULL [region_no],   [member_no] [region_no],   [member_no]
4 0 [LastNameInd] nonclustered located   on PRIMARY [lastname] NULL NULL [lastname],   [member_no] [lastname],   [member_no]
5 0 [columnstore_index] nonclustered   columnstore located on PRIMARY n/a, see   columns_in_leaf for details n/a, columnstore   index n/a, columnstore   index n/a, columnstore   index Columns with   column-based index: [member_no], [lastname], [firstname]

I hope this helps you when looking at your indexes!

Enjoy,
kt

25 thoughts on “(OLD): New SQL Server 2012 rewrite for sp_helpindex”

  1. 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!!!

    Reply

  2. 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.

    Reply

    1. People actually use those? ;-) ;-) ;-)

      Good point. I can do that… (I’ll try to get another version out there shortly. there are a few little tweaks that I’d like to do as well…)

      Thanks!!
      kt

      Reply

  3. I use this almost every day and really don’t know what I’d do without it. Really!

    I updated the script a while ago to see columnstore indexes, but haven’t yet decided if I wanted statistics in there yet.

    The columns I’ve been thinking of adding are last_updated, rows, rows_sampled, and modification_counter from sys.dm_db_stats_properties. (not really sure what to do with steps and unfiltered_rows yet)

    Thoughts?

    John
    p.s. hope your cheek gets better in a couple months!

    Reply

    1. 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.

      Thanks!!
      kt

      Reply

  4. I followed the instructions but I am not able to locate the stored procedure in the master database and in addition I dont know which parameters it takes.

    Reply

    1. When you opened and ran these scripts in master, did you get any errors? And, how are you looking in master? Did you execute SELECT * FROM sys.objects WHERE name like ‘sp[_]%’?

      As for parameters, it works EXACTLY the same way as sp_helpindex!

      Hope that helps!
      kt

      Reply

      1. 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

        Reply

        1. Hey there Brenda – The invalid object error would be the same error that you’d receive running just sp_helpindex. You *must* supply a valid object name:

          sp_helpindex ‘member’

          Hope that helps!
          Kimberly

          Reply

      2. 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?

        Reply

        1. Hey there Brenda – The invalid object error would be the same error that you’d receive running just sp_helpindex. You *must* supply a valid object name:

          sp_helpindex ‘member’

          Hope that helps!
          Kimberly

          Reply

          1. Brenda,

            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.

            Thanks
            Kenneth

    1. 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.

      Thanks!
      kt

      Reply

    1. 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! :)

      hth,
      k

      Reply

  9. Hi Kim,

    I have been using this sproc extensively – have found it very handy. Thank you!

    Any plans on creating a SQL-2014/2016 version?

    When trying it on a table with a clustered columnstore index I get this “noncommittal” :-) description for the index:

    “new index type located on PRIMARY”

    Thanks again, I enjoy following your blogs and courses on Pluralsight,

    Marios Philippopoulos

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course