T-SQL Tuesday: The Best Index Analysis Solution

For this month’s T-SQL Tuesday, Jens Vestergaard (b|t) challenges us to wax poetic about our essential SQL Server tools. I’m here to say, unequivocally, that Plan Explorer from SentryOne changed the game for me.

If you haven’t used Plan Explorer before, there’s no time like the present. It’s FREE (download here)! And no matter what your title at work is, if you’re in charge of slinging code, and want it to execute efficiently, Plan Explorer helps you pinpoint areas of improvement and even quantifies the benefits of your changes.

My favorite part of Plan Explorer, and the part I’d like to discuss today, is the Index Analysis tab. Aaron Bertrand wrote a Index Analysis  on how to use the features of Index Analysis. Rather than rehash that information here, I wanted to explain how Index Analysis made me a better developer through an example, and how it inspired my first SQL Saturday talk.

I’ve always been intrigued by query tuning and writing efficient T-SQL code, but it took being put in a charge of a database with no indexes before I got intimate with indexing strategy. I’m still learning, but part of my journey through that experience was identifying frequently-executed, high-cost queries and tuning them, which sometimes entailed creating a covering index.

What I love about using Plan Explorer in that scenario is that the Index Analysis tab guides you toward creating the best index to cover a query. It uses a color-coded grid, backed by tried and true indexing methods. Index Analysis lays out the legend for the color scheme. Here’s an example of a query against a table that has a clustered index scan, a sort, and no nonclustered indexes on the table. We see a Total Score of 0% highlighted in red, which tells us that the clustered index does nothing for this plan. We can do better here.

After adding a new index and naming it, we have several red cells in the grid. These represent columns that must be a key or included column in the index to cover the query.

By clicking the drop-down in the cell, we can see options to test the impact of adding a column to the index.

In this example, I’ve chosen JobTitle at random. In this case, the cell went from red to yellow. The yellow color lets me know that it’s possible that there is a better choice out there for this index design. Let’s explore some other options while considering the Output, Sort Type, Sort Order, and Predicate data that Plan Explorer surfaces.

When I notice that SalariedFlag is included in a predicate and apply that as my first key column, I get a dark green color. That means that this is a great choice for this index. As a general rule, if there’s a predicate applied on a column, it’s favorable to have it as the first key column in a covering index. This is where Plan Explorer takes us from aimlessly using a query tuning solution to noticing patterns that we can apply in the future.

As we go along, we’ll see that the Total Score goes up or down as we make choices for the index. I was able to achieve a score of 75% by including the OrganizationalLevel as the second key column here, since it is ordered. I also chose to add JobTitle and LoginID, but made the choice to include them rather than put them in the key, as there is no sort or predicate on those columns, but they are required for the final output.

If we’re satisfied at this point, we can click the “S” button at the bottom of the index to script this out for further testing on a test server.

When I re-run the query after applying the index, I see in the Plan Diagram that I now have an index seek instead of a scan, and that the high-cost sort is gone. Not only that, the Index Analysis tab shows our new index in bold, which means that the optimizer is using it. We also see that the statistics were updated at the time I created this new index. Finally, we see a dark green 90% as our Total Score.

By taking note of how Index Analysis behaves, I learned over time how to create better covering indexes for my highest cost queries. In addition, I was inspired by Plan Explorer’s Index Analysis to develop my first SQLSaturday session, “Handcrafting Covering Indexes.” That session debuts at SQLSaturday Wheeling on April 28, 2018.

Happy Performance Tuning!