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
6 votes
1 answer
260 views

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

I've moved several large tables (each with >10^9 rows and a couple of dozens columns) from clustered rowstore to clustered columnstore indexes on a SQL Server 2014 instance and noticed that ...
user avatar
4 votes
1 answer
81 views

Columnstore index predicate is not used in index scan

We are using a clustered columnstore index table with ~50M records and experiencing big performance degradation when running on GCP cloud sql vs. running locally using the same db schema and data (...
user avatar
4 votes
1 answer
255 views

Why significantly worse Column Store performance on Read-only AG secondary DB?

We have a pair of SQL 2019 Enterprise instances hosted in identically spec'd and configured VMs in Azure. They for a simple primary/secondary availability group array, containing a small number of ...
user avatar
  • 819
0 votes
0 answers
24 views

Adding new column to clustered column store indexed table

I designing clustered column store indexed fact tables. Later on many new columns might be added to these currently ~90 columns tables. How adding new nullable column is affected by the size (number ...
user avatar
  • 266
0 votes
1 answer
41 views

How to change default compression mode in mariaDB Columnstore?

In mariaDB there is a way to disable columnstore compression in session level (and set the disable flag in each session). Is there any way to change the default compression mode?
user avatar
0 votes
0 answers
60 views

Is a columnstore index appropriate when all columns are (mostly) unique?

I have a longish table ( 60M rows ) where there are only 7 columns. One is a unique ID, two are datetimes, and two are notes and descriptions. There the notes and descriptions are very regular, except ...
user avatar
  • 101
3 votes
2 answers
324 views

Columnstore: terrible execution plan - filter instead of seek

See the execution plan on https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise) I have a datawarehouse table peak_reporting_data which tracks the activity per day and hour and ...
user avatar
0 votes
0 answers
73 views

UPDATE on a clustered COLUMNSTORE table with 150 columns and 30 mln records just stuck

I have a table TABLE_A which is having 150 columns (with 5 lob type) and 30 millions. User were facing read performance issue, so I created CLUSTERED COLUMNSTORE on the table. After I converted the ...
user avatar
0 votes
1 answer
62 views

Clustered Column Store Index - does it have max row size limitation?

Updatable Clustered Column Store Index (SQL Server 2016+) - does it have max row size (8060) limitation, or you can put any row size you want in there ? In other words, is it possible to get below ...
user avatar
0 votes
1 answer
30 views

Error loading data in MariaDB Columnstore

I have a MariaDB Columnstore database running on the cloud in Linux. I'm trying to export a CSV file from my Windows machine to the database. I created the file with DBeaver, setting the new line as \...
user avatar
  • 51
0 votes
1 answer
96 views

Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements

My Table tbl_FieldeData_ColStore Data Example: /NonClustred Column Store Index on Table/ CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore ( PDID,FieldID ) /Below Select was ...
user avatar
0 votes
2 answers
45 views

Columnar DBMS and issue understanding multiple where constraints

I read a lot about columnar DMBS and perfectly understand that they are great for researches on a column. This question has a partial answer. Now, say I have a DMBS with 3 columns A, B and C. Doing a ...
user avatar
  • 101
0 votes
0 answers
51 views

"Error opening column file" in MariaDB Columnstore data cpimport

I'm trying to import a large file, around 2GB, into MariaDB Columnstore. Other files that are smaller worked fine, so I suspect it's a size issue. I create the table with engine Columnstore with no ...
user avatar
  • 51
0 votes
0 answers
44 views

Database Page Compression and ColumnStore Archive - is data compressed when in buffer pool and when sent to client? [duplicate]

One of benefits of Page Compression or ColumnStore Archive is reduced storage space requirements Here I have two questions: When compressed data is read from disk to buffer pool, in the buffer pool ...
user avatar
0 votes
0 answers
74 views

Clustered Columnstore Index alignment on multiple columns

I have a fact table which is a clustered columnstore index. It is also: -partitioned by a single data date column. -has a nonclustered, aligned, composite unique index (PK) on 6 INT columns, plus the ...
user avatar
  • 266
0 votes
2 answers
123 views

Storing Live Timeseries Data in SQL Server Azure Database

I have an SQL Server Azure Database and am trying to store time-series data that I get from multiple sensors. Data Source:- 5-minute data is obtained via an API. Current table structure: - Timestamp |...
user avatar
1 vote
1 answer
274 views

MariaDB Column Store Suitability for Data Warehouse

We want to build a data warehouse to distribute valid information across the organization (reporting) and get some insights into our data (analytics). After some research we found MariaDB using Column ...
user avatar
  • 13
0 votes
1 answer
111 views

What is the most performant way to group by and join high cardinality columns in column based table?

I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
user avatar
  • 101
0 votes
1 answer
51 views

Optimize BETWEEN Query in SQL Server Azure

I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:- Timestamp | ComponenentID | Parameter1 | ...
user avatar
0 votes
1 answer
55 views

Is columnstore a good option for UPDATES with WHERE clauses?

I am developing a database project on SQL Server and I am thinking whether using columnstore index is a good idea. The project consists of a table (A) that will hold a large number of rows, with many ...
user avatar
  • 103
2 votes
2 answers
266 views

What is the correct and performant pattern for loading (and querying) a large clustered columnstore table?

I am doing some test with clustered columnstore tables - both partitioned and not. In my research, I've seen flavors of this pattern: -- step 1: make new table SELECT TOP 0 * INTO [Fact].[Order_CCI] ...
user avatar
5 votes
2 answers
252 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 ...
user avatar
  • 3,609
1 vote
1 answer
338 views

Slow inserts in mariadb columnstore

I have just started on researching the feasibility of using MariaDB's columnstore for OLAP, and I find inserts are very slow. This is MariaDB 10.5 on a debian 10 system, just an elderly desktop with ...
user avatar
  • 221
2 votes
2 answers
373 views

How does a column-oriented DBMS filter on multiple columns?

I'm learning how column-oriented DBMS / "columnars" work for OLAP situations. Let's say we have a log of millions of transactions with 3 columns: timestamp, shop, product, and we want to ...
user avatar
  • 161
5 votes
2 answers
474 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 ...
user avatar
  • 266
1 vote
1 answer
79 views

Could database with compat level 100 support columnstore index?

I have an instance which runs on SQL Server 2017 Developer Edition instance. I restored database ContosoRetailDW which had compatibility level 100. After restore operation, I run the following query: ...
user avatar
  • 1,163
0 votes
1 answer
24 views

Does binary logging apply to ColumnStore?

When binary logging is enabled, does MariaDB's binary logging also record ColumnStore events, and not just InnoDB events? For example, does it log an UPDATE made to a ColumnStore table row? I've ...
user avatar
  • 103
0 votes
2 answers
100 views

Why am I getting error 35353 when enabling CDC on a table with a CCI?

I'm trying to enable Change Data Capture on a table with a clustered columnstore index, a unique non-clustered index, and a non-unique non-clustered index in SQL Server Standard 2019. The command ...
user avatar
1 vote
1 answer
188 views

Eliminate filter operator before columnstore index scan operator

I have a large fact table with millions of rows called MyLargeFactTable, and its a clustered column store table. There is a composite primary key constraint on it there as well (customer_id,...
user avatar
  • 266
3 votes
1 answer
148 views

Row vs Columnar DB for queries with multiple OR statements

I understand columnar databases are great for speed queries where you don't use many fields to lookup against, but what if you only used OR statements? For instance, I want all the records that have (...
user avatar
  • 133
8 votes
2 answers
1k 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 ...
user avatar
1 vote
1 answer
260 views

Azure tempdb filling up while trying to convert a large table to columnstore... how to mitigate?

I have a large reporting table (approx 6B rows) taking up roughly 400GB of storage on an azure sql db. I've been trying to convert it to a columnstore table using the following commands: insert Logs(...
user avatar
5 votes
1 answer
433 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 ...
user avatar
  • 24k
0 votes
1 answer
101 views

Recommendations about deleting large set of rows in MSSQL [closed]

I need to delete about 75+ million rows from a table everyday that contains around 3.5 billions of record. Database recovery mode is simple, I have writen a code that deletes 15.000 rows in a while ...
user avatar
1 vote
1 answer
213 views

If a rowstore index is stored in a b-tree data structure, what kind of data structure is used for a columnstore index? [duplicate]

My understanding of how regular rowstore indexes are stored is in a type of b-tree data structure, but I was wondering because of the different nature of columnstore indexes, what type of underlying ...
user avatar
  • 24k
4 votes
1 answer
219 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 ...
user avatar
  • 1,966
1 vote
1 answer
66 views

Do I still need OLAP cubes after a SQL Server Upgrade

After our DBA got sacked for poor performance, I am now our accidental DBA. I have been pushing to upgrade our SQL Server instance from 2012 to 2017 so I can use the clustered column store indices in ...
user avatar
  • 113
2 votes
1 answer
406 views

Adding COMPRESSION_DELAY to an existing COLUMNSTORE INDEX

When I create an COLUMNSTORE index I can specify COMPRESSION_DELAY = x [minutes] in the CREATE-Statement. It delays the movement of the rows in an closed segment from the delta store to the compressed ...
user avatar
22 votes
3 answers
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 ...
user avatar
  • 9,466
4 votes
2 answers
2k views

Columnstore Index maintenance and finding fragmentation

We are using clustered columnstore indexes and partitions in SQL Server 2014. Some tables have a lot of updates and we find ourselves with a lot of deleted rows for some partitions. In our tests, a ...
user avatar
0 votes
1 answer
1k views

Do I still need to disable or drop column store index before insert and/or update on big tables?

As the title says, I thought that from MSSQL 2016 I don't really have to drop or disable ColumnStore indexes before ETL and rebuild it after. Any consensus?
user avatar
  • 1
0 votes
1 answer
200 views

Table with Clustered Columnstore Index bigger than a Clustered Rowstore Index

We have a query that is doing an index scan on a 36 million row table that has a Clustered Columnstore Index. All the cost of the plan is pretty much on the index scan of this Clustered Columnstore ...
user avatar
8 votes
2 answers
720 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] ...
user avatar
4 votes
1 answer
313 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 ,...
user avatar
10 votes
2 answers
1k 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 ...
user avatar
6 votes
1 answer
438 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 ...
user avatar
  • 235
2 votes
1 answer
451 views

Storing Social Security Number as DECIMAL(10,9) for Columnstore

I am working on a VERY large database (10B + rows) that performs matching on SSN and BirthDate to try and find duplicate records. The table is using columnstore compression (SQL SERVER 2016) and it ...
user avatar
  • 31
2 votes
3 answers
629 views

Database solution for a large matrix/very wide table

Suppose I have these two data frames (simplified for my question): users +---------+ | user_id | +---------+ | 1 | | 2 | | ... | +---------+ articles +------------+------------+ | ...
user avatar
  • 173
0 votes
1 answer
487 views

Performance Metrics: Columnstore Indexes vs SSAS Cubes [closed]

What has faster performance speed: Columnstore Indexes or SSAS Cubes? Are Columnstore indexes proven to be faster than SSAS cubes (SQL Analysis Services)? Have their been any performance studies ...
user avatar
10 votes
3 answers
628 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 ...
user avatar
  • 678