IKR! Feels like I haven’t been posting since, forever, not really…more like a couple months.
Had some life changing events, still in the midst of those events. On 11-30-2015 my daughter Evelyn Margarita Hidalgo was born. This means that for most of November and December I was completely engulfed in all that goes along with a new baby. I coached my wife during the birth for 30 hours (really she did all the work, and I’m completely amazed by how she did it with no pain medication), and I’ve been helping out with taking care of the baby. Then, the Christmas holiday showed up and I was once again out of commission. I know that many of you thought I was off on some sort of awesome mission, probably out fighting ninjas robots and piloting megazords, however I was just at home having some quality family time.
So, that is where I’ve been.
Moving right along, I’d like to present a script I’ve been working on that is based on Kendra Little’s webcast for how she uses the missing index report in SQL Server. It’s basically the same script with an added lazy man component. I added in some string generation so that the script will generate the create statements utilizing the order of the equality, inequality and included columns. Why is this cool? I feel that the method that Kendra presents in her webcast was a simple and effective way to look at the missing indexes and identify possible candidates for creation. It’s something I’ve become very comfortable using and ends up being a script that I use to teach junior DBAs missing indexes. The format of this query is great for quick analysis in a spreadsheet and excluding the tsql column, it can be easily presented to others. It’s also post fodder so that I can try to get back in the swing of posting after missing two months.
Once identification has been complete, you can just cut and paste the create script and GO! Some items to note
1. Please for the love of all that is holy, DO NOT CREATE ALL THE INDEXES. That will most likely suck, and aside from most likely impacting your server in a negative way, it will also impact your server in a negative way. You have to read the report, and understand why you are creating the index.
2. If there are a huge amount of include columns…then the script generates an index name that has a huge amount of include columns (and eventually runs out of name). I’m currently trying to come up with an intelligent way to alter this so that only “enough” columns appear.
3. You’ll note the prefix of MIX…you can probably guess that I use an index naming convention that prefixes MIX to any index that was created using the missing index report. You can change this to whatever you want, but I use this convention so that I know if an index was created for a specific query or ticket vs. an index that was created based on this report (TIX, QIX, MIX).
If you’d like to view the script you can access it via Github