5

I'm doing some testing of columnstore indexing on a single table that has about 500 million rows. The performance gains on aggregate queries have been awesome (a query that previously took about 2 minutes to run now runs in 0 seconds to aggregate the entire table).

But I also noticed another test query that leverages seeking on an existing rowstore index on the same table is now running 4x as slow as it previously did before creating the columnstore index. I can repeatedly demonstrate when dropping the columnstore index the rowstore query runs in 5 seconds, and by adding back in the columnstore index the rowstore query runs in 20 seconds.

I'm keeping an eye on the actual execution plan for the rowstore index query, and it's almost exactly the same in both cases, regardless if the columnstore index exists. (It uses the rowstore index in both cases.)

The rowstore test query is:

SELECT *
INTO #TEMP
FROM Table1 WITH (FORCESEEK)
WHERE IntField1 = 571
    AND DateField1 >= '6/01/2020'

The rowstore index used in this query is: CREATE NONCLUSTERED INDEX IX_Table1_1 ON Table1 (IntField1, DateField1) INCLUDE (IntField2)

The columnstore test query is:

SELECT COUNT(DISTINCT IntField2) AS IntField2_UniqueCount, COUNT(1) AS RowCount
FROM Table1
WHERE IntField1 = 571 -- Some other test columnstore queries also don't use any WHERE predicates on this table
    AND DateField1 >= '1/1/2019' 

The columnstore index is: CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Table1_2 ON Table1 (IntField2, IntField1, DateField1)

Here is the execution plan for the rowstore index query before I create the columnstore index: Execution Plan - Rowstore Index - Pre-Columnstore Index Creation

Here is the execution plan for the rowstore index query after I create the columnstore index: Execution Plan - Rowstore Index - Post-Columnstore Index Creation

The only differences I notice between the two plans is the Sort operation's warning goes away after creating the columnstore index, and the Key Lookup and Table Insert (#TEMP) operators take significantly longer.

Here is the Sort operation's info with the warning (before creating the columnstore index): Sort Operation - Warning

Here's the Sort operation's info without the warning (after creating the columnstore index): Sort Operation

I would've thought a read query that is specifically leveraging the same rowstore index and execution plan in both cases should have roughly the same performance on every run, regardless of what other indexes exist on that table. What gives here?

Edit: Here's the TIME and IO stats before creating the index: Stats - Before Columnstore Index Creation

Here's the stats after creating the columnstore index: Stats - After Columnstore Index Creation

  • 2
    The key lookup has to go to the clustered index. Can you re-create the non-clustered index in a way that avoids the key lookup? Or change SELECT * to just the columns covered by the index? – Aaron Bertrand Jun 25 at 17:27
  • 2
    Can you add IO and time statistics for both queries? – Piotr Jun 25 at 18:07
  • 1
    @AaronBertrand I get that the key lookup adds an additional performance bottleneck but I'm not sure why that bottleneck wouldn't be constant regardless if a columnstore index exists or not. It appears that operation is heavier after a columnstore index exists on that table. That being said, I'll re-write the query to only select the fields included in the nonclustered rowstore index and re-run my tests pre and post the columnstore index to see if I still see the same change in performance I'm currently seeing... Please stand by... – J.D. Jun 25 at 18:08
  • 1
    @AaronBertrand Re-writing the query to only select the included columns so that it eliminates the key lookup operation from the query plan seems to make the difference, the runtimes are consistent now regardless of there being a columnstore index or not. But I'm still confused why that's relevant for when a columnstore index exists (even the clustered index is a rowstore index, so the key lookup shouldn't have anything to do with this columnstore index)? – J.D. Jun 25 at 18:17
  • 1
    @Piotr Added the stats to the bottom of my question. (Looks like there's a lot more Read-Ahead Reads after the columnstore index is created, and they're all happening on Table1 instead of the Worktable.) – J.D. Jun 25 at 18:25
6
+50

Adding the nonclustered columnstore index allows for a batch mode sort in the second execution plan. This causes all of the processing to be done on one thread - so even though the query has a parallel plan, it's essentially running serially. You can see that by looking at the details of the different operators.

I reproduced your problem locally, here's the sort operator with per-thread counts - as you can see, everything is on thread 1:

screenshot of SSMS showing thread skew and execution mode for the sort operator

Notice the "Actual Execution Mode" is "Batch."

Everything after the sort (the nested loops join, key lookup, etc) is essentially serial, which is what slows the query down.

See this KB article for details and possible solutions:

Adds trace flag 9358 to disable batch mode sort operations in a complex parallel query in SQL Server 2016

Batch mode sorts were introduced in SQL Server 2016 under compatibility level 130. If a query execution plan contains parallel batch mode sorts in conjunction with directly-upstream parallel operators, you may encounter degraded performance compared to row mode sort plan equivalents.

This occurs due to a parallel batch sort outputting fully sorted data via a single thread to the upstream parallel operator (for example, a parallel merge join operator). The performance degradation occurs when the upstream parallel operator uses single-threaded processing due to the incoming single-threaded batch mode sort operator.

For completeness, the options outlined there are either:

  • enable TF 9358
  • enable query optimizer hotfixes (through TF 4199, the QUERY_OPTIMIZER_HOTFIXES database option, or the ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint)

Getting rid of the sort is another solution for this problem. The sort is only present to try and prevent too much random I/O from the nested loops join, which is using unordered prefetch, as mentioned in this article by Craig Freedman:

Optimizing I/O Performance by Sorting – Part 1

The plan uses the non-clustered index to avoid unnecessarily touching many rows. Yet, performing 64,000 random I/Os is still rather expensive so SQL Server adds a sort. By sorting the rows on the clustered index key, SQL Server transforms the random I/Os into sequential I/Os.

You can get rid of the sort by:

  • eliminating the need for the key lookup (by selecting less columns, or creating a covering nonclustered index)
  • disabling nested loops prefetching by adding (undocumented, unsupported trace flag) OPTION (QUERYTRACEON 9115) to the query
| improve this answer | |
  • Hmm interesting and I think you figured it out. I do see the Actual Execution Mode on the Sort operator change from Row to Batch mode in my execution plan, before and after I create the columnstore index. Still bizarre to me that this happens and the fixes aren't necessarily "simple". Makes complete sense how the key lookups issue becomes dramatically worse when having to run serially though. If I was able to remove the Sort operator from my execution plan, I would assume that would also fix things?...stupider question, why do I have a Sort operator? (It's on the key field of the table.) – J.D. Jun 29 at 22:15
  • 1
    Saying it out loud I think I might've figured out the answer to my own question. The Sort operator on the key field is so that it can efficiently do the Key Lookups, isn't it? – J.D. Jun 29 at 22:16
  • 1
    @J.D. I agree it's a pretty crappy situation, fortunately it's fixed by the "optimizer hotfixes" which hopefully most folks are turning on. You're right about the sort, I updated the question and added a reference for that. – Josh Darnell Jun 30 at 12:48
  • 1
    Awesome, thanks for cracking this one and explaining it to me. Really helpful to know! – J.D. Jun 30 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.