Pipboy level utility!
Indexing…a DBAs boon and bane all rolled into one. If you can truly be one with the IX then you will be on the path of SQL TAO. If the IX rejects you and you cannot find a way to tame it…then you will surely be on the path of unemployment. These are the hard facts about indexing.
On several occasions I have found myself needing to cleanup a messy index situation, more often than not, unused indexes are sitting around gathering dust (not being read) and getting in the way (accumulating writes). Through my travels as a student of SQL TAO here are some key points to remember:
1. Do not use any scripts that do not take into consideration or show reads vs. writes
2. How long has your server been up? A restart of the instance will clear out your stats, so perhaps that index isn’t really 0 reads and n writes.
3. Disable the index first, drop the index later.
4. Have you looked into the plan cache to see if there are any plans cached that use that index? If so, have you read through the SQL to see what might happen?
First off you need to get Brent Ozar’s sp_BlitzIndex. You will find this tool to be extremely valuable. I run it at the start of each week and save the results into a spreadsheet for analysis. I would become familiar with the @mode=0 and @mode=2.
Mode 2 is for collecting all the index info. This mode offers all the details you could want from all the indexes across your database. The results can be overwhelming, I would suggest you get into the habit of dumping these queries into a spreadsheet. Another great suggestion is to dump the results into a DBA table (you know, the one you collect your own metrics and stats in), and append a timestamp column (or something so that you can identify when the stats where collected), and start playing with SSRS.
Mode 0 is your standard default run of sp_BlitzIndex. If you are in the same boat that some of us have been in, the one where you inherited databases that were clearly using the index shotgun, this mode will return a wealth of information. I normally copy all of this into a spreadsheet weekly, then I start dissecting. I start with multiple index personalities as part of my analysis. This is a great place to quickly identify indexes that can be combined together. Looking at the index definition column and the secret_columns, you can quickly see if two similar indexes can be combined resulting in a single better index, less writes on your system, and less space usage.
Another tool I use is a modified version of Pinal Dave’s unused index script. I’ve changed a few things in the script to taste. First I changed the columns from KB To MB. I’m not sure about you, but I normally work in environments where things are sized in GB so it’s just quicker for my brain to process thousands of MB vs KB. Next, I’ve added a column that contains the TSQL to disable the index (I left the drop TSQL column, just in case you need it). Finally, I added a couple comments at the bottom and you can play with those options to view the results differently. I normally run the script a few various ways, record the results and compare them later. You can view/download the script here
When you are undergoing index tuning using the standard, DisableDrop/Analyze/Create/Analyze cycle, I would highly recommend that you disable indexes first, keep them disabled for some amount of time before dropping. I always keep logs of what indexes I disabled on what day (IX_Disabled_DBName_DATE.sql) and I always keep logs of what indexes were dropped, and their create statements. Just some simple DBA CYA.
These are just a couple of tools and tips that you can use to help you on the path of SQL TAO. I hope you found this article informative and helpful.
Cheers.