Tempdb Blocking With Non-Clustered Columnstore Indexes

Ned Otter runs into a tricky issue:

I have a client that used Itzik Ben-Gan’s solution of creating a filtered nonclustered columnstore index to achieve batch mode on a rowstore (in fact I proposed that the client consider it). They have an OLTP system, and often perform YTD calculations. When they tested, processing time was reduced by 30 to 50 percent, without touching a single line of application code. If that ain’t low hanging fruit, I don’t know what is —

However, during testing, I noticed some intermittent blocking that didn’t make sense to me. But I couldn’t nail it down, and they went live with the “filtered nonclustered columnstore index” solution.

Once they deployed – and there was a lot of concurrency – I could see what had eluded me during my proof of concept: blocking in tempdb.

Read on for the repro and check out Ned’s UserVoice bug report.

Related Posts

Lock Promotion

Erik Darling tries to figure out why his locks can’t get ahead in the rat race: The first thing I found is that there were 16 attempts at promotion, and four successful promotions.Why did this seem weird? I dunno.Why would there be only 4 successful attempts with no competing locks from other queries?Why wouldn’t all […]

Read More

Triggering KB 4462481

Joe Obbish shows how you can recreate the error described in KB 4462481: Consider a query execution that meets all of the following criteria: 1. A parallel INSERT INTO… SELECT into a columnstore table is performed 2. The SELECT part of the query contains a batch mode hash join 3. The query can’t immediate get […]

Read More

Categories

November 2018
MTWTFSS
« Oct Dec »
 1234
567891011
12131415161718
19202122232425
2627282930