April 1, 2015 at 5:32 am
Comments posted to this topic are about the item Stairway to Columnstore Indexes Level 2: Columnstore Storage
April 29, 2015 at 6:25 am
For someone like me who knows nothing about this, the article is incredible. Thank you so much.
May 5, 2015 at 12:11 am
Thanks for the detailed explanation!
I am still missing something...
It my user whant to see a crosstab report of NetPrice Amount by Month by Product - how the Columnstore index helps?
It still will have to match Date with Product with NetPrice at the ROW level - so it can aggregate that in March 2015 the revenues from Chairs were 20,000$
I probably missed something...
Thanks,
Michael
May 7, 2015 at 3:01 am
mshparber (5/5/2015)
Thanks for the detailed explanation!I am still missing something...
It my user whant to see a crosstab report of NetPrice Amount by Month by Product - how the Columnstore index helps?
It still will have to match Date with Product with NetPrice at the ROW level - so it can aggregate that in March 2015 the revenues from Chairs were 20,000$
I probably missed something...
Thanks,
Michael
Hi Michael,
If you have a columnstore index on a large table, then the optimizer will automatically create an execution plan that uses the columnstore index. If you are not getting the performance improvements you were hoping for, there may be several causes.
1. The columnstore index is not used. That is possible if the query is such that, according to the estimated costing done by the optimizer, it will actually take more time than using any of the other indexes. That can be true (there are actually queries that perform better with a traditional index), or it can be a mis-estimate.
2. The columnstore index is used, but the plan uses row mode execution instead of batch mode execution. That means that the query probably gains some performance from the columstore index, but far less than when batch mode would be used. This is especially common on SQL Server 2012, where batch mode was very limited and a lot of query constructions would cause SQL Server to fall back into row mode. I will cover this in detail in one of the later levels of the stairway.
If you can post a repro script (CREATE TABLE statements, a few INSERT statements with sample data, CREATE INDEX statements, and the actual query that does not perform as expected), I can take a quick look and see if I can give more details. Please test your repro script on an empty test database with case sensitive collation and US-English language settings to ensure that I can simply copy, paste and execute it on my end. Also please understand that I can take a quick look, but cannot afford to take many hours out of my consulting work to assist individual readers of this article - so if you are going to post a 10,000-line monster query, I can only respond by sending you my rates.
April 24, 2017 at 8:21 am
any idea, if (and if yes then how) small datatypes as TINYINT (= 1 byte) could be compressed in columnstored indexes?
Its clear, that I will save space when I replace "this is a long string" with a simple tinyint (at least, when it occurse more than once), but when the column data type is already as short as possible, it could only work when you save "value 1 (which is the dictonary entry for the original tinyint CountryID 20) will used in rows 1-10, 12-50 and 90-200", but this would it make much harder to recombine rows to answer an SELECT *
God is real, unless declared integer.
April 24, 2017 at 9:46 am
t.franz - Monday, April 24, 2017 8:21 AMany idea, if (and if yes then how) small datatypes as TINYINT (= 1 byte) could be compressed in columnstored indexes?Its clear, that I will save space when I replace "this is a long string" with a simple tinyint (at least, when it occurse more than once), but when the column data type is already as short as possible, it could only work when you save "value 1 (which is the dictonary entry for the original tinyint CountryID 20) will used in rows 1-10, 12-50 and 90-200", but this would it make much harder to recombine rows to answer an SELECT *
Yes, tinyint data is also compressed. The average space gain is less than for other data types, but it can still be relevant.
The primary compression method for tinyint will be run-length encoding (RLE). So if the data in rows 1 to 20 reads 1 / 1 / 1 / 1 / 2 / 2 / 2 / 2 / 3 / 3 / 1 / 1 / 1 / 1 / 4 / 1 / 3 / 3 / 3 / 3, then this will be stored as 1 (4) / 2 (4) / 3 (2) / 1 (4) / 4 (1) / 1 (1) / 3 (4). The algorithm that builds the columnstore index includes a step (after dividing the table into rowgroups) where the rows within a rowgroup are sorted to optimize the benefit of RLE. The "optimal" sort order for this column would after RLE result in 1 (9) / 2 (4) / 3 (6) / 4 (1). However, the sort algorithm that is used for columnstore indexes has to look at ALL columns, not just a single one.
A secondary compression method for tinyint tries to reduce the size of each value to less than 1 full byte. Let's for example say that, within a specific rowgroup, all values in your tinyint columns are NULL or between 23 and 48. The algorithm will replace NULLs with a value not in the normal range (probably 22), and then subtract 22 from each value changing the range to 0 (for NULL) and 1 - 26 (for 23-48); this can then be encoded in 5 bits instead of 8 for the value + 1 for the NULL marker. The "magic value" to represent NULL, and the offset of 22, are stored only once, in the rowgroup metadata. (You can find it in the DMV sys.column_store_segments. This DMV is introduced in level 4, though I have not shown all columns; the null value (null_value) and the offset (base_id) are two of the columns I chose not to include).
April 24, 2017 at 10:02 am
Thank you very much for the quick answer. I always wondered, how exactly SQL Server does this stuff and your article (plus the answer) makes it a lot more clear for me.
God is real, unless declared integer.
May 6, 2017 at 11:18 am
I am sorry to get into this late. SQL Server 2016 became smarter with regards to Segment reading. Have a look at this below. I know you have already observed this but SQL Server 2014 and 2012 do not specify Segments read and those skipped but only dwell on Logical reads for you to determine if there are skipped Segments:
(1 row(s) affected)
Table 'FactOnlineSales'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 8468, lob physical reads 1, lob read-ahead reads 0.
Table 'FactOnlineSales'. Segment reads 9, segment skipped 6.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
May 6, 2017 at 11:29 am
INNOCENT GUMBO - Saturday, May 6, 2017 11:18 AMHi Hugo;I am sorry to get into this late. SQL Server 2016 became smarter with regards to Segment reading. Have a look at this below. I know you have already observed this but SQL Server 2014 and 2012 do not specify Segments read and those skipped but only dwell on Logical reads for you to determine if there are skipped Segments:
(1 row(s) affected)
Table 'FactOnlineSales'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 8468, lob physical reads 1, lob read-ahead reads 0.
Table 'FactOnlineSales'. Segment reads 9, segment skipped 6.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(1 row(s) affected)
Table 'FactOnlineSales'. Scan count 8, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 4282, lob physical reads 0, lob read-ahead reads 0.
Table 'FactOnlineSales'. Segment reads 5, segment skipped 10.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Yes, that is a 100% correct observation. Version over version things improved with the columnstore. This series (with the exception of the last level, which is now in final edit stage) focuses on 2012 and 2014 only, because that was the scope when we started (we added the last level later).
Being able to see rowgroups read and skipped from the STATISTICS IO was a huge step forward. (Although I am not happy that they used the term "segments" to show the number of rowgroups)
May 8, 2017 at 12:34 am
Another question.
If I understood it correct, it has to read all segments / pages for the city column, when I execute a SELECT COUNT(*) from customers WHERE city = 'New York' .
With a nonclustered index on the city column it would just start after New Windsor, read the few tousand entries of New York and stop at Newark.
But since - following to my understanding - the entries in the columnstore index are not ordered and segment elemination would not work (I guess in each rowgroup of > 1 mio people will be a few persons who lives in a town < New York and in a town > New York), it has to read all 500 mio city entries (instead of the only max. 8 mio real inhabitants in a nonclustered index).
Is the following assumation true? When I'm using Partitioning - each partition has its own global dictionary, since I can switch the partition in and out as I wish (and when I create a CCI on the staging table the SQL Server has no idea into which table I'll be switching it).
God is real, unless declared integer.
May 8, 2017 at 3:20 am
t.franz - Monday, May 8, 2017 12:34 AMAnother question.
If I understood it correct, it has to read all segments / pages for the city column, when I execute aSELECT COUNT(*) from customers WHERE city = 'New York'.
With a nonclustered index on the city column it would just start after New Windsor, read the few tousand entries of New York and stop at Newark.
But since - following to my understanding - the entries in the columnstore index are not ordered and segment elemination would not work (I guess in each rowgroup of > 1 mio people will be a few persons who lives in a town < New York and in a town > New York), it has to read all 500 mio city entries (instead of the only max. 8 mio real inhabitants in a nonclustered index).
That is (mostly) correct, but does not necessarily mean that the nonclustered index will be faster in this case.
On string columns, rowgroup elmiimination is simply not supported at all. My guess is that this is because the use of dictionaries changes order. "New York" might be entry 3876 (so the valye 3876 will be used to represent it); "New Jersey" could be 57623, etc. Using min_data_id and max_data_id to eliminate rowgroups could in theory be used for equaltiy (if New York is 3876, a rowgroup with min_data_id of 4782 can be skipped), but since there's no way to contol the order of values this would not likely have significant impact.
So yes, for your query on a columnstore index all segments for that single column are read, whereas a nonclustered rowstore index can navigate directly to the first New York entry and then scan until it passes the last one. However, these segments are still better compressed then a rowstore index (even with page compression), and you will also get the benefit of batch mode execution (on SQL2014 and up; SQL2012 will require a small change to the query to force batch mode).
For a large city as New York, I would not be surprised if the columnstore version is still faster. Replace it with some small village with only a handful of customers, and the rowstore index wins. Luckily, you can always combined nonclustered columnstore indexes with rowstore indexes (and starting with SQL2016 can also combine clustered columnstore indexes with rowstore indexes). Often the optimizer will pick the best index for you; if needed you can help it with hints or query rewrites.
Absolutely true. In fact, partition switching is by far the most popular method for data loads into a nonclustered columnstore index on SQL2012 and 2014. You load into a "normal" table, build the nonclustered columnstore index (making the table readonly), the swap it with a partition in the full table - "et voila", you just loaded new data in a table with a (readonly) nonclustered columnstore index without having to go through a full rebuild.
(On SQL2016 where the nonclustered columnstore index, and on any version with a clustered columnstore, this is not needed becuase the index doesn't make the table readony, but you can still choose to do it this way - e.g. because updating the columnstore index has too much overhead to accept for large data loads, or because you want more control over the order of rows before you build the columnstore index).
April 19, 2021 at 1:18 pm
Very good set of articles
One question regarding storage and indexes - hopefully it makes sense
Using the sample code.
If I have something like:
select product
, count(*), sum(SalesQuantity)
from dbo.FactOnlineSales
group by product
With this I'm loading data ordered by salesdate using the clustered index; drop and create clustered columnstore method so it's sorted by salesdate
How does this code work efficiently as it will still need to read in all the segments (assume all products in each rowgroup - which is pretty likely). Then order this by product and sum, even if I added a where to filter further
Also, how does it link the SalesOrderQuantity to the product?
Having read through the set, I cannot quite see this
Thanks
- Damian
April 19, 2021 at 2:17 pm
Thanks, Damian!
In your sample query, you have no WHERE clause. This means you need to read and process all data from the table. Rowgroup elimination (sometimes mistakenly called segment elimination), which can be somewhat controlled as you indicate by ordering data before building the columnstore index, can increase the performance of a query with a WHERE clause by skipping entire rowgroups if none of the data in that rowgroup can qualify. For a query without WHERE, this will not help.
You still benefit from the many other benefits of columnstores: you do not read any data from other columns in the FactOnlineSales table; the data you do read is compressed (so less I/O); and the query processes the data in batch mode. Plus, if you're on SQL Server 2016 or higher, this specific query would benefit from aggregate pushdown.
You can see what this query does by looking at its execution plan. I predict from left to right a SELECT, a Hash Match (Aggregate), and a Columnstore Index Scan operator. That last one reads only the two required columns, Product and SalesQuantity. These are stored "vertically", but they do follow the same order. So operators processing the data "know" that within a given rowgroup, Product #127 matches with SalesQuantity #127. The read data is then not ordered, but processed in whatever order it has by the Hash Match. Up to SQL Server 2014 this means that an in memory table is set up with an entry for each Product, and it stores a "total so far" of all SalesQuantity values for that product. As of SQL Server 2016, the same logic is used but a lot of the action is done inside of the Columnstore Index Scan operator which now only passes the final results to its parent operator, instead of passing all the data from each row individually.
I hope this helps!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply