Warunek filtru niepoprawnie zastosowany do indeksu klastrowanego magazynu kolumn

W poniższym przykładzie predykaty są takie same, jednak górna instrukcja (poprawnie) zwraca 0 wierszy, dolna instrukcja zwraca 1 - nawet jeśli predykaty NIE są zgodne:

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

Dlaczego tak się dzieje?

Więcej informacji:

• Indeks nieklastrowany w górnej instrukcji NIE jest filtrowany
• CheckDB zwraca 0 problemów
• Wersja serwera: Microsoft SQL Azure (RTM) - 12.0.2000.8 Dec 19 2018 08:43:17 Copyright (C) 2018 Microsoft Corporation

Wklej link do planu:

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

Dalsze informacje:

Uruchomiono, dbcc checktable ([transaction]) with all_errormsgs, extended_logical_checks, data_purityco wskazuje na brak problemów.

Mogę niezawodnie odtworzyć problem z tą tabelą podczas przywracania kopii zapasowej tej bazy danych.

Ten błąd nie wymaga usuwania ani zmiany nazw kolumn.

Zobaczysz również to samo zachowanie, statusId = 100którego nigdy nie było w żadnej wersji kolumny.

Wymagania

• Klastrowy magazyn kolumn
• Indeks nieklastrowany b-drzewa
• Plan, który wykonuje wyszukiwanie w magazynie kolumn za pomocą
  • Docelowe wiersze w magazynie delta
  • Wypchnięty predykat spoza SARG
  • Porównanie z NULL przy użyciu testu równości

Przykład

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;

Każda z poniższych sytuacji pozwoli uniknąć błędu:

• Przenoszenie wierszy ze sklepu delta przy użyciu dowolnej metody, w tym reorganizacja z określoną opcją kompresowania rowgroups
• Pisanie predykatu, aby jawnie odrzucić = NULL
• Włączenie nieudokumentowanej flagi śledzenia 9130, aby uniknąć wypychania predykatu do wyszukiwania

db <> demo skrzypiec .

Ten błąd został naprawiony w CU15 dla SQL Server 2017 (i CU7 dla SQL Server 2016 SP2):

Poprawka: Kwerenda względem tabeli z zarówno klastrowanym indeksem magazynu kolumn, jak i nieklastrowanym indeksem magazynu danych może zwracać niepoprawne wyniki w SQL Server 2016 i 2017

To jest błąd w SQL Server. Jeśli kolumna zostanie usunięta z tabeli z klastrowanym indeksem magazynu kolumn, a następnie zostanie dodana nowa kolumna o tej samej nazwie, prawdopodobnie używa ona starej, usuniętej kolumny dla predykatu. Oto MVCE:

Ten skrypt zaczyna się od 10000wierszy z statusIdof 1i statusId2of 5- następnie upuszcza statusIDkolumnę i zmienia nazwę statusId2na statusId. Na koniec wszystkie wiersze powinny mieć statusIdpo 5.

Ale następujące zapytanie trafia w indeks nieklastrowany ...

select *
from example
where statusId = 1
    and total <= @filter
    and barcode = @barcode
    and id2 = @id2

... i zwraca 2wiersze (z wybranymi statusIdinnymi niż sugerowane przez WHEREklauzulę) ...

+-------+---------+------+-------+----------+
|  id   | barcode | id2  | total | statusId |
+-------+---------+------+-------+----------+
|     5 |    5    | NULL |  5.00 |        5 |
| 10005 |    5    | NULL |  5.00 |        5 |
+-------+---------+------+-------+----------+

... podczas gdy ten uzyskuje dostęp do magazynu kolumn i poprawnie zwraca 0

select count(*) 
from example 
where statusId = 1

MVCE

/*Create table with clustered columnstore and non clustered rowstore*/
CREATE TABLE example
(
id        INT IDENTITY(1, 1),
barcode   CHAR(22),
id2       INT,
total     DECIMAL(10,2),
statusId  TINYINT,
statusId2 TINYINT,
INDEX cci_example CLUSTERED COLUMNSTORE,
INDEX ix_example (barcode, total)
);

/* Insert 10000 rows all with (statusId,statusId2) = (1,5) */
INSERT example
       (barcode,
        id2,
        total,
        statusId,
        statusId2)
SELECT TOP (10000) barcode = row_number() OVER (ORDER BY @@spid),
                   id2 = NULL,
                   total = row_number() OVER (ORDER BY @@spid),
                   statusId = 1,
                   statusId2 = 5
FROM   sys.all_columns c1, sys.all_columns c2;

ALTER TABLE example
  DROP COLUMN statusid
/* Now have 10000 rows with statusId2 = 5 */


EXEC sys.sp_rename
  @objname = N'dbo.example.statusId2',
  @newname = 'statusId',
  @objtype = 'COLUMN';
/* Now have 10000 rows with StatusID = 5 */

INSERT example
       (barcode,
        id2,
        total,
        statusId)
SELECT TOP (10000) barcode = row_number() OVER (ORDER BY @@spid),
                   id2 = NULL,
                   total = row_number() OVER (ORDER BY @@spid),
                   statusId = 5
FROM   sys.all_columns c1, sys.all_columns c2;
/* Now have 20000 rows with StatusID = 5 */


DECLARE @filter  DECIMAL = 5,
        @barcode CHAR(22) = '5',
        @id2     INT = NULL; 

/*This returns 2 rows from the NCI*/
SELECT *
FROM   example WITH (INDEX = ix_example)
WHERE  statusId = 1
       AND total <= @filter
       AND barcode = @barcode
       AND id2 = @id2;

/*This counts 0 rows from the Columnstore*/
SELECT COUNT(*)
FROM   example
WHERE  statusId = 1;

Mam również podniesiony problem na Azure portalu sprzężenia zwrotnego :

I dla każdego, kto się z tym spotka, przebudowanie Indeks klastrowanego magazynu kolumn rozwiązuje problem:

alter index cci_example on example rebuild

Przebudowa CCI naprawia tylko wszelkie istniejące dane. Jeśli zostaną dodane nowe rekordy, problem pojawi się ponownie w tych rekordach; więc obecnie jedyną znaną poprawką dla tabeli jest jej całkowite odtworzenie.

Na podstawie planów wydaje się, że indeks magazynu kolumn został utworzony przy wyłączonym zestawie ANSI_NULLS. Tabele i indeksy zachowują ustawienie z okresu, w którym indeks został utworzony. Możesz to sprawdzić, tworząc duplikat indeksu magazynu kolumn, upewniając się, że ANSI_NULLS jest WŁĄCZONY, a następnie upuszczając oryginał lub wyłączając go.

Ale jeśli nie odkryłeś błędu programu SQL Server, jest to jedyny sposób, aby wyniki mogły się zdarzyć.