Questions tagged [columnstore]

For questions about column-oriented storage and access mechanisms in databases. Although the term "columnstore" itself is used by Microsoft and MariaDB for their respective implementations, you should use this tag for questions about other vendors' technologies (e.g. IBM Db2 column-organized tables) and for questions about the technology in general, not specific to a particular vendor.

Filter by
Sorted by
Tagged with
28
votes
1answer
751 views

Is the eager spool operator useful for this delete from a clustered columnstore?

I'm testing deleting data from a clustered columnstore index. I noticed that there is a large eager spool operator in the execution plan: This completes with the following characteristics: 60 ...
23
votes
1answer
5k views

What exactly can SQL Server 2014 execute in batch mode?

When a columnstore index is being used in a query SQL Server is able to use batch mode. Documentation is thin on what can run in batch mode and what can't. Please look at the following (motivating) ...
21
votes
1answer
2k views

What's the anatomy of a columnstore index?

One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index. I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
21
votes
3answers
1k views

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

I was experiencing some performance issues with a query yesterday and upon further investigation, I noticed what I believe is odd behavior with a clustered columnstore index that I'm trying to get to ...
20
votes
3answers
662 views

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

I was working on a demo involving CCIs when I noticed that some of my inserts were taking longer than expected. Table definitions to reproduce: DROP TABLE IF EXISTS dbo.STG_1048576; CREATE TABLE dbo....
19
votes
3answers
6k views

Clustered columnstore indexes and foreign keys

I am performance tuning a data warehouse using indexes. I am fairly new to SQL Server 2014.Microsoft describes the following: "We view the clustered columnstore index as the standard for storing ...
19
votes
1answer
1k views

Nonclustered index storage on clustered columnstore

In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object's bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is ...
15
votes
2answers
389 views

Columnstore Index in read_only filegroup prevents CheckDB

It appears setting a filegroup to read_only prevents dbcc checkdb for the entire database if the filegroup contains a columnstore index. When attempting to run checkdb or checkfilegroup (for any ...
14
votes
4answers
3k views

Does the order of columns in a columnstore index matter?

I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table. Will there be any change in performance based on the order of columns that I use ...
12
votes
1answer
636 views

How to make use of Batch Mode with an UNPIVOT (a loop join)?

I have a query of the following form: SELECT ... FROM ColumnstoreTable cs CROSS APPLY ( SELECT * FROM (VALUES ('A', cs.DataA) , ('B', cs.DataB) , ('C', cs.DataC) ) ...
12
votes
1answer
240 views

Why does batch mode window aggregate yield arithmetic overflow?

The following query performs a windowed SUM over a columnstore table with 1500 total rows, each of which has the value 0 or 1, and it overflows the INT data type. Why is this happening? SELECT a, p, ...
10
votes
3answers
27k views

Create failed for Index 'ClusteredColumnStoreIndex'. Timeout expired

I am creating Clustered Column Store Index in SQL Server 2014. I am getting error as "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not ...
10
votes
2answers
967 views

How to Prevent Partitioned Columnstore Deadlocks on SELECT

I have three Clustered Columnstore Index (CCI) tables in SQL Server 2016. All of these CCIs are in the same partitioning scheme, based on Tenant ID. Lately, and inconsistently, I am getting deadlocks ...
10
votes
3answers
533 views

Filter condition not correctly applied to Clustered Columnstore index

Using the below example, the predicates are the same, however the top statement (correctly) returns 0 rows, the bottom statement returns 1 - even though the predicates do NOT match: declare @barcode ...
9
votes
2answers
655 views

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

I have a data warehouse comprised of four clustered columnstore index tables (CCI) and nine rowstore tables. These tables are used only for analytics and the CCI data is inserted from staging tables ...
9
votes
2answers
3k views

Code creating clustered columnstore index while maintaining row order

I want to convert a rowstore table to a columnstore table by creating a clustered columnstore index. There are three columns in the table: id,time, and value. The table is ordered by id and time ...
9
votes
2answers
1k views

Identity column in columnstore index

I have an extremely large table IMO (~137 million rows) with lots of repeated data, lots of NULL columns, and such. I am considering exploring this using a table with a COLUMNSTORE INDEX and I have ...
8
votes
4answers
881 views

Has the guidance on the setting for 'cost threshold for parallelism' changed with the advent of columnstore indexes?

First off, what I'm not asking. I'm not asking what my setting should be. Many are recommending upping the value past the default, and I certainly understand why that's the case for B-Tree based ...
8
votes
2answers
441 views

Lob Logical Read and lob read-ahead reads in NCCI [duplicate]

I have a test table with following structure. CREATE TABLE [dbo].[DW_test]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [CourtCaseID] [int] NOT NULL, [ActionID] [int] NOT NULL, PRIMARY KEY CLUSTERED([ID] ...
7
votes
2answers
3k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different dimensions ...
7
votes
2answers
699 views

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

Question is similar to this one, but the answer doesn't seem to answer this question. My understanding of a clustered columnstore table (and please correct me if I am wrong) is that each column is ...
7
votes
2answers
837 views

Do BIT columns offer any performance advantages for CCIs?

Do BIT columns offer any performance advantages when used in Clustered Columnstore Indexes? I am interested in any performance benefits gained from defining a column in a CCI as BIT instead of BIGINT, ...
6
votes
1answer
387 views

Slide Partition from Rowstore to Columnstore

I have a rowstore table which contains ~10 billion records. The table is partitioned on an INT field and has about 1,400 active partitions. I also have a clustered columnstore table built on the same ...
6
votes
1answer
410 views

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

I am having an issue with Aggregate Pushdown for float/real data types. According to the documentation, Aggregate Pushdown is supported for "Any datatype <= 64 bits" or 8 bytes (): ...
6
votes
1answer
1k views

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

I am designing the ERD for a new data warehouse. SQL Server 2016 Clustered Columnstore Indexes for the "fact" tables Loose star schema I put "fact" in quotes and say "loose star schema" because, ...
6
votes
1answer
1k views

Clustered columnstore index performance SQL Server 2014

I'm setting up an OLAP database using SQL Server 2014. The core fact table has about 40,000,000 rows, 225 columns with an average row size of 181 bytes. I've been toying around with the clustered ...
6
votes
1answer
1k views

Clustered columnstore index space usage

I have a simple table with a clustered columnstore index: ID INT NOT NULL, Hash BINARY(20) NOT NULL This table has some billion rows and according to sp_spaceused, sys.allocation_units and SSMS ...
6
votes
2answers
463 views

Columnstore index and low selective columns

I have a table with almost 1T rows. create table bigtable ( K1 int, K2 date, -- PK columns C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ... B1 bit, B2 bit, ........
5
votes
3answers
2k views

Does SQL Server require more RAM to hold columnstore indexed tables than traditional rowstore?

I would like to implement (SQL Server 2014) clustered columnstore indexes on some very large, wide tables. Will I need more RAM to support this? If so, how do I determine how much?
5
votes
1answer
877 views

When are dimension tables still needed using clustered columnstore indexing?

I am using MS SQL Server 2016 Clustered Columnstore Indexing (let's call it CCI) in my reporting database. In initial designs I was thinking star schema but then I started playing with CCI. Now I ...
5
votes
2answers
161 views

SQL Server 2019 column store indexes - maintenance

I have a clustered columned stored index on table used for logging - only inserts (but not bulk inserts). The current table stats are: 3541 millions rows 6.6 GB reserved space I see this morning the ...
5
votes
2answers
196 views

Clustered columnstore index on small tables

Clustered column store indexed tables in general are useful for large tables. Ideally with million of rows. And also useful with queries, which selects only the subset of available columns in such ...
5
votes
1answer
168 views

Can adding a columnstore index to a table affect read performance of a query that uses a rowstore index on the same table?

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 ...
5
votes
1answer
794 views

Column Store Index over an clustered index (created when primary key is declared) - SQL Server

When a clustered index is created, the table itself becomes the index structure sorted by the index key. Imagine now that we have 5 columns in a table XPTO (a,b,c,d,e) and "a" is the primary key and ...
5
votes
1answer
734 views

Columnstore index for Key/Value Pair table on SQL Server

I need to build a database for storing assessments. Each assessment can have an indefinite number of questions, and an indefinite number of responses. Responses for each assessment can grow up to ...
5
votes
2answers
584 views

How to keep smaller transaction logs files while rebuilding index of very large table

I need all the help that I can get to get this index rebuild successful. Especially an expert advise on transaction log management. Background The target database is a DW database hosting 3 big ...
5
votes
1answer
2k views

Any experience with cstore_fdw? [closed]

It looks like there is a (finally) an open source columnar and compression extension for Postgres. I wonder if anyone had any experience with it yet? I've worked with Greenplum for years, but I've ...
5
votes
1answer
809 views

SQL Server 2016 - ColumnStore Clustered Index vs. Non-ColumnStore Clustered Index

I just read the SQL 2016 Columnstore Index guide located here. We're going to have some rather large tables (hundreds of millions to billions of rows) in a SQL 2016 database that will be used for both ...
5
votes
2answers
2k views

Are indexes unnecessary for columnar database?

In a columnar database, are indexes unnecessary or useless?
5
votes
1answer
190 views

Restored DB has data, DBCC CHECKDB is clean, but SELECT TOP 1 returns zero rows [closed]

Having restored a client's SQL 2014 EE DB onto our Developer Edition instance, without issue, then running a DBCC CHECKDB that found zero errors, and a clustered columnstore index alleging over 1Bn ...
4
votes
1answer
508 views

SELECT from Clustered Columnstore results in LOB Reads

I am performing a SELECT from the following Clustered Columnstore Index which contains 5.2 Billion Records. When I review the output of STATISTICS IO, I am seeing LOB Logical reads. My question is ...
4
votes
1answer
112 views

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

Given a simple row-based table without a PK but with a row-based clustered index like so: create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value]) Then I wish to ...
4
votes
1answer
478 views

How to drop an in-memory temporal columnstore

Consider the following: CREATE DATABASE [Foo] ALTER DATABASE [Foo] ADD FILEGROUP XTP CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE [Foo] ADD FILE (NAME=XTP,FILENAME='C:\Program Files\Microsoft SQL ...
4
votes
2answers
234 views

Columnstore index on multi-tenant database

I have a sql azure database that is used only for analytics. It is constantly being updated with data pushed from the OLTP system. The OLTP inserts/updates/deletes are pushed via webhooks to a ...
4
votes
1answer
200 views

MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

I have an indexed view with non-clustered columnstore index, e.g. CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS SELECT BusinessDate, ItemId ,...
4
votes
1answer
763 views

Table partitioning and future values

I have a clustered columnstore table with approximately 1.4 billion rows. I figured it being a clustered columnstore would best suit my need for various aggregations on many different splits/columns. ...
4
votes
2answers
818 views

Rebuilding large columnstore indexed tables - am I doing it right?

We are planning to move the database from SQL 2014 ENT into 2016 ENT. We realized the data currently in columnstore table are not aligned in time when they are loaded, so we need to re-align the data. ...
3
votes
1answer
296 views

Why does the Tuple Mover in C-Store consider only rows older than LWM?

In the Tuple Mover section of the C-Store paper by Michael Stonebraker (link: http://db.csail.mit.edu/projects/cstore/vldb.pdf) the following is described: MOP (merge out process) will find all ...
3
votes
1answer
412 views

sp_estimate_data_compression_savings for column store compressions

Is there a way to get compressions saving for column store and column store column store archival compression? In order to check these compressions saving I have to drop all clustered and non ...
3
votes
1answer
938 views

How Ola Hallengrens Index maintenance scripts handles columnstore indexes?

I'd like to know how Ola's script handles columnstore indexes. Couln't find any information about that, only in the version history it was mentioned that script does have a support. My experience is ...