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 am using fewer than 225 columns. But I am using the same number of rows and random data (which is unfavorable for columnstore) and I saw results that are much different than yours. So my initial thought is that yes, you do have some sort of problem with either your configuration or your test queries.
A few of the key takeaways:
- Columnstore has dramatically faster performance than rowstore for simple aggregations across all rows in a column
- If loaded carefully, columnstore can perform surprisingly well for singleton seeks. There is an I/O hit, but with a warm cache performance was very good. But not as good as rowstore for this use case, of course.
- If you need to be able to perform both singleton seeks and large aggregation queries, you might consider using a non-clustered columnstore index on top of a standard b-tree table.
- You mention that you have 225 columns, but an average row is just 181 bytes. This seems a little unusual; is your table mostly
BIT
columns? That might be something to look into further. I did see very good compression ratios on a simple BIT
column columnstore (over 99%), but it may be the case that much of that is due to the absence of row overhead and this advantage would disappear with many BIT
columns on a single row.
- If you want to learn (a lot) more about columnstore, Niko's 66-part (and counting) blog series has been the most valuable reference that I've come across.
And now on to the details:
Create rowstore data set
Nothing too exciting here; we create 40MM rows of pseudo-random data.
SELECT @@VERSION
--Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
-- Jun 9 2015 12:06:16
-- Copyright (c) Microsoft Corporation
-- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
GO
-- Create a rowstore table with 40MM rows of pseudorandom data
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b)
, E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b)
, E8(N) AS (SELECT 1 FROM E4 a CROSS JOIN E4 b)
SELECT TOP 40000000 ISNULL(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0) AS id
, ISNULL((ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 5) + 1, 0) AS col1
, ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col2
, ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col3
, ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col4
, ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col5
INTO dbo.test_row
FROM E8
GO
ALTER TABLE test_row
ADD CONSTRAINT PK_test_row PRIMARY KEY (id)
GO
Create columnstore data set
Let's create the same data set as a CLUSTERED COLUMNSTORE
, using the techniques described to load data for better segment elimination on Niko's blog.
-- Create a columnstore table with the same 40MM rows
-- The data is first ordered by id and then a single thread
-- use to build the columnstore for optimal segment elimination
SELECT *
INTO dbo.test_column
FROM dbo.test_row
GO
CREATE CLUSTERED INDEX cs_test_column
ON dbo.test_column (id)
GO
CREATE CLUSTERED COLUMNSTORE INDEX cs_test_column
ON dbo.test_column WITH (DROP_EXISTING = ON, MAXDOP = 1)
GO
Size comparison
Because we are loading random data, columnstore achieves only a modest reduction in table size. If the data was not as random, the columnstore compression would dramatically decrease the size of the columnstore index. This particular test case is actually quite unfavorable for columnstore, but it's still nice to see that we get a little bit of compression.
-- Check the sizes of the two tables
SELECT t.name, ps.row_count, (ps.reserved_page_count*8.0) / (1024.0) AS sizeMb
FROM sys.tables t WITH (NOLOCK)
JOIN sys.dm_db_partition_stats ps WITH (NOLOCK)
ON ps.object_id = t.object_id
WHERE t.name IN ('test_row','test_column')
--name row_count sizeMb
--test_row 40000000 2060.6328125
--test_column 40000000 1352.2734375
GO
Performance comparison
In the following two test cases, I try two very different use cases.
The first is the singleton seek mentioned in your question. As commenters point out, this is not at all the use case for columnstore. Because an entire segment has to be read for each column, we see a much greater number of reads and slower performance from a cold cache (0ms
rowstore vs. 273ms
columnstore). However, columnstore is down to 2ms
with a warm cache; that's actually quite an impressive result given that there is no b-tree to seek into!
In the second test, we compute an aggregate for two columns across all rows. This is more along the lines of what columnstore is designed for, and we can see that columnstore has fewer reads (due to compression and not needing to access all columns) and dramatically faster performance (primarily due to batch mode execution). From a cold cache, columnstore executes in 4s
vs 15s
for rowstore. With a warm cache, the difference is a full order of magnitude at 282ms
vs 2.8s
.
SET STATISTICS TIME, IO ON
GO
-- Clear cache; don't do this in production!
-- I ran this statement between each set of trials to get a fresh read
--CHECKPOINT
--DBCC DROPCLEANBUFFERS
GO
-- Trial 1: CPU time = 0 ms, elapsed time = 0 ms.
-- logical reads 4, physical reads 4, read-ahead reads 0
-- Trial 2: CPU time = 0 ms, elapsed time = 0 ms
-- logical reads 4, physical reads 0, read-ahead reads 0
SELECT *
FROM dbo.test_row
WHERE id = 12345678
GO 2
-- Trial 1: CPU time = 15 ms, elapsed time = 273 ms..
-- lob logical reads 9101, lob physical reads 1, lob read-ahead reads 25756
-- Trial 2: CPU time = 0 ms, elapsed time = 2 ms.
-- lob logical reads 9101, lob physical reads 0, lob read-ahead reads 0
SELECT *
FROM dbo.test_column
WHERE id = 12345678
GO 2
-- Trial 1: CPU time = 8441 ms, elapsed time = 14985 ms.
-- logical reads 264733, physical reads 3, read-ahead reads 263720
-- Trial 2: CPU time = 9733 ms, elapsed time = 2776 ms.
-- logical reads 264883, physical reads 0, read-ahead reads 0
SELECT AVG(id), SUM(col3)
FROM dbo.test_row
GO 2
-- Trial 1: CPU time = 1233 ms, elapsed time = 3992 ms.
-- lob logical reads 207778, lob physical reads 1, lob read-ahead reads 341196
-- Trial 2: CPU time = 1030 ms, elapsed time = 282 ms.
-- lob logical reads 207778, lob physical reads 0, lob read-ahead reads 0
SELECT AVG(id), SUM(col3)
FROM dbo.test_column
GO 2
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 "uniquifier" column of the DBCC PAGE
output. This shows that a unique non-clustered index does not need to include the columnstore row locator, whereas a non-unique non-clustered index does.
The following code creates a columnstore-organized table with a unique and non-unique b-tree nonclustered index on the same column:
CREATE TABLE dbo.Heapish
(
c1 bigint NOT NULL,
c2 bigint NOT NULL,
INDEX CCI_dbo_Heapish CLUSTERED COLUMNSTORE
);
GO
INSERT dbo.Heapish WITH (TABLOCKX)
(c1, c2)
SELECT TOP (1024 * 1024 * 8)
c1 = ROW_NUMBER() OVER
(ORDER BY C1.[object_id], C1.column_id),
c2 = ROW_NUMBER() OVER
(ORDER BY C1.[object_id], C1.column_id)
FROM master.sys.columns AS C1
CROSS JOIN master.sys.columns AS C2
ORDER BY
c1
OPTION (MAXDOP 1);
GO
CREATE UNIQUE NONCLUSTERED INDEX UNIQUE_c2 ON dbo.Heapish (c2) WITH (MAXDOP = 1);
CREATE NONCLUSTERED INDEX NONUNIQUE_c2 ON dbo.Heapish (c2) WITH (MAXDOP = 1);
We can see the size of the index row at different levels of the b-tree using sys.dm_db_index_physical_stats
:
SELECT
DDIPS.index_level,
DDIPS.page_count,
DDIPS.record_count,
DDIPS.min_record_size_in_bytes,
DDIPS.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID(N'dbo.Heapish', N'U'),
INDEXPROPERTY(OBJECT_ID(N'dbo.Heapish', N'U'), N'UNIQUE_c2', 'IndexID'),
NULL, 'DETAILED'
) AS DDIPS;
SELECT
DDIPS.index_level,
DDIPS.page_count,
DDIPS.record_count,
DDIPS.min_record_size_in_bytes,
DDIPS.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID(N'dbo.Heapish', N'U'),
INDEXPROPERTY(OBJECT_ID(N'dbo.Heapish', N'U'), N'NONUNIQUE_c2', 'IndexID'),
NULL, 'DETAILED'
) AS DDIPS;
The output is:
Both structures have the same row size at the leaf level, but the nonunique nonclustered index is 12 bytes larger than the unique nonclustered index at the non-leaf levels due to the 8-byte columnstore locator, plus 4 bytes of overhead for the first variable-length column in a row (uniquifier is variable length).
Best Answer
This isn't directly supported for nonclustered columnstore indexes.
It does work for clustered columnstore.
Azure Synapse Analytics has language support for doing it in one step e.g.:
This syntax has not yet made it to the SQL Server box product, though it is available under an undocumented feature flag so perhaps it isn't far away. It still won't work on a nonclustered columnstore index though.
General Workaround
The best you can do is to create the nonclustered rowstore index with
MAXDOP = 1
, then replace it with a nonclustered columnstore index withMAXDOP = 1
andDROP_EXISTING = ON
.This isn't guaranteed to preserve the ordering as you want, but it is highly likely:
This will give you your best chance of achieving rowgroup elimination when filtering on
PropertyId
.Special Case
When the desired ordering matches the rowstore clustered index (as appears to be the case in the question), there is no need to create a rowstore nonclustered index first. The documentation says:
So, in your case, it should be enough to run only:
See this db<>fiddle demo.
Metadata
You can see the min and max values for each rowgroup and column using: