Kevin Eckart investigates filtered indexes not being used:
This warning is telling me that Parameterization is to blame for the filtered index not being used. From here, I see 3 options.
Remove the parameters and use literals. (not practical)
Use Dynamic SQL
Use OPTION(RECOMPILE) at the bottom of the query.
This is the classic issue with filtered indexes: you expect them to be used, but when you check the plan, they aren’t.
Erik Darling wants to know if fill factor affects index fragmentation:
It’s not just queries that reading extra pages can slow down. DBCC CHECKDB, backups, and index and statistics maintenance all have to deal with all those pages. Lowering fill factor without good reason puts you in the same boat as index fragmentation does, except regular maintenance won’t “fix” the problem.
Like everything else, the appropriate fill factor depends upon your context.
Erik Darling shows how to make filtered indexes a bit more robust:
But why oh why didn’t SQL use my filtered indexes for even smaller subsets of the filter condition? It seemed insane to me that SQL would know the filter for the index is on (x > y), but wouldn’t use them even if (z > x).
The solution was to put the filtered column in the include list. This lets SQL generate statistics on the column, and much like getting rid of the predicate key lookup, allows you to search within the filtered index subset for even more specific information.
Filtered indexes are as useful as they are mercurial.
Ed Elliott has another nice tool in his SSDT Dev Pack:
This new tool for the SSDT Dev Pack adds a menu item (Tools–>SSDT Dev Pack –> Find Duplicate Indexes) what it does is scan all the projects in the solution (it doesn’t follow “this database” references to examine them, maybe a future version) and then print to the output window a list of all the duplicate indexes based on the table, the columns and included columns – I don’t check anything else so you might actually want a duplicate in some circumstances but these should be very few and far between.
If you double click on the index it will take you to the location in the code where it actually is so you can delete it 🙂
A very useful tool gets even more useful.
Niko Neugebauer, in part 73 of his ongoing columnstore index series:
This result was observed right after the finish of the loading script, where we can clearly see 4 Delta-Stores for 10 Million Rows. 3 of the Delta-Stores are Closed and 1 Delta-Store is Open, which is an absolutely impossible combination if we think about Clustered Columnstore Indexes, where one would expect to have 10 Compressed Row Groups or 10 Delta-Stores (9 Closed & 1 Open).
If you take a more detailed look at the associated sizes of the closed Delta-Stores, you will see that they increase each time a new Delta-Store is being used. For example, the first one is capped at 1.048.567 Rows, the second one is capped at 2.097.152 and the last closed Delta-Store is set to 4.193.904 Rows – meaning that the size is being constantly doubled.
I’d like to see this as the first step toward expanded sizes for compressed rowgroups.
If you’re using SQL Server 2014, you get the benefit of writing inline non-clustered indexes. Denny Cherry has more:
As for the syntax it’s pretty straight forward. Below is a sample table with a couple of indexes, one created on the column c2 and one created on C1 and C2. Now sadly include columns aren’t supported with inline indexes, but hopefully that’ll show up in a future version of SQL Server.
This was added for In-Memory OLTP support, and I like it. For more on Denny’s comment about tempdb performance, check out a slide deck Eddie Wuerch used to teach people (including me) about temp table reuse.
Gail Shaw on how clustered indexes do not guarantee physical order:
Do indexes (clustered or non-clustered) define the physical storage order of the rows?
No, absolutely not.
What indexes do is provide a logical ordering, a collection of pointers, that allow the storage engine to retrieve data from an index ordered by the index key, but that’s logical ordering, it specified nothing regarding the physical ordering.
Read the whole thing.
Kevin Feasel
2016-01-12
Indexing