The Case of the Weirdly Long COLUMNSTORE_BUILD_THROTTLE Wait

A funny thing happened on the way to my filtered nonclustered columnstore demo recently…

It was taking forever to create my demo index.

My demo table had a unique clustered index created on two columns: FakeBirthDateStamp (DATETIME2(0)), FirstNameByBirthDateId (BIGINT).

I used the following code to create my filtered nonclustered columnstore index (nccx):

ON dbo.FirstNameByBirthDate ([FirstNameByBirthDateId], [FakeBirthDateStamp], [StateCode], [FirstNameId], [Gender], [Flag1], [Flag2])
WHERE (FakeBirthDateStamp < '2015-01-11');

But creating the index was super slow. I’d limited my dataset size so that populating the table and creating the clustered rowstore index on the table could all be done in less than 2.5 minutes, but this nonclustered columnstore index was taking way longer. I’m running it again as I write this post, and it’s up to 12 minutes right now — still going!

Some clues… memory grants and wait stats, oh my

Looking in sys.dm_exec_query_memory_grants, I can see that my filtered mccx index create requested ~450MB of memory grant, and got it! Yay! But it’s only using under 2MB of memory grant, and its max used grant is 55MB. It’s using all four of the cores on my VM, but CPU use total is at a whopping 2%.

Running Adam Machanic’s free sp_WhoIsActive procedure, I can see that I am constantly waiting on columnstore_build_throttle:

Click for a larger image

What’s up with columnstore_build_throttle?

Denzil Ribeiro has a great post on how columnstore indexes are built. He writes that while segments can now be built in parallel, the first segment is built with a single thread to see how much of a memory grant is really needed before things get real.

While this first segment is being built, other threads wait on it, and the other threads all give off COLUMNSTORE_BUILD_THROTTLE.

Am I not getting past the first segment?

I’m a slow writer, so at this point, my test filtered nccx create is up to 22 minutes.

Reminder: populating the whole database and creating the rowstore clustered index took less than 2.5 minutes.

At this point in my original test, I had a small realization and a question: could I have screwed up the filter?

A test: remove the filter

Guess how long it takes to create the same nccx, but with no filter definition, like this:

ON dbo.FirstNameByBirthDate ([FirstNameByBirthDateId], [FakeBirthDateStamp], [StateCode], [FirstNameId], [Gender], [Flag1], [Flag2]);

30 seconds or so.

Not… we’re up to 24 minutes on my repro of the “bad filtered nccx” create.

30 seconds.

Aha! I messed up my filter!

At this point in my testing, I realized this was a case of user error.

Can you guess what I did wrong?

Don’t worry if you don’t, it took me longer to figure this out than I’d like to admit (and I work with this dataset a lot).

I used the wrong datatype

To review, my original index definition was…

ON dbo.FirstNameByBirthDate ([FirstNameByBirthDateId], [FakeBirthDateStamp], [StateCode], [FirstNameId], [Gender], [Flag1], [Flag2])
WHERE (FakeBirthDateStamp < '2015-01-11');

But the FakeBirthDateStamp column is DATETIME2(0).

‘2015-01-11’ will implicitly convert to a DATETIME2(7). Comparing a larger data type to a smaller one often equals BIG TROUBLE in SQL Server.

I knew this, I just didn’t think about it.

That first index create might complete someday. I’m not sure, the longest I’ve let it run is 34 minutes before cancelling it.

But if I correct my index creation definition to this…

ON dbo.FirstNameByBirthDate ([FirstNameByBirthDateId], [FakeBirthDateStamp], [StateCode], [FirstNameId], [Gender], [Flag1], [Flag2])
WHERE (FakeBirthDateStamp < CAST('2015-01-11' AS DATETIME2(0)));

It creates in ~41 seconds.

So, yes, data types really matter ūüôā

Extra credit: what about rowstore filtered indexes?

I just know one of you clever folks is going to ask this eventually, so I went ahead and tested.

For a rowstore filtered index with a filter on the DATETIME2(0) column, if I create the index with an explicitly correct datatype, it creates in 15-16 seconds.

If I create the same rowstore filtered index with a “sloppy” filter that SQL Server implicitly converts, the index creates in 27-30 seconds.

Both indexes work as expected for sample test queries.

So I saw a performance difference for both rowstore and columnstore index creation, but it’s much more dramatic with filtered columnstore indexes.

Extra extra credit: what if I have a different clustered index on the table?

To recap: in this case where I saw the super-slow filtered nonclustered columnstore index create, I had what I’ll call “Pattern 1”:

  • A unique clustered index with compound key = FakeBirthDateStamp DATETIME2(0), FirstNameByBirthDateId BIGINT
  • Nonclustered columnstore index with a predicate who has an implicit conversion on FakeBirthdateStamp to DATETIME2(7)

Pattern 1 is recommend in Sunil Agarwal’s post here, so that SQL Server can combine scans on the filtered NCCX with seeks on the clustered index.

I wondered if I’d have the same problem if instead I had what I’ll call “Pattern 2”:

  • A clustered PK on FirstNameByBirthDateId BIGINT
  • Nonclustered columnstore index with a predicate who has an implicit conversion on FakeBirthdateStamp to DATETIME2(7)
  • A filtered rowstore nonclustered index to help find the rest of the rows

Pattern 2 is demonstrated in Niko Neugebauer’s post here.

And, in fact, with Pattern 2 I do NOT run into the super-slow index create with the implicit conversion on the filter of the NCCX. It takes ~40 seconds.

Aren’t edge conditions fun?

In summary…

We should all (especially me) get in the habit of explicitly casting literals to the proper data type in any kind of filtered index.

That’s why I don’t think this is a bug. I messed up in my index definition — being unclear about a datatype is my bad, not the SQL Server’s.

The Case of the Rowgroup Deadlock in a Columnstore Index

I came across a fun deadlock when writing demos for my session on the Read Committed isolation level this week. (It’s OK to call it “fun” when it’s not production code, right?)

I was playing around with a nonclustered columnstore index on a disk-based table. Here’s what I was doing:

Session 1: this session repeatedly changed the value for a single row, back and forth. This puts it into the delta store.

Session 2: this session repeatedly counted the number of rows in the table, using the columnstore index.

With my sample data in this scenario, I found I frequently generated deadlocks.

Let’s look at the deadlock graph

I started up an Extended Events trace for the xml_deadlock_report event, and here’s what this deadlock looks like (with some annotations)…

Click for a larger view

Breaking it down

The circle on the left is Session 2 – it was running a SELECT COUNT that used the nonclustered columnstore index. It was chosen as the deadlock victim.

Reading the arrows, the SELECT COUNT query:

  • Had a lock on the compressed rowgroup in the columnstore index
  • Wanted a shared lock on the b-tree delta store for the columnstore index to count the rows for data that had been changed

The circle on the right is Session 1 – it was running an update that changed the value for one column in one row. Reading the arrows, the UPDATE query:

  • Had an intent exclusive lock on the b-tree delta store
  • Wanted an intent exclusive lock on the compressed rowgroup (presumably to do the work to make sure it was clear the related row is in the delta store)

Voila– deadlock!

How do I fix it?

If I really want to churn changes into my columnstore index at the same time that I rapid-fire query the data, I may want to change my isolation level for the query counting the data.

Read committed snapshot isolation or snapshot isolation for the SELECT COUNT query ¬†can change this blocking scenario and sneak us right past those deadlocks — as long as enabling those is the right thing for my application and my database server.

The more clever we get, the more ways things can backfire

I’m not writing this post to bash columnstore indexes — far from it. Churning tons of updates into a columnstore index isn’t necessarily what you want to do.

Changing to indexes or data can cause deadlocks. It can also make them go away! That’s a fact of life, and as we add more indexing tools to our toolkits, we still gotta live with it.

Want to learn more about deadlocks?

Check out my course, Troubleshooting Blocking & Deadlocks for Beginners. It gives you example code to create a deadlock, and walks you through tracing and decoding the deadlock graph, too.

Removing Query Hints with Plan Guides (Dear SQL DBA Episode 43)

If you need to add, remove, or replace hints from ad-hoc queries where you can’t change the code, plan guides can help.

See a demo of removing a query hint from parameterized TSQL run from an application, and get tips on how to make your plan guides work in SQL Server.

The code from the demo is here. Links for more info are below the video. Have fun!

Option 1: Listen to the 30 minute audio podcast episode (or download it!)

You can listen on the run by subscribing to the podcast on iTunes , on Google Play, or plug this RSS feed into your favorite podcast app:

Option 2: Watch the YouTube video

This is the exact same content as in the audio podcast, but with visuals of the slides and demos.

Further reading

The video shows creating a plan guide with sp_create_plan_guide to remove a query hint, but you can use plan guides to do much more!

  1. You can add or remove table hints (such as an index hint or a FORCESCAN hint), using slightly different syntax with sp_create_plan_guide. See examples in Books Online here.
  2. You can force parameterization of specific queries using sp_create_plan_guide to create a ‘TEMPLATE’ type plan guide. See example syntax on this type of plan guide here.
  3. You can “freeze” an execution plan so that it is always used for the query. For this one, you use the procedure sp_create_plan_guide_from_handle. See code on how to do this here.

Columnstore Index Returns Zero Rows… Which is One Row

I’ve never claimed to be great at math, but until recently¬†I thought I knew how to count to one.

Zero… one.

That’s what we learned in kindergarten. (I didn’t go to pre-school.)

Apparently SQL Server didn’t go to kindergarten

Because it can’t even count to one in some¬†execution plans.

I have a very simple query. It’s running against a table with a nonclustered columnstore index.

SELECT COUNT(*) FROM pt.FirstNameByBirthDate_1966_2015;

The query returns one row, as expected. Here’s my count:

For the record, that is the correct number of rows in the table. Here’s where things get weird. In the actual execution plan, the columnstore index returns zero rows.

Yes, this is¬†really the actual execution plan. I’m not tricking you, I promise.

Zero rows go into the hash match, but one row comes out

It would appear that this hash match has conjured a row from nowhere. If we look to the left of the hash match, a row appears!

Our hash match operator is psychic. Did it phone a hotline to find out the count of this table? If so, why is the columnstore index scan in the plan in the first place?

Let’s look at the properties of the columnstore scan

Things get even weirder.

The columnstore index scan executed in batch mode and used 4 cores. It did a scan with each thread, although somehow it did ZERO reads and accessed ZERO partitions in my partitioned table. (Whaaatttt?)

But it did do work. It did fancy, expensive work. Look at those locally aggregated rows!

This is an Enterprise Edition optimization for batch mode columnstore

These locally aggregated rows are¬†called ‘aggregate pushdown’.¬†Aggregate pushdown is one of the enhancements¬†that remains an Enterprise Only feature, even after SQL Server 2016 SP1. (Sunil Agarwal discusses these features here.) It makes your columnstore indexes even faster.

But that just looks wrong! It still outputs a row!

I agree, it looks wrong. So I asked Niko about it, because if anyone thinks like a columnstore index, it’s Niko. I asked when I was at SQLSaturday Portugal, too, which was just an awesome event, so I could ask in person and see his face.

I asked, “Is this a bug in my execution plan? Or a feature?”

Niko explained¬†that it’s a feature. Or at least, it’s on purpose. It’s just not very good at drawing lines. What the execution plan is¬†trying to say is more like this:

The plan is trying to explain that the hash match doesn’t have to do any of the counting, because the columnstore index did it all with enterprise¬†magic.

But… but… that doesn’t look right

SQL Server is drunk. At least, that’s what I said to Niko. And his face said that he perhaps agrees with me. It’s not like Niko¬†drew the lines, so don’t blame him! But he did confirm that this is not a bug in execution plans, this was done on purpose.

I guess if it’s using “new math”, SQL Server can count those rows however it wants.