USE THESE: Updates to SQLskills Index Procedures

OK, I’ve been meaning to update these for quite some time… Randolph West tweaked a few things a few months ago (they’re so awesome!) and I’d been wanting to consolidate all of the madness of having to have multiple scripts for “normalizing” the INCLUDE list as the order there doesn’t matter (but, it is “preserved” in the internal tables so for me to determine REAL duplicates I need to order this for comparison).

Anyway, I’ve finally done all of this. And, I removed the OLD “2008” portion of the name as they work on all releases from 2008 through 2019. I’ve tested most of this but I suppose I could have missed something. So, definitely let me know if you find anything!

In the end, there are now ONLY THREE index related procedures (create them in this order):

  1. sp_SQLskills_exposecolsinindexlevels
  2. sp_SQLskills_helpindex (this one uses sp_SQLskills_exposecolsinindexlevels)
  3. sp_SQLskills finddupes (this one uses sp_helpindex)

And, because I know a few of you have the previous scripts installed, these updated scripts will drop the old versions – if they exist. These procedures will be removed:

  • sp_SQLskills_ExposeColsInIndexLevels_INCLUDE_UNORDERED
  • sp_SQLskills_SQL2008_finddupes_helpindex
  • sp_SQLskills_SQL2008_finddupes
IMPORTANT: If you attended my PASS Session “Indexing for Performance,” I’ve included these updated scripts in the SQLskillsIndexProcs project and I have sent them in for PASS to post on their site AND I’ve upload them to our demos page here

How do you use these index scripts?

sp_SQLskills_helpindex

sp_SQLskills_helpindex is for adding / exposing more details related to you indexes. All you need to do is pass in a table name.

EXEC [sp_sqlskills_helpindex] '[dbo].[member]';
go

Or, for those of that are lazy (myself included)…

EXEC sp_sqlskills_helpindex member
go

sp_SQLskills_finddupes

sp_SQLskills_finddupes is for determining your duplicate indexes on a table, or within the entire database.

EXEC [sp_sqlskills_finddupes] '[dbo].[member]';
go

Or, for those of that are lazy (myself included)…

EXEC sp_sqlskills_finddupes member
go

And, the BEST one is this as it looks at the entire database:

EXEC sp_sqlskills_finddupes
go

SO, here’s the zip 20211111_SQLskillsIndexProcs that has all 3 SQL scripts.

Have fun!
-k

5 thoughts on “USE THESE: Updates to SQLskills Index Procedures

Leave a Reply

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

Other articles

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.