on November 9, 2017
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):
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_FirstNameByBirthDate_filtered
ON dbo.FirstNameByBirthDate ([FirstNameByBirthDateId], [FakeBirthDateStamp],
[StateCode], [FirstNameId], [Gender], [Flag1], [Flag2])
WHERE (FakeBirthDateStamp < '2015-01-11');
GO
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:
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:
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_FirstNameByBirthDate_filtered
ON dbo.FirstNameByBirthDate ([FirstNameByBirthDateId], [FakeBirthDateStamp], [StateCode], [FirstNameId], [Gender], [Flag1], [Flag2]);
GO
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…
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_FirstNameByBirthDate_filtered
ON dbo.FirstNameByBirthDate ([FirstNameByBirthDateId], [FakeBirthDateStamp], [StateCode], [FirstNameId], [Gender], [Flag1], [Flag2])
WHERE (FakeBirthDateStamp < '2015-01-11');
GO
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…
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_FirstNameByBirthDate_filtered
ON dbo.FirstNameByBirthDate ([FirstNameByBirthDateId], [FakeBirthDateStamp], [StateCode], [FirstNameId], [Gender], [Flag1], [Flag2])
WHERE (FakeBirthDateStamp < CAST('2015-01-11' AS DATETIME2(0)));
GO
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.