Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [columnstore]

The tag has no usage guidance.

3
votes
1answer
65 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
85 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 ...
1
vote
0answers
52 views

Help with Frequent Data Updates on Clustered Columnstore index [closed]

I’ve been asked to look at a SQL Server 2014 Enterprise VM with 6 cores MAXDOP set to 3, 256 GB Ram. The CPU is running very high but there isn’t enough activity going on to justify the CPU workload....
-2
votes
1answer
51 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 ...
0
votes
1answer
61 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
73 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 ...
1
vote
2answers
94 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. ...
10
votes
1answer
145 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
78 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
102 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 ...
2
votes
1answer
78 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
56 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 ...
5
votes
1answer
174 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 ...
0
votes
0answers
29 views

Is the modeling technique changing with column-oriented databases?

I've been working on Kimball's modeling techniques lately and he's giving directional guidance on which data belongs in a dimension and which in a fact table. But sometimes it's hard to define an ...
1
vote
2answers
56 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 ...
12
votes
2answers
206 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 ...
1
vote
1answer
31 views

select runs in batch mode, but CTAS runs in row mode

I have the same query that shows as running in batch mode when run as a select statement, but row mode when the same query is the select part of a CTAS operation. Both tables (plus the resulting table)...
3
votes
2answers
74 views

Can I artificially limit the Columnstore cache size in SQL Server?

I am trying to evaluate the potential performance of a data science workload on a large-ish dataset (~200GB). We have had excellent results using columnstore indexes in SQL Server 2017. However, the ...
0
votes
0answers
73 views

Using clustered columnstore indexes in conjunction with non-clustered columnstore indexes

Table A has approximately 500 million records in it and is of the form similar to the following | ID | FirstName | LastName | MiddleName | ------------------------------------------ | 1 | John |...
2
votes
1answer
298 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 ...
1
vote
2answers
233 views

How to install columnstore in MariaDB on Mac OS X with Homebrew

I would analyse time-series data using MariaDB ColumnStore but error encountered MariaDB [(none)]> CREATE DATABASE forex; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> USE forex; ...
0
votes
0answers
41 views

Difference between Column-Oriented and Document-Based data models

I am learning about column-oriented and document-based databases for a data engineering class. I am having trouble understanding the differences between the two. I know they are both based on ...
2
votes
2answers
894 views

Creating non-clustered index on large table fills transaction logs

Using SQL Server 2016, I'm trying to create a non-clustered covering index on a table in my database that is of the structure int, bigint, int, varchar(20), varchar(4000). The size is ~13,241,928 rows....
1
vote
0answers
29 views

What does column family-oriented datastore mean in practice

I was reading this article about HBase. Within it, the storage format of HBase is described: HBase is referred to as a column family-oriented data store. It’s also row-oriented: each row is indexed ...
1
vote
2answers
601 views

What to do when you need a cursor and you have a columnstore clustered index

I have a very large SQL Server table with a ColumnStore clustered index. The table is very fast to query, so this is good. However I need to do some updates to a large portion of records in the ...
0
votes
1answer
44 views

Columnstore Aggregate Pushdown doesn´t work

Recently I started investigating columnstore indexes for some warehouse-behaving part of our system. What I can´t achieve yet is to make aggregate pushdown work. I've checked data types and all ...
4
votes
2answers
136 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 ...
1
vote
0answers
51 views

Why are pages being removed from the buffer cache

I have an test environment with SQL Server 2016 SP1 installed. My machine has 32GB of RAM and I give 30GB to SQL Server. I have 2 databases in my server - BGD and, BGD_Mem. The second one contains ...
3
votes
0answers
148 views

Table design pattern that can support partitioning, clustered columnstore index, and spatial data?

I am designing a spatial database schema for SQL Server 2016 with a table that is going to hold a large amount of rows (50+ billions, increasing by 50+ millions per day). Requirements: Spatial ...
7
votes
2answers
275 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, ...
3
votes
1answer
149 views

Table partitioning and future values

I have a table with approximately 1.4 bn rows; currently just one partition - but I would like to split the data into multiple partitions. I haven't been using partitions before, so I am not quite ...
5
votes
1answer
210 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 (): ...
1
vote
1answer
119 views

Clustered columnstore indexes - Do I need to create a non clustered index on a column that's used in a join? [closed]

I have two very large tables that need to be joined to produce a data extract. Both tables have clustered column store indexes and are partitioned. DW Sentry advises me in the plan view that it ...
0
votes
1answer
296 views

What is an efficient storage method to store Graph Data from GraphX (Spark)?

I generate graph data using Spark's GraphX library and I need an efficient way to store graph data. I have access to Apache Cassandra and ideally I want to store the graph data in there but the issue ...
1
vote
1answer
1k views

Adding new columns to Table with Clustured Columnstore Index

I have a daily facts table 'FactsTable1' with 240 columns on which I am exploring 'Clustured Columnstore Index'(CCI). I want to know, What happens when I add another ~50 columns to the table? Will ...
1
vote
1answer
237 views

Columnstore index query performance based on number of columns in SELECT statement

I have a table 'FactsTable1' which is horizontally partitioned on basis of date & it also has clustered columnstore index on it. I am running following queries on it. Query 1 : SELECT SUM([...
1
vote
1answer
108 views

Why does my columnstore index not fill the entire rowgroup?

I'm using [vColumnstoreDensity] to monitor the health of my columnstore indexes. I've noticed that I've got one index that looks strange. As far as my understanding goes, the columnstore index will ...
18
votes
3answers
425 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....
3
votes
1answer
74 views

SQL Server columnstore metadata (pushdown) filter

AFAIK columnstore indexes store min/max values for segments (1 million rows), so this information can be used to prune not needed data. This feature seems to work with equality predicates, but not ...
4
votes
1answer
156 views

Why does CCI creation on an empty table request a huge memory grant?

On a QA server with very low memory, I experienced error 8545 when creating clustered columnstore indexes on new tables in SQL Server 2016: Error 8645: A time out occurred while waiting for memory ...
0
votes
1answer
64 views

Foreign keys without any checking or index

I have a problem on SQL Server that led me to the question of whether it is possible to define a foreign key that is not enforced, not backed by an index, and does not need the referenced columns to ...
6
votes
1answer
548 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, ...
1
vote
0answers
353 views

SQL Server 2016 partitioning with columnstore

Environment is SQL 2016/SP1/CU1 Enterprise on Windows 2012 R2 Standard, and I'm running tests on using paritioning in combination with clustered columnstore. For one large table (300 million rows), ...
5
votes
2answers
296 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 ...
1
vote
1answer
754 views

SQL Server 2016 ColumnStore Clustered Index - Query plan showing a scan, performance not great

I have a SQL 2016 table that looks as follows: I have a columnstore clustered index on the table and no other indexes. I'm executing the following query: Select * From dbo.PanelWorkflow Where ...
3
votes
1answer
191 views

Creating additional indexes on a table with a columnstore clustered index

According to this article, a table with a clustered columnstore index can have one or more additional non-clustered indexes. However, when I try to add a non-clustered index on a table with a CCI, I ...
5
votes
1answer
556 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 ...
2
votes
1answer
354 views

Does column store index reduce data space ? How this is possible?

I was testing for performance of one of my queries. I was trying different indexes. When I applied a column-store index on my table, the table size reduced by 70-80 %. How is this possible?
1
vote
1answer
356 views

Getting Max for partition column in clustered columnstore index

SQL 2014 Enterprise I have a datawarehouse table with ~ 800 million rows (total table size ~25 GB) this table is partitioned by the import_date column (on daily basis) it has a clustered columnstore ...
3
votes
2answers
202 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. ...