In SQL Server 2016 the OLTP Systems have received a significant improvement – support for the Columnstore Indexes (disk-based Nonclustered Columnstore & In-memory based Clustered Columnstore).
In both cases we have as the base the underlying OLTP-style table, with a Delta-Store object (or Tail Row Group for InMemory tables), that will hold the new data being inserted or updated by the final users. The data that is being frequently updated in OLTP-style systems is called Hot Data. The data that just being inserted into your table is definitely a Hot Data.
The important moment for the table is when the data becomes Cold or mostly infrequently read-accessed, and meaning that it can be compressed into Columnstore format.
This does seem interesting and can be very helpful in using columnstore indexes across different data patterns.
Remember that this is an application problem and is not a SQL problem. We only get into trouble when applications (or people) expect results to be sorted when they’re not. So unless you have a tiny application, or a huge amount of discipline, it’s likely that there is some part of your application that assumes sorted results when it shouldn’t.
Here’s a method I used that attempts to identify such areas, exposing those assumptions. It involves reversing indexes.
It’s an interesting idea to try out in a dev environment.
We have had an index job that has been failing for a while. This is one of those things you really don’t want to clean up because no one is complaining, but you know you should. I had heard that I could rebuild one partition at a time, but where to start? Today, I worked my way through it, so here it is so that you can do it too.
First you need to find the biggest indexes, there is a good chance those are the ones that live on partitions. I am removing Primary Keys.
This can be a real time-saver if a majority of your partitions either are read-only or at least rarely update.
Whenever you set up a script to create or drop an index, you want a safety check in there. Drop the index if it exists. Create the index if it doesn’t.
Or do something else programmatically. Checking if an index exists is a pretty frequent task. But there’s no simple function to test if an index exists in SQL Server.
Good article, and in the comments, Kendra talks about the next logical step: consolidating indexes.
A scan of the entire index is over 1600 pages. This query read three. It’s a scan, but it’s a scan which stopped after reading one page of the leaf (the other two are likely the root and intermediate pages, used to locate the first page in the leaf).
The scan read one row and then stopped, because that’s all that was needed. It did that, because there was a 1 row row-goal added to the query. For more details on row goals, see Paul White’s article on the subject.
Read the whole thing.
Corollary: sometimes a seek isn’t really a seek; sometimes it’s a scan even when the icon says “seek” because that’s more efficient.
What about adding a clustered index and dropping it? Nooooooo, and again, I learned something new. This causes two rebuilds of the non-clustered indexes as they are rebuilt with the cluster addition and then rebuilt when the table changes back to a heap (to get the heap locations). That’s crazy, and certainly not what we want.
Also read Matthew Darwin’s comment, as “Don’t do X” usually has an “Except when Y” corollary.
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.
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.
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.
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.