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.
159
questions
0
votes
1
answer
60
views
Reducing Fragmentation on a clustered columstore index
I have a database with a clustered columnstore index on it. The table has 1,685,123,846 records in it.
I use the ola hallengre script monthly to rebuild the index, but it takes so long to run before ...
- 101
0
votes
1
answer
72
views
Best solution for storing metadata and data from documents?
First of all do I want to mention that I'm fairly new to databases and big data.
Background:
I'm working for a large company who wished to unify all their documentation.
At it's core of my project am ...
- 101
0
votes
1
answer
61
views
Why Are Many Open Delta Rowgroups Created On A SQL Server Columnstore Table When Simultaneously Trickle Feeding Rows And Running Select Queries?
SQL Server 2022 Express (16.0.1000.6) on Windows Server 2016 Standard.
I have a table with two indexes.
An ordered columnstore index (ordered by a smalldatetime column, and then an int column).
A ...
- 109
-1
votes
1
answer
51
views
Nonclustered columstore Index memory allocation
We are planning to use a non-clustered columnstore index on 30 nvarchar(100) columns. The table can have rows ranging from a few hundreds to millions. These columns are selected during query execution ...
- 1
1
vote
1
answer
71
views
Should I use a single column with many dimensions with columnar data?
I'm using Apache Iceberg which performs queries on Parquet files. The data saves a lot of types of events and their respective numeric values. I'm hesitating whether it's more efficient to have one ...
- 113
0
votes
0
answers
91
views
Nonclustered Columnstore Tuple Mover Merge Threshold
I'm learning about columnstore indexes in SQL Server and do some testing to better understand it. For the tests I'm using the StackOverlfow2013 database on SQL Server 2022.
I've created a copy of the ...
- 1,994
8
votes
1
answer
462
views
Why are statistics slower to build on clustered columnstore?
Building statistics on a clustered columnstore table always seems to read the whole table, even if I ask for a small sample. Why is this?
- 82.9k
1
vote
1
answer
261
views
Deadlock with Reorganize Index
I was running alter index reorganize on some columnstore indexes in SQL Server 2016 and got a deadlock message.
Will reorganize always be the victim? Because I do not want to cause other jobs to fail ...
- 785
0
votes
1
answer
31
views
Synapse column store datetime encoding
I have a table in an Azure Synapse database. This table has a clustered columnstore index. It has a datetime column. The minimum value in this column is 2001-03-27 00:00:00 and the maximum is 2022-12-...
- 24.7k
4
votes
3
answers
608
views
Trim_reason = DICTIONARY_SIZE on Clustered ColumnStore index
On a partitioned table with 17 billion rows, I'm getting a very high number of row_groups.
I've heard that I should try to get 1.048.576 rows in each rowgroup.
How do I achieve that?
The trim_reason ...
- 2,173
0
votes
1
answer
18
views
How to make the first match in a group a different group
I need to group by a certain column and to make the first occurrence into another group.
select * from table;
id
currency
amount
1
USD
1
2
USD
2
3
EUR
3
4
IL
4
5
EUR
5
6
USD
6
So something ...
- 513
26
votes
1
answer
2k
views
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
I found again an issue with SQL Server and MERGE statement and need some confirmation.
I can reproduce my issue constantly on a Azure Database (but not on a on premise SQL Server 2017/2019).
Please ...
- 363
0
votes
0
answers
64
views
Setting up an HTAP Environment
I've had success in the past with setting up a CIX/PK on an identity column and then setting up supporting NCIX and NCCsIX on tables to help support an HTAP environment.
I'm curious, going forward ...
7
votes
1
answer
1k
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 ...
4
votes
1
answer
370
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 (...
- 143
4
votes
1
answer
440
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 ...
- 879
0
votes
0
answers
62
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 ...
- 265
0
votes
1
answer
104
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?
- 1
0
votes
0
answers
302
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 ...
- 101
3
votes
2
answers
557
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 ...
- 865
0
votes
0
answers
539
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 ...
- 1,931
0
votes
1
answer
345
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 ...
- 5,472
0
votes
1
answer
95
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 \...
- 51
0
votes
1
answer
206
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 ...
- 11
0
votes
2
answers
127
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 ...
- 101
0
votes
0
answers
93
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 ...
- 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 ...
- 5,472
0
votes
0
answers
266
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 ...
- 265
0
votes
2
answers
363
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 |...
- 25
1
vote
1
answer
724
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 ...
- 13
1
vote
1
answer
660
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 ...
- 111
0
votes
1
answer
89
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 | ...
- 25
0
votes
1
answer
202
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 ...
- 103
2
votes
2
answers
726
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] ...
- 121
5
votes
2
answers
545
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 ...
- 3,770
1
vote
1
answer
675
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 ...
- 231
2
votes
2
answers
972
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 ...
- 161
5
votes
2
answers
969
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 ...
- 265
1
vote
1
answer
152
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:
...
- 1,253
0
votes
1
answer
85
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 ...
- 103
0
votes
2
answers
298
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 ...
- 21
1
vote
1
answer
381
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,...
- 265
3
votes
1
answer
423
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 (...
- 133
10
votes
2
answers
3k
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,221
1
vote
1
answer
606
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(...
- 1,221
5
votes
1
answer
1k
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 ...
- 35.1k
0
votes
1
answer
292
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
1
answer
508
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 ...
- 35.1k
5
votes
1
answer
467
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 ...
- 2,066
1
vote
1
answer
138
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 ...
- 113