Filter Condition Not Applied to Clustered Columnstore Index in SQL Server

azure-sql-databasecolumnstoresql server

Using the below example, the predicates are the same, however the top statement (correctly) returns 0 rows, the bottom statement returns 1 – even though the predicates do NOT match:

declare @barcode nchar(22)=N'RECB012ZUKI449M1VBJZ'  
declare @tableId int = null
declare @total decimal(10, 2) = 5.17

SELECT 1
FROM
    [dbo].[transaction] WITH (INDEX([IX_Transaction_TransactionID_PaymentStatus_DeviceID_DateTime_All]))
WHERE
    Barcode = @barcode
    AND StatusID = 1
    AND TableID = @tableID
    AND @total <= Total

SELECT 1
FROM
    [dbo].[transaction] 
WHERE
    Barcode = @barcode
    AND StatusID = 1
    AND TableID = @tableID
    AND @total <= Total

Why could this be happening?

Further info:

The Non Clustered Index in the top statement is NOT filtered
CheckDB returns 0 issues
Server Version: Microsoft SQL Azure (RTM) - 12.0.2000.8 Dec 19 2018 08:43:17 Copyright (C) 2018 Microsoft Corporation

Paste the Plan link:

https://www.brentozar.com/pastetheplan/?id=S1w_rU68E

Further Info:

Have ran dbcc checktable ([transaction]) with all_errormsgs, extended_logical_checks, data_purity which indicates no issues.

I can reliably reproduce the issue against this table when restoring a backup of this database.

Best Answer

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 with NULL using an equality test

Example

DROP TABLE IF EXISTS dbo.Example;
GO
CREATE TABLE dbo.Example
(
    c1 integer NOT NULL,
    c2 integer NULL,

    INDEX CCS CLUSTERED COLUMNSTORE,
    INDEX IX NONCLUSTERED (c1)
);
GO
INSERT dbo.Example
    (c1, c2)
VALUES
    (1, NULL);
GO
DECLARE @c2 integer = NULL;

-- Returns one row but should not
SELECT
    E.* 
FROM dbo.Example AS E 
    WITH (INDEX(IX))
WHERE
    E.c2 = @c2;

Any of the following will avoid the bug:

Moving rows out of the delta store using any method including reorganizing with the compress rowgroups option specified
Writing the predicate to explicitly reject = NULL
Enabling undocumented trace flag 9130 to avoid pushing the predicate into the lookup

db<>fiddle demo.

This bug was fixed in CU15 for SQL Server 2017 (and CU7 for SQL Server 2016 SP2):

FIX: Query against table with both clustered columnstore index and nonclustered rowstore index may return incorrect results in SQL Server 2016 and 2017

Related Solutions

Sql-server – Clustered columnstore index performance SQL Server 2014

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

Sql-server – Error 35338: Clustered columnstore index is not supported

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 that columnstore indexes also require Enterprise Edition.