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ą.

sobota, 16 sierpnia 2014

Indeks klastrowy - prześwietlenie cz 1

Tym postem chciałbym zapoczątkować całą serie dotyczącą indeksu klastrowego. Temat ten jest dość ciekawy oraz złożony i warto na niego spojrzeć od różnych stron.

Indeks klastrowy jest dość specyficzny w swojej naturze, jako że tylko jeden taki może być założony na tabeli. Wynika to z faktu, że liście tego indeksu zawierają dane i są tak naprawdę strukturą tabeli. Nie oznacza to jednak, że dane są fizycznie zapisane na dysku w kolejności klucza indeksu. SQL Server, może ale nie musi, zwrócić wyniku zapytania w kolejności klucza indeksu. Jest to jeden z mitów krążąych na jego temat.

Cykl wpisów poświęcony indeksowi klatrowemu rozpocznę właśnie od obalenia tej teorii.
Poniższy skrypt tworzy tabelę z dwoma kolumnami oraz wypełnią ją danymi. Pierwsza kolumna zawiera wartości od A-Z a druga od 1-26 w kolejności malejącej.
set nocount on
go
 
create table ClusteredIndexOrder (
    Col1 char(1) primary key clustered,
    Col2 int not null unique
)
 
go
 
insert into ClusteredIndexOrder (Col1, Col2)
select CHAR(ASCII('A') + number - 1) as col1, 27 - number as col2 from (
select distinct number from master.dbo.spt_values where number between 1 and 26
) numbers
go
Rezultatem jest taka oto struktura

Col1 Col2
---- -----
A    26
B    25
C    24
D    23
E    22
F    21
G    20
H    19
I    18
J    17
K    16
L    15
M    14
N    13
O    12
P    11
Q    10
R    9
S    8
T    7
U    6
V    5
W    4
X    3
Y    2
Z    1
 
Jeżeli prawdą byłoby, że dane są zwrócone w kolejności klucza indeksu klastrowego to poniższe zapytanie powinno zwrócić wynik jak powyżej.
 
select * from ClusteredIndexOrder
 
A co ono zwraca?
 
Col1 Col2
---- -----
Z    1
Y    2
X    3
W    4
V    5
U    6
T    7
S    8
R    9
Q    10
P    11
O    12
N    13
M    14
L    15
K    16
J    17
I    18
H    19
G    20
F    21
E    22
D    23
C    24
B    25
A    26
“Niespodziewanie” dane pojawiły się w odwrotnej kolejności.

Z pomocą rozwiązania zagadki przychodzi analiza planu wykonania.





Otóż to nie indeks klastrowy był w tym przypadku skanowany a unikalny założony na kolumnie Col2. Jako, że wszystkie kolumny klucza indeksu klastrowego znajdują się w liściach indeksu nieklastrowego i indeks ten w całości pokrywa to zapytanie optymalizator zapytań w pierwszej kolejności wybiera indeksy nieklastrowe. Przykład ten jest bardzo specyficzny. Większość tabel, która maja założony indeks klastrowy i dodatkowy indeks nieklastrowy będzie miała więcej kolumn. Indeks nieklastrowy będzie też zawierał mniej danych w związku z czym jeżeli takowy indeks pokrywa w całości zapytanie mniej operacji IO, wczytywania danych do pamięci i blokowania zasobów będzie wymagane.

Poniższe dwa zapytania zwrócą dane w kolejności klucza indeksu klastrowego.

select * from ClusteredIndexOrder where Col1 <> ''
select * from ClusteredIndexOrder with(index=1)

Plany zapytań w ich przypadku będą następujące






W pierwszym przypadku predykat na kolumnie indeksu klucza klastrowego wymusił operator seek natomiast w drugim przypadku scan był wywołany przez wymuszenie indeksu w zapytaniu.
Poniższe zapytanie zwróci wynik w kolejności klucza na indeksie unikalnym.

select * from ClusteredIndexOrder where Col2 <> 0
Wynika to z faktu, że zapytanie zawiera predykat na kolumnie klucza, co przeważnie skutkuje bardzie optymalnym planem zapytania wykorzystującym operator seek.


W jednym znastępnych wpisów pokaże jakie okoliczności muszą zajść by scan był mimowszystko preferowanym operatorem.

To co jest najważniejsze to to, że kolejność wyników może być zagwarantowana tylko poprzez klauzulę ORDER BY. W każdym innym przypadku wynik jest niedetermistyczny co pokaże w kilku kolejnych wpisach.

wtorek, 11 lutego 2014

Usuwanie planów zapytań

W poprzednim poście poruszyłem temat planów wykonania zapytań i to, że mogą mieć one negatywny wpływ na wydajność bazy danych w przypadku gdy nie są ponownie wykorzystywane. Plany o niskim poziomie ponownego wykorzystania tylko zabierają miejsce na dane.

Co więc zrobić aby serwer funkcjonował wydajnie bez konieczności jego częstego restartu?

Otóż plany wykonania można usunąć z bazy danych.

Do tego calu służy dunkcja DBCC FREEPROCCACHE, która przyjmuje plan_handle jako parametr.
Poniższe zapytanie może być wykorzystane do zidentyfikowania potęcjalnych planów zapytań do usunięcia.
select
    cp.plan_handle,
    cp.usecounts,
    cp.size_in_bytes,
    cp.cacheobjtype,
    cp.objtype,
    txt.[text] 
from sys.dm_exec_cached_plans AS cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) txt
order by cp.usecounts 
Wywoływania DBCC FREEPROCCACHE manualnie raczej nie jest najbardziej optymalnym rozwiązaniem. Dlatego poniżej dwie opcje na poradzenie sobie z problemem w sposób automatyczny, poprzez użycie zapytania w zadaniu SQL Agenta.

Opcja 1. Usuwanie wszystkich zapytań typu ad hoc.
--Remove all Adhoc plans
declare @plan_handle varbinary(64)
declare objtype = 'Adhoc'
open plancleanup
fetch next from plancleanup into @plan_handle
while @@fetch_status = 0
begin
    dbcc freeproccache(@plan_handle)    
    fetch next from plancleanup into @plan_handle
end
close plancleanup
deallocate plancleanup
go
Opcja 2. Usuwanie zapytań ad hoc jak i też tych przygotowanych (sp_executesql) w przypadku gdy ich wielkość w pamięci przekracza ustalony próg .
--Remove all plans when they take more than 20 MB.
declare @cachesize decimal(10,2)
declare @threshold decimal(10,2)
set @threshold = 20
select @cachesize = (sum(size_in_bytes)*1.)/1024/1024 from sys.dm_exec_cached_plans where objtype in ('Adhoc', 'Prepared')
if(@cachesize > @threshold) 
begin
DBCC FREESYSTEMCACHE('SQL Plans');
end
go
Wywołanie DBCC FREEPROCCACHE bez parametów usunie także plany wykonania dla procedur składowanych. Nie jest to dobra opcja.

Powyższe zapytania na serwerach produkcyjnych powinny być wykonywane z rozwagą.

poniedziałek, 10 lutego 2014

Cachowanie planów wykonania

SQL Server domyślnie przechowuje plany wykonania dla wszystkich zapytań. Każdy plan zajmuje jednak trochę miejsca i jak tego miejsca na serwerze będzie za mało to inne procesy mogą z tego powodu działać znacznie wolniej. Poza planami wykonania zapytań server w cachu przechowuje też dane, które były zwrócone przez zapytania, tak by w przypadku ponownego uruchomienia tego samego zapytanie nie trzeba było odczytywać ponownie danych z dysku.

Dlatego, też jeżeli server ma w pamięci zbyt dużo planów wykonania, które nie podnoszą wydajności serwera mniej miejsca będzie dostepnego dla danych.

Jak już wspomniałem, SQL server domyślnie przechowuje wszystkie plany, ale w zależności od rodzaju zapytania, mogą być one w różnej postaci.
--Query 1  (ad hoc)
select * from [sys].[objects] where object_id = 3
go
 
--Query 2 (ad hoc)
declare @id int = 3
select * from [sys].[objects] where object_id = @id
go
Powyższe dwa zapytania są klasycznym przykładam zapytań ad hoc. Wykonanie tego skryptu spowoduje, że w cachu pojawią się dwa plany wykonania. Zmiana wartości predykatu wzapytaniu spowoduje wygenerowanie nowego planu. Niekonieczie musi być to zła wiadomość z punktu widzenia poszczególnych zapytań (lepsze wykorzystanie statystyk). Trochę więcej na ten temat pisałem tutaj.

Zdarza się, że tego typu zapytania znajdują się w aplikacjach. Z reguły oznacza to kiepską architekturę systemu. Choć pewnie może wynikać też z faktu, że kiedyś kawałek kodu został napisany tylko “tymczasowo” i tak już zostało. Czasami też po prostu łatwiej skorzystać z zapytania ad hoc niż tworzyć kolejną procedurę składowaną. Tak czy siak plan wykonania będzie w cachu.

Innym podejściem jest wykorzystanie sp_executesql.
--Query 3 (prepared statement)
declare @id int = 3
declare @sql nvarchar(max)
set @sql = N'select * from sys.objects where object_id = @id'
exec sp_executesql @sql, N'@id int', @id = @id
go
W tym przypadku plan wykonania będzie jeden niezależnie od wartości predykatu. W zależności od liczebności zbioru danych i jakości statystyk niewłaściwe indeksy mogą być wykorzystane dla konkretnego warunku. Dobra wiadomość jest taka, że tylko jeden plan wykonania będzie zapisany. Jest to zdecydowana poprawa w stosunku do poprzedniej opcji.

Jeżeli zapytanie jest wykonywane bardzo sporadycznie, do tego stopnia, że nie przeszkadza nam ponowne wygenerowanie planu wykonania, warto zastanowić się nad OPTION(RECOMPILE). W przypadku zastosowania tej opcji w zapytaniu, jego plan nie zostanie dodany do cachu. Skutkiem ubocznym, będzie to, że każde wykonanie zapytania będzie wymagało jego kompilacji.

Poniżej przykład tego typu zapytania
--Query 4 (recompile)
select * from sys.objects where object_id = 3 option(recompile)
go
OPTION(RECOMPILE) może być także użyte w przypadku sql_executesql.
Warto mieć powyższe na uwadze projektując aplikacje i dostęp do bazy danych. Nie każde zapytanie jest takie samo i różne sposoby ich wykonywania mogą poprawić wydejność serwera.