Columnstore Index Compression Delay

Niko Neugebauer found a “compression delay” option on columnstore indexes in SQL Server 2016 CTP 3.2:

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.

Reversing Sort Order

Michael Swart shows how reversing index sort order can expose invalid assumptions in code:

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.

Rebuilding Indexes By Partition

Andrea Allred shows how to rebuild indexes one partition at a time:

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.

Finding Indexes

Kendra Little talks about index discovery:

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.

Index Scans

Gail Shaw proves that sometimes, an index scan isn’t a full index scan:

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.

Don’t Rebuild Heaps

Steve Jones notes the issues around rebuilding tables lacking clustered indexes:

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.

Filtered Indexes Are Tricky

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.

  1. Remove the parameters and use literals. (not practical)

  2. Use Dynamic SQL

  3. 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.

Fill Factor And Fragmentation

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.

Include Filtered Index Columns

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.

Find Duplicate Indexes In SSDT

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.

Categories

August 2017
MTWTFSS
« Jul  
 123456
78910111213
14151617181920
21222324252627
28293031