USE THESE: Updates to SQLskills Index Procedures
source link: https://www.sqlskills.com/blogs/kimberly/use-these-updates-to-sqlskills-index-procedures/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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):
- sp_SQLskills_exposecolsinindexlevels
- sp_SQLskills_helpindex (this one uses sp_SQLskills_exposecolsinindexlevels)
- 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
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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK