1

I find I am spending a lot of time lately examining lists of proposed indexes that were generated by an index tuning adviser or some other automated process.

Back in the day (circa 2005) I found the suggestions to be rather awful in that they seemed geared to one specific query and more often than not were merely a covering index that had every column in the table whether or not those colums were already indexes.

As time has progressed, I feel the index tuning advisers have improved dramatically, but I still have a built-in mistrust and still want to spend the time to review each one.

I am trying not to be pessimistic, but it is difficult when a vendor (Microsoft) suggests to add multiple overlapping covering indexes to improve performance, especially when we are paying for storage space. I also don't want to spend my own time needlessly. I would rather be fixing poorly written queries.

7

Remember it is just an advisor...just as in life they are not always right on the path that you should take. It's free advise to take or leave.

For the most part it is basing it off the DMV data in SQL Server so it can even fluctuate on what it sees as being an issue. I generally will them as a guide.

Example:

I see a common set of tables showing "suggested" indexes. Well that would tell me then maybe the current indexes, for those tables, need to be reviewed against the current load that is running. Index architecut can change over the life of an application, the indexes created when during release version 1.0 may not be the same once you reach version 5.0.

I would then maybe go use sp_BlitzIndex or Jason Strate's Index Analysis to look at the table and indexes. Maybe go through and capture the execution plans for thost tables, or pull them from cache.

Remember though, it is worth testing any index you add to a table if you can. If not and you are going through modifying indexes, then make sure you make a script of the current indexes so you can restore them.

4

As a consultant that looks at a lot of servers not managed by DBAs, I see DTA indexes in the "Index Hoarder: Unused NC index" section of sp_BlitzIndex a lot. They are also often in the "Multiple Index Personalities: Duplicate keys" or the "Multiple Index Personalities: Borderline duplicate keys" sections. Sometimes these indexes are being used, but they have very few Reads and lots of Writes.

Rather than use the DTA, I would recommend using sp_BlitzIndex, or an alternative missing indexes script, to find the high-value missing indexes for your database. If using sp_BlitzIndex, consider the indexes with an "estimated benefit per day" of 5 million or greater. I'll sometimes even recommend the ones with 1 million or greater if the "uses" are high.

1

Likely no to DTA but almost definitely yes to the missing index details stored in execution plans.

I get to see inefficient garbage queries dragging down servers all day long. I often pass on lists of missing indexes along with execution plans only to hear vendors hem and haw. "We can't just apply anything!"

And so they don't apply anything no matter how strong the evidence. Test and profile away and prove it - they don't care.

Indexes don't need to be hand crafted and it's often not enough that you have half an index here and half an index there - if that then causes nested loops or key lookups repeatedly on large tables then that can cause issues.

There are good reasons the missing index warnings get triggered. Obviously it's not worth creating ALL of them on EVERYTHING and some might not have a huge beneficial impact (instead having a beneficial but insignificant impact).

But for the most part, the top ones as analysed by CPU, IO, and count, turn out to be beneficial. You're more likely to have a crap execution plan with crap indexes than magically stumble across a better working execution plan with crap indexes.

It's not data science.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.