Your Indexes have Feature Phobia

Well, lets be blunt here. Indexes are rarely scared of new features. It’s really people who are scared of new features– or maybe they just don’t know about them. But sp_BlitzIndex® doesn’t like to point fingers at people, so let’s just put the blame on your indexes.

No indexes use includes

“Included columns” were introduced as a feature in SQL Server 2005. An included column exists in the “leaf” of an index, but isn’t present in the key columns. Using included columns can help you “cover” queries with an index– without inflating the key of that index.

Like any good thing, you don’t want to overuse included columns. That just leads you to tons of wide indexes, which is another disorder in itself.

But index tuning and management is all about balance. And if you’re not using included columns at all by now (hey, it’s been HOW MANY years since 2005?), then we’re guessing you don’t even know what you’re missing.

Includes are used in less than 3% of indexes

This diagnosis is a little fuzzier than the one before. There’s no hard and fast rule that I should use includes on 3% or more of my indexes — not at all!

However, I might have a database that has 700 indexes, and one has includes. It wouldn’t be diagnosed as “no indexes use includes,” but are the databases’ indexes quite possibly out of balance? When this comes up, take a look at your indexing practices and make sure you’re taking advantage of all the options.

Borderline: No filtered indexes or indexed views exist

This check is bound to be controversial, but sp_BlitzIndex® isn’t afraid of controversy.

First, let’s make one thing clear: you absolutely do not need to use filtered indexes or indexed views in every database. In fact, if you made it a practice to always use these features in EVERY database, that would be a little ridiculous.

However, don’t you want to know if you’re NEVER using these features? If you have a fair amount of SQL Server databases, there’s very likely a scenario or two when one of these will come in handy, and you’re not taking advantage of it.

Gotchas

Think creating a filtered index or indexed view is super-low risk? Think again!

if you research these features and find a great use case, be careful when you implement them against a database for the first time. When you create either a filtered index or an indexed view, writes to the table may fail if the application is using different SET options than were in place when the filtered index or indexed view was created. See SET Options That Affect Results for more information. For this reason we recommend always creating indexes in a development environment and testing applications first when possible– especially with filtered indexes or indexed views.

Filtered index aren’t defined properly

When you create a filtered index, it’s important that the columns in the filter definition make it into the index definition. If you don’t, the optimizer may skip using them for queries where they could be really useful.