Fixing Issues Related To Filtered Indexes

Kevin Chant looks at a few problems that can pop up when using filtered indexes:

In a past post here I did an overview of different index types. I said in that post that I think filtered indexes could be more popular. In this post I will cover fixing some of the problems caused when you first introduce rowstore filtered indexes to a SQL Server database.

Some of you have probably been there already. You’ve put in your first filtered index on a database only to find an issue has happened. I’ve witnessed these issues at a few places. This will hopefully reduce the pain.

I’ve definitely experienced the third issue (which also pops up when using parameterized queries, so the optimizer doesn’t know that it can use the filtered index), but never the first two.

Related Posts

Finding Missing Index Hints in Query Store

Grant Fritchey shows us another place where we can find missing index hints: A couple of notes on the query. I cast the query_plan as xml so that I can use the XQuery to pull out the information. It is possible that the plan might be so large that you get an error because of […]

Read More

Benefits of Inline Indexes

Phil Factor covers a topic fairly close to my heart: Usually, the added features of the CREATE TABLE syntax in new releases of SQL Server are esoteric, and unless you are dealing with memory-optimized tables or other esoteric stuff, they aren’t of great interest. However, the Inline INDEX for both a table and column index […]

Read More

Categories

November 2018
MTWTFSS
« Oct Dec »
 1234
567891011
12131415161718
19202122232425
2627282930