The tag has no usage guidance.

learn more… | top users | synonyms (1)

23
votes
1answer
310 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 ...
16
votes
1answer
2k 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) ...
14
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 ...
13
votes
2answers
2k 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 ...
11
votes
1answer
182 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) ) ...
8
votes
2answers
280 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 ...
7
votes
2answers
2k 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 ...
6
votes
2answers
507 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 ...
6
votes
1answer
353 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 ...
5
votes
3answers
547 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 ...
4
votes
1answer
215 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 ...
4
votes
0answers
59 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, .......
4
votes
0answers
109 views

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

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 ...
3
votes
1answer
146 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
225 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
671 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 ...
3
votes
2answers
420 views

Are indexes unnecessary for columnar database?

In a columnar database, are indexes unnecessary or useless?
2
votes
1answer
63 views

Building a ColumnStore index hogs system resources

I am experimenting with the columnstore and I am finding that while it is building the process hogs all the system resources. I have a table with 2.3 billion rows and while I build the columnstore on ...
2
votes
0answers
34 views

Is there impact on query execution as more partitions are added to a table?

We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical ...
2
votes
0answers
53 views

Column Store Indices versus Dimensional Tables

I have a Data Warehouse and want to think ahead one year when I may get a database update allowing me to use column store indices. My dimensional tables are mostly junk dimensions containing columns ...
2
votes
0answers
377 views

Best Practice to update columnstore indexed tables

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
1
vote
3answers
8k 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 responding....
1
vote
1answer
2k views

IBM DB2 - store large files

I want to store large files in a DB2 database and I need more information about which is the best technique to use as I am new in this IBM product (previous experience with T-SQL only). The situation:...
1
vote
1answer
89 views

Data structure design for supporting arbitrary number of columns in table or database [closed]

I am currently working on a sort-of a meta-modeler to build a free web service so that people can input data and run several models on that data. The task I am currently struggling is: user needs to ...
1
vote
1answer
187 views

Column store index on a large fact table

I have seen column store index and also tested it. I have no doubt admiring that it is lightning fast. But every tutorial and blogs I have considered, says about its limitation "table will become read ...
1
vote
1answer
429 views

what is the structure of a Mysql multi-column index? [closed]

For example: CREATE TABLE test( col_1 INT, col_2 INT, col_3 VARCHAR(11), col_4 VARCHAR(11) )ENGINE = INNODB; CREATE INDEX mul ON test(col_2,col_3,col_4); Sorry,my English is not good,I mean ...
1
vote
1answer
43 views

NC Columnstore and delete on batch

hope you can give me some pointers on this behavior I have a NC Columnstore and in the same batch I want to drop this column store and "delete" some information. But I get this error: DELETE ...
1
vote
0answers
44 views

Why not create ColumnStore index on every single table?

As ColumnStore index improves performance for data warehouse databases, and also since SQL 2104, it is updatable too, why not creating ColumnStore index on every single table? Edit: In SQL 2014 we ...
1
vote
0answers
54 views

Clustered Columnstore Index on Table with Foreign key

Using SQL Server 2014, I am trying to create a clustered columnstore index on a table which is referenced by a foreign key. This is the syntax I am using CREATE CLUSTERED COLUMNSTORE INDEX ...
1
vote
0answers
49 views

Failed to allocate reserved pages: FAIL_RESERVED_PAGE_ALLOCATION

Last night I received the error on one of my servers of: Failed to allocate reserved pages: FAIL_RESERVED_PAGE_ALLOCATION 2047 I know that this has already been discussed on here some but all ...
1
vote
0answers
33 views

Storage solution for realtime time series analytics

I'm designing a system which allows user to analyze their data over time. It is like a IoT database which stores a lot of information from different devices from the user. I don't think that a normal ...
1
vote
0answers
35 views

Does the order of columns in a non-clustered ColumnStore index matter? [duplicate]

I'm reading about creating a non-clustered ColumnStore index. I noticed that when I modify an already created non-clustered ColumnStore in SQL Server Management Studio I have an option to change the ...
0
votes
1answer
290 views

Msg 823, Level 24, State 2 for Clustered Column Store Index in SQL Server 2014

CREATE CLUSTERED COLUMNSTORE INDEX CCI_TempTable1 ON dbo.TempTable1 ON[PRIMARY] After firing this query I found the error. Msg 823, Level 24, State 2, Line 20 The operating system returned error 1(...
0
votes
3answers
770 views

How can we get the table and the column name of known data value?

I have a value (like 'IT department'), based on this I want to get the table and column name in which this value exists/present.
0
votes
1answer
304 views

Storing 5TB web server access log vs OLAP DB

We have over 5TB compressed web server log in a HDFS and we often analyse using Hadoop. It is painful to run map reduce on 5TB of data and most importantly, not many developer are familiar with it. ...
0
votes
1answer
60 views

Array as a column vs lookup table

Hi I have two tables that has one to many relationship. Table 1 Users: ------------- | id | name | ------------- | 01 | John | ------------- | 02 | Harry | -----------...
0
votes
1answer
42 views

DB with column store and parallel query execution?

I am looking for open-source alternatives to SQL Server Enterprise Edition for a mostly-read analytics workload. The main key features that I'm looking for are Column-oriented storage Parallel ...
0
votes
1answer
45 views

Columnar & Row Oriented DB

In a previous question, I discussed some challenges with a row-oriented table. I have two tables in this application. The first table is well-suited for row-oriented operation; the second is well-...
0
votes
1answer
30 views

Record scan speed, column orientated databases and SSDs

I'm learning about column orientated databases. I have just watched a YouTube video on column orientated databases by Arnaldo from MIT, who suggests an interesting motivation for column orientated ...
0
votes
0answers
83 views

Columnstore Index Scan Cardinality Estimates

In SQL Server 2012, I am seeing large cardinality estimate discrepancies for a columnstore index scan. It looks like statistics have been generated for the index, but I don't see any details in DBCC ...
0
votes
1answer
197 views

How to create column group in WiredTiger engine in mongodb 2.8?

MongoDB 2.8 supports WiredTiger strorage engine, and WiredTiger supports column grouping. How we can create a collection in mongodb 2.8 with grouped columns?
0
votes
0answers
85 views

Is it possible to set Buffer pool extension on table having clustered Column Store index in SQL Server 2014?

I created Clustered Column Store index on a table. I want to improve memory performance using buffer pool. Is it possible to set buffer pool on sql server, a table having clustered column Store index ...
-1
votes
1answer
189 views

Error 35338: Clustered columnstore index is not supported [closed]

On SQL Server 2012, I want to index a table with a columnstore index. But an error message apears: Msg 35338, Level 16, State 1, Line 1 Clustered columnstore index is not supported. My code ...