wtorek, 9 września 2014

Indeks klastrowy - prześwietlenie cz 2

W poprzednim wpisie pokazałem w jaki sposób obecność dodatkowych indeksów i predykatów użytych w zapytaniu może wpłynąć na kolejność rekordów zwróconych z tabeli na której istnieje indeks klastrowy.
Wspomniałem także, że skan indeksu klastrowego jest mniej optymalnym rozwiązaniem, niż seek na innym indeksie z racji tego, że indeks klastorowy przeważnie wymaga wiecej operacji IO. Spowodowane jest to tym, że indeks ten zawiera po prostu wszystkie dane na poziomie zerowym indeksu.
I co najważniejsze jedynym sposobem by dane zwrócone zostały w oczekiwanej kolejności jest użycie klauzuli ORDER BY w zapytaniu.
Jednakże nie tylko obecność dodatkowego indeksu może powodować, że odpytywanie tabeli zawierającej indeks klastrowy zwróci rekordy w innej kolejności.
Poniższy skrypt tworzy sekwencje, tabele oraz unikalny indeks klastrowy gdzie duplikaty są ignorowane.
--Create Sequence

CREATE SEQUENCE dbo.RecordNo

    START WITH 1

    INCREMENT BY 1 ;

GO

 

--Create table

CREATE TABLE ClusteredTableScan (

    Id INT NOT NULL,

    InsertSeqNo INT NOT NULL DEFAULT(NEXT VALUE FOR dbo.RecordNo),

    AllocationSeqNo INT NULL,

    Filler CHAR(781) NULL

);

GO

 

--Clustered index with duplicates being ignored

CREATE UNIQUE CLUSTERED INDEX IDX_CTS ON ClusteredTableScan (Id) WITH (IGNORE_DUP_KEY = ON, SORT_IN_TEMPDB = ON);

GO

Kolejny snippet wypełnia tabele danymi. 1000 rekordów, wartości kolumny ID 0-999 jednakże dodane w losowej kolejności. Obecność kolumny filler powoduje częste dzielenie stron i dużą fragmentację indeksu.


DECLARE @rowcount INT = 0;

 

--Populate table with high level of index fragmentation

--Values from 0=9

WHILE @rowcount < 10

BEGIN

    INSERT INTO ClusteredTableScan(Id) VALUES(ABS(CHECKSUM(NEWID()))%10);

    SET @rowcount += @@ROWCOUNT;

END

 

--Values from 10-99

WHILE @rowcount < 100

BEGIN

    INSERT INTO ClusteredTableScan(Id) VALUES(ABS(CHECKSUM(NEWID()))%100);

    SET @rowcount += @@ROWCOUNT;

END

 

--Values from 100-999

WHILE @rowcount < 1000

BEGIN

    INSERT INTO ClusteredTableScan(Id) VALUES(ABS(CHECKSUM(NEWID()))%1000);

    SET @rowcount += @@ROWCOUNT;

END

GO

Poniższy fragment kodu porządkuje kolumnę InsertSeqNo, tak by wartosci były od 1 do 1000 oraz ustawia wartość w kolumnie AllocationSeqNo.


--Remove gaps in the InsertSequenceNo columns and set the allocation order

;WITH CTE AS (

    SELECT

        InsertSeqNo,

        AllocationSeqNo,

        ROW_NUMBER() OVER(ORDER BY InsertSeqNo) AS NewInsertSeqNo,

        ROW_NUMBER() OVER(ORDER BY FILE_ID, page_id, slot_id) AS NewAllocationSeqNo

    from ClusteredTableScan

    CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

)

 

UPDATE CTE SET InsertSeqNo = NewInsertSeqNo, AllocationSeqNo = NewAllocationSeqNo;

GO

AllocationSeqNo jest to kolejności w jakiej fizycznie rekordy zapisane są na dysku.

W tym momencie tabela z danymi jest już przygotowana do testów.

Skanowanie indeksu. Kolejność wq klucza.


SELECT Id, InsertSeqNo, AllocationSeqNo FROM ClusteredTableScan



Id          InsertSeqNo AllocationSeqNo

----------- ----------- ---------------

0           5           1

1           3           2

2           9           3

3           6           4

4           7           5

5           1           12

6           2           13

7           4           14

8           8           15

9           10          16

10          51          17

...

999         869         370

image

Powyższe zapytanie zwraca rekordy w kolejności klucza indeksu. Kolumna InsertSeqNo, w której wartości są ustalane w momencie dodania rekordu nie pokrywa się z kolumną Id. Także kolejność w AllocationSeqNo nie pokrywa się z kluczem indeksu.


TABLOCK i NOLOCK. Skan indeksu. Kolejność wg allokacji.

Poniższe dwa zapytania pomimo tego, że używają tego samego planu zapytania co wcześniejsze zapytanie zwracają wyniki w innej kolejności.


SELECT Id, InsertSeqNo, AllocationSeqNo FROM ClusteredTableScan WITH(TABLOCK)

SELECT Id, InsertSeqNo, AllocationSeqNo FROM ClusteredTableScan WITH(NOLOCK)


W przypadku gdy blokowana jest cała tabela lub też nie ma żadnego blokowania SQL Server jako najbardziej optymalny sposób dostępu do danych wybiera kolejność w jakiej dane są fizycznie zapisane na dysku.


Id          InsertSeqNo AllocationSeqNo

----------- ----------- ---------------

0           5           1

1           3           2

2           9           3

3           6           4

4           7           5

55          11          6

56          38          7

57          68          8

58          24          9

59          82          10

...

619         252         1000


Zarówno blokowanie całej tabeli jak i nie blokowanie zasobów wiążą się z dodatkowymi konsekwencjami.

TABLOCK powoduje, że inne zapytania nie mogą modyfikować danych w tabeli w momencie kiedy bieżące zapytanie jest wykonywane. Skutkuje to tym, że silnik zapytań wie, że w między czasie z tabelą nic się nie stanie w związku z czym wybiera optymalną scieżkę i odczytuje dane w kolejności w jakiej są na dysku.

Inaczej sytuacja ma sie w przypadku NOLOCK. NOLOCK lub tez tryb READ UNCOMMITED daje informacje silnikowi zapytań, że spójność i powtarzalność danych nie jest istotna. Skutkuje to tym, ze silnik zapytań znowu może wybrać optymalny sposób dostępu do danych, czyli kolejność alokacji. O (nie)powtarzalności i NOLOCK pisałem jakiś czas temu tutaj.

NOLOCK i predykaty w zapytaniu.

Użycie NOLOCK nie gwarantuje jednak tego, że silnik zapytań zawsze użyje operatora scan i bedzie odczytywał rekordy w kolejności alokacji.


SELECT Id, InsertSeqNo, AllocationSeqNo FROM ClusteredTableScan WITH(NOLOCK) WHERE ID > -1


W powyższym zapytaniu występuje predykat na kluczu w indeksie. Z tego też powodu zostanie zastosowany operator seek, co powoduje, że dane będą w kolejności klucza indeksu.

image

Zastosowany predykat w powyższym zapytaniu ma jeszcze jedną cechę. Optymalizator wie, że może skorzystać ze statystyk efektywnie, czyli wykorzystując histogram.

Jednakże dodanie operatora algebraicznego lub też wykorzystanie funkcji na kolumnie klucza w predykacie powoduje, że histogram nie może być wykorzystany i cały indeks musi być skanowany.


SELECT Id, InsertSeqNo, AllocationSeqNo FROM ClusteredTableScan WITH(NOLOCK) WHERE ID + 0 > -1

SELECT Id, InsertSeqNo, AllocationSeqNo FROM ClusteredTableScan WITH(NOLOCK) WHERE ABS(ID) > -1 OPTION(MAXDOP 1)


image

Co ciekawe taki sam efekt można uzyskać także w przypadku predykatu, który powoduje, że histogram może być wykorzystany.


SELECT Id, InsertSeqNo, AllocationSeqNo FROM ClusteredTableScan WITH(NOLOCK INDEX=0) WHERE ID > -1


Wystarczy wymusić w zapytaniu index=0, czyli stertę.

Wynikiem ostatnich 3 zapytań będzie zbiór danych w kolejności alokacji na dysku.

Podsumowując, obecność indeksu klastrowego nie gwarantuje, że dane zostaną zwrócone w kolejności klucza indeksu. Stać się tak może nawet jak w zapytaniu nie użyjemy NOLOCK lub TABLOCK. TABLOCK może zostać użyty niejawnie w przypadku gdy silnik zapytań uzna takie rozwiązanie za najbardziej optymalne.

Tabela z danymi w tym teście charakteryzuje się dużą fragmentacją.