27 votes
Accepted

Why MERGE doesn't insert more than 277 records into a table which is configured with temporal table and a non-clustered index on history table

Azure SQL Database sometimes builds an invalid execution plan for your merge-insert. When it decides to maintain the column store history table using a single operator (a narrow plan) everything is ...
Paul White's user avatar
  • 84k
19 votes
Accepted

Nonclustered index storage on clustered columnstore

The "bookmark" is the columnstore index original locator (per "Pro SQL Server Internals" by Dmitri Korotkevitch). This is an 8-byte value, with the columnstore index's row_group_id in the first 4-...
AMtwo's user avatar
  • 16.1k
16 votes
Accepted

Does it make sense to add indexes to a clustered columnstore index table?

My understanding of a clustered columnstore table (and please correct me if I am wrong) is that each column is stored in some physically ordered manner... All columns are sorted in the same order. I ...
Paul White's user avatar
  • 84k
15 votes
Accepted

Code creating clustered columnstore index while maintaining row order

A clustered columnstore index is fundamentally different from a clustered rowstore index. You may have noticed there is no key column specification for a clustered columnstore index. That's right: a ...
Paul White's user avatar
  • 84k
13 votes
Accepted

Why can it take up to 30 seconds to create a simple CCI rowgroup?

In many respects, this is expected behaviour. Any set of compression routines will have widely ranging performance depending on input data distribution. We expect to trade data loading speed for ...
Paul White's user avatar
  • 84k
13 votes

Lob Logical Read and lob read-ahead reads in NCCI

How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records? There is one LOB physical read and 1,877,324 read-ahead reads. Read-ahead is still a ...
Paul White's user avatar
  • 84k
13 votes

Why would a table with a Clustered Columnstore Index have many open rowgroups?

With constant trickle inserts, you very well may end up with numerous open deltastore rowgroups. The reason for this is that when an insert starts, a new rowgroup is created if all of the existing ...
Tony Hinkle's user avatar
  • 7,939
12 votes
Accepted

Columnstore Index in read_only filegroup prevents CheckDB

The problem occurs when DBCC tries to verify a deleted bitmap for a read-only columnstore table. Deleted bitmaps are stored on the same filegroup as the columnstore table. They track rows logically ...
Paul White's user avatar
  • 84k
12 votes
Accepted

Why are statistics slower to build on clustered columnstore?

Sampled statistics on a heap or b-tree use TABLESAMPLE SYSTEM. This algorithm employs an allocation-ordered scan and chooses pages from the scan to sample. All rows on the selected pages contribute to ...
Paul White's user avatar
  • 84k
11 votes
Accepted

When are dimension tables still needed using clustered columnstore indexing?

I don't think that your question is applicable to any RDBMS that supports columnar storage. I'm writing my answer from the point of view of SQL Server and most of the reasons depend on implementation ...
Joe Obbish's user avatar
  • 32.2k
11 votes

How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?

Benefits of partitioning a CCI: Query performance can be improved because a minimum level of rowgroup elimination is guaranteed, despite how the data gets loaded or modified. Most of the generic SQL ...
Joe Obbish's user avatar
  • 32.2k
11 votes
Accepted

How to Prevent Partitioned Columnstore Deadlocks on SELECT

Since you're on SQL Server 2016, it's worth mentioning that there is at least one public bug fix for parallel deadlocks involving columnstore indexes: FIX: A deadlock occurs when you run a parallel ...
Josh Darnell's user avatar
  • 29.2k
10 votes

Why can it take up to 30 seconds to create a simple CCI rowgroup?

I can't say exactly why this behavior is occurring but I believe I've developed a good model of the behavior via brute force testing. The following conclusions only apply when loading data into a ...
Joe Obbish's user avatar
  • 32.2k
9 votes

Error 35338: Clustered columnstore index is not supported

SQL Server 2012 only supports the creation of nonclustered columnstore indexes. Change your code to: CREATE NONCLUSTERED COLUMNSTORE INDEX cci_Simple ON [dbo].[Card] In SQL Server 2012 this will ...
James Anderson's user avatar
9 votes
Accepted

How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?

Update having taken partitioning all the way to production: Deciding the right partitioning for a clustered columnstore index (CCI) is a very bespoke process. If the wrong partitions are chosen, ...
Cyndi Baker's user avatar
8 votes

Why can it take up to 30 seconds to create a simple CCI rowgroup?

I believe, that this has to do with the internal optimisations of the compression for the single column tables, and the magic number of the 64 KB occupied by the dictionary. Example: if you run with ...
Niko Neugebuer's user avatar
8 votes
Accepted

Columnstore Aggregate Pushdown doesn't work for float/real data types

Microsoft has updated the documentation to say that float is not supported*. The documentation for aggregate pushdown was inaccurate in some places and it doesn't state all of the restrictions. In ...
Joe Obbish's user avatar
  • 32.2k
8 votes

Filter condition not correctly applied to Clustered Columnstore index

This is a bug with SQL Server. If a column is deleted from a table with a clustered columnstore index, and then a new column is added with the same name, it appears to be using the old, deleted column ...
Uberzen1's user avatar
  • 678
8 votes
Accepted

Slide Partition from Rowstore to Columnstore

No, this is not possible. ALTER TABLE... SWITCH is a metadata operation and both tables need to have identical schemas. There's no such thing as a partitioned table that has some columnstore ...
Joe Obbish's user avatar
  • 32.2k
8 votes
Accepted

Why is statistics update slower on a clustered columnstore than on a rowstore?

You are allowing SQL Server to choose the sampling rate for the statistics. Run the test again with a specific sample for both and you should see more comparable times. UPDATE STATISTICS dbo.rowstore ...
Paul White's user avatar
  • 84k
8 votes

Trim_reason = DICTIONARY_SIZE on Clustered ColumnStore index

I've heard that I should try to get 1,048,576 rows in each row_group. That's an ideal, but it's not always achievable in practice. It's not often a primary factor you should concentrate on in any ...
Paul White's user avatar
  • 84k
7 votes
Accepted

Where should I put nvarchar(max) dimensions in my data warehouse?

Microsoft recommends using a CCI for large data warehouse tables with a few caveats including: Don't use a clustered columnstore index when: The table requires varchar(max), nvarchar(max), or ...
Joe Obbish's user avatar
  • 32.2k
7 votes

Adding new columns to Table with Clustured Columnstore Index

What happens when I add another 50 columns to the table? These columns will be added to the table with NULL values if you have not specified a default value. If you have added a default value, the ...
Stijn Wynants's user avatar
7 votes
Accepted

Why does batch mode window aggregate yield arithmetic overflow?

Multiple commenters have been able to reproduce this issue. We initially thought that SQL Server 2017 CU10 resolved the issue, but then found that the error can be reproduced in all versions of SQL ...
7 votes
Accepted

SELECT from Clustered Columnstore results in LOB Reads

Multiple sources appear to indicate Columnstore Indexes store its data as LOB data, which is happening in this case. My understanding and take-away is that it should always be expected for LOB reads ...
SQL Sean's user avatar
  • 235
7 votes
Accepted

Filter condition not correctly applied to Clustered Columnstore index

This bug does not require dropping or renaming columns. You will also see the same behaviour for statusId = 100 which was never present in any version of the column. Requirements A clustered ...
Paul White's user avatar
  • 84k
7 votes
Accepted

Why would a table with a Clustered Columnstore Index have many open rowgroups?

Why would a table with a Clustered Columnstore Index have many open rowgroups? There are many different scenarios that can cause this. I'm going to pass on answering the generic question in favor of ...
Joe Obbish's user avatar
  • 32.2k
7 votes
Accepted

Adding COMPRESSION_DELAY to an existing COLUMNSTORE INDEX

An existing columnstore index can be altered as such: ALTER INDEX ColumnstoreIndexName ON SchemaName.ObjectName SET (COMPRESSION_DELAY = x Minutes); As found in the documentation on ALTER INDEX ...
Randi Vertongen's user avatar
7 votes
Accepted

How to control Segmentation min/max data_id on a non-clustered ColumnStore index

This isn't directly supported for nonclustered columnstore indexes. It does work for clustered columnstore. Azure Synapse Analytics has language support for doing it in one step e.g.: CREATE ...
Paul White's user avatar
  • 84k
7 votes
Accepted

Columnstore: terrible execution plan - filter instead of seek

You will pretty much never want a column store scan on the inner side of a nested loops join. The engine doesn't support batch mode in that scenario (batch mode column store scans can't be rewound). ...
Paul White's user avatar
  • 84k

Only top scored, non community-wiki answers of a minimum length are eligible