8

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 an IDENTITY column in the original table, which is my only column where every row is unique.

Should I leave this column out or include it? I have read that you want to include all rows of your table into the COLUMNSTORE INDEX but I have also read that the best candidates are columns with lots of non-unique rows.

Is this just a bad candidate for a COLUMNSTORE INDEX?

I am using SQL Server 2012, so it is a nonclustered columnstore. I am just exploring possible better ways to store this data. Updates are nonexistent, although new rows will periodically be added through an ELT process so I am assuming some work would be done there. Some folks mine this data and generate huge reports, lots of scanning of rows, brings the server to a crawl at times which has forced us to offload a copy daily to a secondary server.

  • 1
    Is the identity column on the original table also your clustered index? If so, SQL Server will automatically include that column in any non-clustered columnstore index, even if you don't explicitly ask for it. This is somewhat similar to the way that clustered index columns will be included in a non-clustered b-tree index, but the data will be stored as actual compressed columnstore segments in this case. See dba.stackexchange.com/questions/103722/… for more info. – Geoff Patterson Sep 2 '15 at 19:41
  • 137 million rows is big but manageable. Have you looked into partitioning the table and putting it on different filegroups ? Columnsstore index in sql 2012 is not writable, so you are going to run into problems - you have to drop and recreate it. I am not telling columnstore will is bad, but its better to explore other options as well. – Kin Shah Sep 2 '15 at 20:49
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 you with better compression than page typically does. Typically. Please test it before advancing to production.

Your biggest problem in SQL Server 2012 will be a very weak implementation of the Batch Mode, and there is nothing you can do about it.

3

I couldn't resist joining Niko with another answer (welcome, Niko!). In general, I agree with Niko that the batch mode limitations in SQL 2012 (if Niko won't link to his own blog, I will :)) may be a major concern. But if you can live with those and have full control over every query you are writing against the table to carefully vet it, columnstore could work for you in SQL 2012.

As far as your specific questions about the identity column, I found that the identity column compresses very well and would highly recommend including it in your columnstore index in any initial testing you. (Note that if the identity column happens to also be the clustered index of your b-tree, it will be automatically included in your non-clustered columnstore index.)

For reference, here are the sizes I observed for ~10MM rows of identity column data. The columnstore loaded for optimal segment elimination compresses to 26MB (vs. 113MB for PAGE compression of the rowstore table), and even the columnstore built on a randomly ordered b-tree is only 40MB. So this shows a huge compression benefit, even over the best b-tree compression SQL has to offer and even if you don't bother to align your data for optimal segment elimination (which you would do by first creating a b-tree and then building your columnstore with MAXDOP 1).

enter image description here

Here is the full script I used in case you'd like to play around:

-- Confirm SQL version
SELECT @@version
--Microsoft SQL Server 2012 - 11.0.5613.0 (X64) 
--  May  4 2015 19:05:02 
--  Copyright (c) Microsoft Corporation
--  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )


-- Create a columnstore table with identity column that is the primary key
-- This will yield 10 columnstore segments @ 1048576 rows each
SELECT i = IDENTITY(int, 1, 1), ROW_NUMBER() OVER (ORDER BY randGuid) as randCol
INTO #testIdentityCompression_sortedColumnstore
FROM (
    SELECT TOP 10485760 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS randI, NEWID() AS randGuid
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
    CROSS JOIN master..spt_values v3
) r
ORDER BY r.randI
GO
ALTER TABLE #testIdentityCompression_sortedColumnstore
ADD PRIMARY KEY (i)
GO
-- Load using a pre-ordered b-tree and one thread for optimal segment elimination
-- See http://www.nikoport.com/2014/04/16/clustered-columnstore-indexes-part-29-data-loading-for-better-segment-elimination/
CREATE NONCLUSTERED COLUMNSTORE INDEX cs_#testIdentityCompression_sortedColumnstore ON #testIdentityCompression_sortedColumnstore (i) WITH (MAXDOP = 1)
GO

-- Create another table with the same data, but randomly ordered
SELECT *
INTO #testIdentityCompression_randomOrderColumnstore
FROM #testIdentityCompression_sortedColumnstore
GO
ALTER TABLE #testIdentityCompression_randomOrderColumnstore
ADD UNIQUE CLUSTERED (randCol)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX cs_#testIdentityCompression_randomOrderColumnstore ON #testIdentityCompression_randomOrderColumnstore (i) WITH (MAXDOP = 1)
GO

-- Create a b-tree with the identity column data and no compression
-- Note that we copy over only the identity column since we'll be looking at the total size of the b-tree index
-- If anything, this gives an unfair "advantage" to the rowstore-page-compressed version since more
-- rows fit on a page and page compression rates should be better without the "randCol" column.
SELECT i
INTO #testIdentityCompression_uncompressedRowstore
FROM #testIdentityCompression_sortedColumnstore
GO
ALTER TABLE #testIdentityCompression_uncompressedRowstore
ADD PRIMARY KEY (i)
GO

-- Create a b-tree with the identity column and page compression
SELECT i
INTO #testIdentityCompression_compressedRowstore
FROM #testIdentityCompression_sortedColumnstore
GO
ALTER TABLE #testIdentityCompression_compressedRowstore
ADD PRIMARY KEY (i)
WITH (DATA_COMPRESSION = PAGE)
GO

-- Compare all the sizes!
SELECT OBJECT_NAME(p.object_id, 2) AS tableName, COUNT(*) AS num_segments, SUM(on_disk_size / (1024.*1024.)) as size_mb
FROM tempdb.sys.partitions p
JOIN tempdb.sys.column_store_segments s
    ON s.partition_id = p.partition_id
    AND s.column_id = 1
WHERE p.object_id IN (OBJECT_ID('tempdb..#testIdentityCompression_sortedColumnstore'),OBJECT_ID('tempdb..#testIdentityCompression_randomOrderColumnstore'))
GROUP BY p.object_id
UNION ALL
SELECT OBJECT_NAME(p.object_id, 2) AS tableName
    , NULL AS num_segments
    , (a.total_pages*8.0) / (1024.0) as size_mb
FROM tempdb.sys.partitions p
JOIN tempdb.sys.allocation_units a
    ON a.container_id = p.partition_id
WHERE p.object_id IN (OBJECT_ID('tempdb..#testIdentityCompression_compressedRowstore'),OBJECT_ID('tempdb..#testIdentityCompression_uncompressedRowstore'))
ORDER BY 3 ASC
GO
  • Thank you for all the great answers, right now I have decided to hold off until I can get on at least sql server 2014. we are stepping up our upgrades so I am hoping in the next year or so we can do this. – Don Sep 20 '15 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.