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
0
votes
1answer
33 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 | ...
0
votes
1answer
46 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 ...
2
votes
2answers
93 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] ...
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 ...
0
votes
1answer
38 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 ...
1
vote
2answers
82 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 ...
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 ...
1
vote
1answer
53 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: ...
0
votes
1answer
13 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 ...
0
votes
2answers
41 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 ...
1
vote
1answer
96 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,...
3
votes
1answer
78 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 (...
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 ...
1
vote
1answer
87 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(...
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 ...
0
votes
1answer
69 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 ...
1
vote
1answer
75 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 ...
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 ...
1
vote
1answer
35 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 ...
2
votes
1answer
230 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 ...
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 ...
2
votes
2answers
1k 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 ...
0
votes
1answer
642 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?
0
votes
1answer
135 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 ...
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] ...
4
votes
1answer
201 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 ,...
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 ...
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 ...
2
votes
1answer
280 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 ...
2
votes
3answers
453 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 +------------+------------+ | ...
0
votes
1answer
388 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 ...
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 ...
0
votes
0answers
45 views

MS SQL 2017: Generate estimate execution plan take more than 3 min

Sometimes generate Execution Plan (estimated) for simple query take ~3min Query: SELECT MAX(field) FROM table_name WHERE Org = x and PartKey = y The table is columnstore cluster index SQL Server ...
2
votes
1answer
89 views

Does Batch Mode only apply to Agg Functions on ColumnStore Index

Azure SQL Database - Standard Edition (S3 Service Tier) Why does Batch Mode only come into effect when using Aggregate Functions? DROP TABLE IF EXISTS dbo.TransCS CREATE TABLE dbo.TransCS ( ...
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 ...
-1
votes
1answer
143 views

Column store index for on premise Kimball Data Warehouses

Are column store tables now the vendor default method for Kimball Data Warehouse tables with Dim and Facts for On Premise? I know in the cloud, Microsoft automatically recommends Azure Data warehouses ...
0
votes
1answer
54 views

MariaDB Columnstore unexplained behaviour using CASE

We were updating column value based on col 'string_hash_id' value, but we couldn't get through: Error Code: 1815. Internal error: CAL0002: Update Failed: TreeNode::getDecimalVal: non-support ...
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 ...
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 ...
-2
votes
1answer
578 views

Is there any Performance Issue joining Row based Table to Clustered Columnstore Index Table [closed]

Is there any Performance Degradation joining Row based Table to Clustered Columnstore Index Table? Does SQL Server have any guidance for or against this? We have a table that is 2 million rows, and ...
1
vote
1answer
1k views

How Large Table before Using Clustered Columnstore Index

At what size should we consider applying Columnstore Index on a table? Is there a SQL Server storage or design guidance? Our Data Warehouse is 5TB total. Additionally, we have tables which are 100GB ...
2
votes
1answer
409 views

Difference between Clustered Columnstore Index Table in Azure SQL and Table in Azure Data Warehouse

What is the essential difference between a Clustered Columnstore Index table in Regular Azure SQL, and a table in Azure Data Warehouse? They both have columnar storage, no foreign keys, no primary ...
2
votes
2answers
3k views

Can a table have Both a Clustered Columnstore Index and Nonclustered Columnstore Index?

Can I a table have both Clustered Columnstore Index and Nonclustered Columnstore Index? I am seeing example strategies to use Clustered Columnstore Indexes with only Nonclustered Binary row indexes. ...
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, ...
2
votes
2answers
295 views

Performance Implications of encrypted data in a Clustered Columnstore Index

I am using SQL Server 2016 and expecting some details on below points regarding performance point of view: Is there any performance gain or additional benefit on creating a primary key - unique ...
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 ...
3
votes
1answer
939 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 ...
1
vote
2answers
68 views

Will table partitions help me?

I have 10 tables. dbo.Table2008 dbo.Table2009 ... dbo.Table2018 Each table holds approximately 500,000,000 records which are 20 columns wide (if that matters). And each table has a 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 ...
1
vote
2answers
133 views

Why are column store databases the new thing now? [closed]

Column store databases have several advantages over traditional databases - most notably compression and faster seek. Column store seem to be the new fad for databases in the past few years, with HANA ...