An expert across Business Intelligence, Richard works upstream, helping companies articulate their business critical strategies that will yield tangible results through data mining and analysis. As a consultant, he leverages his career with IBM and Microsoft as well as his MBA from London Business School, to ensure positive outcomes for his clients. Areas of speciality include Microsoft SQL Server BI developer; relational database performance; OLAP/data mining; Microsoft Virtual TS.

Wednesday, June 7, 2017

Other people have blogged on optimizer limitations of filtered indexes (eg Rob Farley) but there is more.

Filtered indexes are a very useful tool in relational databases for two main purposes

Creating an index on a subset of rows, so the index is smaller and cheaper.

Filtered indexes can be declared unique for the filtered set. A classic example of this is Type 2 dimensions, which could have a unique index on the business key when filtered by IsCurrent=1. (There will be multiple versions of the business key that are no longer current.) Since the current record is the one most often selected, a filtered index on IsCurrent=1 is common practice.

However, these filtered indexes are not used by the SQL optimizer as often as they could be. Here are two examples.

Select from table where the predicates ensure the desired record is included in a unique filtered index and the predicates contain the full unique key. The filtered unique index won't be used. However, if the index was not filtered (Filter column is in index) the index is used. Logically, the index would be appropriate in both cases.

Select from a view that has a base table "left" joined to another table(s) using all the unique filtered index columns on the "right" side table. Normally, if none of the columns from the "right" side table are in the query (from the view) SQL will avoid accessing the "right" table. This is because there's no data required from it and it doesn't matter if there is no record (left join) and there can be no more than one record (unique constraint). Unfortunately, if the "right" table has a filtered index SQL won't ignore this table and will access it unnecessarily.

Notice the table is accessed by scanning the clustered index, when an index seek would be optimal since it "knows" there's, at max, only one record to return.Likewise, the following query will access the 3 "right" tables even though two of them are not requested and they can make no difference to the number of output rows (unique constraint ensures there is only 0 or 1 row).Select CountryCode, Applesfrom (selectc.CountryCode,c.Country,app.CountryGroup Apples,kfr.CountryGroup Kiwifruit,lem.CountryGroup Lemonsfrom tbCountries cleftouterjoin tbCountryGroups appon app.IsCurrent=1and app.CountryCode=c.CountryCodeand app.CountryGroup='Apples'leftouterjoin tbCountryGroups kfron kfr.IsCurrent=1and kfr.CountryCode=c.CountryCodeand kfr.CountryGroup='Kiwifruit'leftouterjoin tbCountryGroups lemon lem.IsCurrent=1and lem.CountryCode=c.CountryCodeand lem.CountryGroup='Lemons') vwhere Apples isnotnull

Notice the index seeks to tbCountryGroups, two of which are unnecessary.

Now, if you create a new index similar to the filtered index, but instead of filtering by IsCurrent, include IsCurrent in the index. You will find that the above queries will use the unique index to access the appropriate rows for the first query and will avoid accessing the irrelevant "right" side tables in the second query.

Notice how the plan above uses the unfiltered index, whereas before it was scanning the clustered index.

Notice how the plan above avoids joining the unnecessary "right" side tables now that we have an unfiltered index.Note, the tables and queries I have created above are only meant to demonstrate the issue. I am not suggesting that these tables and queries are the business issue. My comment above about the filtered index being common practice with Type 2 dimensions is true, which makes this issue not uncommon in data warehousing.Conclusion: Filtered indexes, are very useful in OLTP and DW environments, just not as useful as I would like.

No comments:

About Me

A Business Intelligence and database expert. Richard's early career was with IBM and Microsoft as a database specialist (20 MCP exam credits). He has combined that with an MBA from the London Business School, giving an unusual combination of business intelligence know-how and business savvy. This makes Richard a useful person to help unearth innovative business strategies and create high performing database solutions.