30

What exactly can run in batch mode as of SQL Server 2014? SQL Server 2014 adds the following to the original list of batch mode operators: Hash Outer join (including full join) Hash Semi Join Hash Anti Semi Join Union All (Concatenation only) Scalar hash aggregate (no group by) Batch Hash Table Build removed It seems that data can transition into batch ...


22

Is there any plausible benefit to the spool iterator in the first plan? This depends on what you regard as "plausible", but the answer according to the cost model is yes. Of course this is true, because the optimizer always chooses the cheapest plan it finds. The real question is why the cost model considers the plan with the spool so much cheaper than the ...


22

Columnstore Structure Columnstore data is physically stored in one or more segments (regular LOB allocation units) per column, and may also be partitioned in the usual way. Each segment contains roughly one million rows of highly-compressed values or value references (several compression techniques are available). A value reference links to an entry in ...


16

The "bookmark" is the columnstore index original locator (per "Pro SQL Server Internals" by Dmitri Korotkevitch). This is an 8-byte value, with the columnstore index's row_group_id in the first 4-bytes and an offset in the second 4-bytes. If you use DBCC PAGE to look at the non-clustered index, the 8-byte columnstore index original locator appears in the "...


15

In Management Studio for SQL 2014, the timeout for the designers is held in Tools > Options > Designers > Table and Database Designers with a default of 30 seconds. However, as the syntax is so simple for creating a clustered columnstore, as already suggested, you should script it rather than using the designer, eg CREATE CLUSTERED COLUMNSTORE INDEX ...


15

No, order does not matter. Each column is considered individually. From the SQL Server team (emphasis added): Typically you will want to put all the columns in your table into the columnstore index. It does not matter what order you list the columns because a columnstore index does not have a key like a B-tree index does. Internally, the data will be ...


14

There is a trick to building fast realtime analytics, regardless of the platform. I've done this with Microsoft Analysis Services, but you can use similar techniques with other platforms as well. The trick is to have a leading partition that can be populated with near-realtime data and a historical partition (or partitions) that are optimised for fast ...


14

A clustered columnstore index is fundamentally different from a clustered rowstore index. You may have noticed there is no key column specification for a clustered columnstore index. That's right: a clustered columnstore index is an index with no keys - all columns are 'included'. The most intuitive description I have heard for a clustered columnstore index ...


13

You've got lots of questions in here: Q: (The lack of foreign keys) confuses me a lot! It is a good practice (not mandatory) to have Fk's in the DWH for a variety of reasons (data integrity, relations visible for semantic layer, ....) A: Correct, it's normally a good practice to have foreign keys in a data warehouse. However, clustered columnstore indexes ...


13

How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records? There is one LOB physical read and 1,877,324 read-ahead reads. Read-ahead is still a physical read, just performed in advance (prefetching). The quote from Redgate is incorrect. How it is possible the total number of lob pages decreased from ...


12

One approach might be to use a #temp table for the values and also introduce a dummy equijoin column to allow for a hash join. For example: -- Create a #temp table with a dummy column to match the hash join -- and the actual column you want CREATE TABLE #values (dummy INT NOT NULL, Col0 CHAR(1) NOT NULL) INSERT INTO #values (dummy, Col0) VALUES (0, 'A'), ...


12

In many respects, this is expected behaviour. Any set of compression routines will have widely ranging performance depending on input data distribution. We expect to trade data loading speed for storage size and runtime querying performance. There is a definite limit to how detailed an answer you're going to get here, since VertiPaq is a proprietary ...


12

The problem occurs when DBCC tries to verify a deleted bitmap for a read-only columnstore table. Deleted bitmaps are stored on the same filegroup as the columnstore table. They track rows logically deleted from compressed row groups. As far as I can tell, everything is organized correctly in the internal system tables (on SQL Server 2017 CU3), and most of ...


11

Identity columns are not truly compressed in Columnstore Indexes in SQL Server 2012 or in SQL Server 2014. It will all truly depend on the workload that you are experiencing. If your workload will include the identity column, then you can very beautifully take advantage of Segment elimination. From the compression point of view - Columnstore will provide ...


9

This involves two steps. One is described above by @wBob, however you will not be able to create it via the UI, it will still time out. What you need to do is to click on the 'Script' icon and then run the generated script. Hope this saves you some time.


9

SQL Server 2012 only supports the creation of nonclustered columnstore indexes. Change your code to: CREATE NONCLUSTERED COLUMNSTORE INDEX cci_Simple ON [dbo].[Card] In SQL Server 2012 this will make your table read-only. You should probably look at adding all three columns of the table to the columnstore index but I'll leave that to your testing. Note ...


9

I can't say exactly why this behavior is occurring but I believe I've developed a good model of the behavior via brute force testing. The following conclusions only apply when loading data into a single column and with integers that are very well distributed. First I tried varying the number of rows inserted into the CCI using TOP. I used ID % 16000 for all ...


9

First, thank you for the information and the reproducing code/situation. I've taken this and filed an internal item, it's been assigned and will be looked at shortly. You can vote for work at filegroup to read_only prevents dbcc checkdb from running on the SQL Server feedback site. I'll update this answer with more information as it becomes available.


8

Beyond the Cost Threshold setting, SQL Server appears to treat parallelism differently for columnstore indexes depending on your SQL Server version (2012 vs 2014) and even the datatypes in your table. I'd start with Joe Chang's post benchmarking decimal vs float datatypes, and read the comments on that post as well. If you want to get exactly the right ...


8

I believe, that this has to do with the internal optimisations of the compression for the single column tables, and the magic number of the 64 KB occupied by the dictionary. Example: if you run with MOD 16600, the final result of the Row Group size will be 1.683 MB, while running MOD 17000 will give you a Row Group with the size of 2.001 MB. Now, take a ...


8

Microsoft has updated the documentation to say that float is not supported*. The documentation for aggregate pushdown was inaccurate in some places and it doesn't state all of the restrictions. In some testing that I did a week ago I also found that float types are not supported. In addition to that, numeric(10,0) is supported despite requiring nine bytes ...


8

I don't think that your question is applicable to any RDBMS that supports columnar storage. I'm writing my answer from the point of view of SQL Server and most of the reasons depend on implementation details specific to SQL Server. When does one still need dimension tables when using CCI? 1. The volume of changes to the dimension table makes updating the ...


8

This is a bug with SQL Server. If a column is deleted from a table with a clustered columnstore index, and then a new column is added with the same name, it appears to be using the old, deleted column for the predicate. Here is the MVCE: This script starts off with 10000 rows with statusId of 1 and statusId2 of 5 - then drops the statusID column and renames ...


8

No, this is not possible. ALTER TABLE... SWITCH is a metadata operation and both tables need to have identical schemas. There's no such thing as a partitioned table that has some columnstore partitions and some rowstore partitions. The best you can do is switch the rowstore partition out, build a clustered columnstore index on the switched out table, and ...


8

Since you're on SQL Server 2016, it's worth mentioning that there is at least one public bug fix for parallel deadlocks involving columnstore indexes: FIX: A deadlock occurs when you run a parallel query on a clustered columnstore index in SQL Server 2016 and 2017 (thanks to Denis Rubashkin for providing the link initially) This was released as part of ...


7

If you can post the specific data and queries you are using, that is probably the only way we can help answer the question in the context of your specific case. You can use a script that generates anonymous data in roughly the same scale as your real example. However, I went ahead and created a similar type of script myself. For the sake of simplicity, I ...


7

If you use Query Analyzer(QA) go to Tools -> Option -> Connection.Reset all the values using 'Reset to Default' Button.By default there is no time out on QA. Or, write your CREATE INDEX or ALTER TABLE statement in the query window and run it. Setting the time out in SQL Server to -1 will prevent time outs. It's possible that you are changing the time out ...


7

Benefits of partitioning a CCI: Query performance can be improved because a minimum level of rowgroup elimination is guaranteed, despite how the data gets loaded or modified. Most of the generic SQL Server partitioning guidance out there doesn't take this into account. Improved flexibility with maintenance operations in that you can do rebuilds at the ...


7

This bug does not require dropping or renaming columns. You will also see the same behaviour for statusId = 100 which was never present in any version of the column. Requirements A clustered columnstore Nonclustered b-tree index A plan that performs a lookup on the columnstore with Target row(s) in delta store A pushed non-SARG predicate A comparison ...


6

I can understand that you feel some pieces that you are used to are missing. But that is only because they are missing. Nonetheless, SQL Server was being successfully used when Foreign Keys were just a concept (which we implemented through triggers in those days), not a physical implementation such as a constraint. Declarative Referential Integrity was ...


Only top voted, non community-wiki answers of a minimum length are eligible