SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Skany indeksów i statystyki odczytów STATISTICS IO

W SQL Server mamy wiele sposobów i narzędzi, za pomocą których możemy obserwować liczniki związane z wydajnością – DMV, statystyki sesji, Extended Events czy Profiler. Analizując szczegółowo pewien przypadek skanowania indeksu, natrafiłem na kolejny wyjątek przekłamywania informacji o dokładnej liczbie fizycznych odczytów przez zmienną sesji STATSTISTICS IO.


Statystyki odczytów – SET STATISTICS IO

Powszechnie znanym problemem związanym ze statystykami widocznymi poprzez STATISTICS IO, jest pomijanie liczby odczytów generowanych przez funkcje skalarne użytkownika. Brak informacji w planie wykonania, jak również w statystykach odczytu jest mylące i może prowadzić do błędnej interpretacji wyników np. w porównywaniu wydajności zapytań. Jak się okazuje nie jest to jedyny przypadek pomijania niektórych odczytów z wykorzystaniem STATISTICS IO.

Na zjawisko większej liczby stron wczytanej do pamięci niż by się można tego było spodziewać, natrafiłem testując skanowanie indeksu klastrowego. Warto podkreślić, że wbrew powszechnie utartym opiniom (powielanym zresztą w wielu publikacjach), skanowanie indeksu to nie tylko czytanie stron na poziomie liści (leaf level skan).

W trakcie skanu, wczytywana jest cała jego struktura, włącznie ze wszystkimi stronami poziomów pośrednich + strony IAM, co może wydawać się nieco zaskakujące. Wynika to prawdopodobnie z faktu, że zazwyczaj sama struktura indeksu (korzeń + poziomy pośrednie) jest relatywnie niewielka w stosunku do poziomu liści. Skanując więc tabelę, warto przy okazji przeczytać pozostałe strony, aby mieć w pamięci cały obiekt. Poza tym strony te, i tak głównie znajdują się w ekstentach zalokowanych na potrzeby danej tabeli, więc zysk z wykorzystanie odczytów wyprzedzających (read-ahead reads) jest znacznie większy niż odsiewanie stron indeksu (<> DATA_PAGES). Taka jest moja interpretacja tego faktu.

Krótki przykład. Skanowanie indeksu klastrowego tabeli dbo.Orders i liczba odczytów widocznych poprzez SET STATISTICS IO :

USE NORTHWIND
GO
 
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE; 
CHECKPOINT
GO
 
SET STATISTICS IO ON
GO
 
SELECT * FROM dbo.Orders
(830 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 22, physical reads 1, read-ahead reads 21, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

W tym przypadku, pierwsze wykonanie zapytania (z wyczyszczonym buforem) skutkowało w sumie 22 odczytami fizycznymi (physical + read-ahead reads) ładującymi strukturę do pamięci RAM. Następnie, wszystkie 22 strony zostały odczytane (logical reads) w celu realizacji zapytania.
Zatem każde kolejne wykonanie tego zapytania będzie wiązało się już tylko z logicznymi odczytami owych 22 stron z bufora.

Dlaczego wartością oczekiwaną są 22 strony ? Zerknijmy na strukturę tego indeksu i całkowitą liczbę stron dla obiektu PK_Orders.

SELECT i.index_id, i.NAME
	,index_type_desc
	,index_level
	,page_count
	,record_count, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Orders'),1,NULL,'DETAILED') x
INNER JOIN sys.indexes i ON x.index_id = i.index_id
	AND x.object_id = i.object_id
order by i.index_id, x.index_level desc

statystyki_odczytow_01

Jak widać indeks jest dwu-poziomowy, 1 strona korzenia + 20 stron liści = 21. Brakującą stroną jest IAM, która również jest czytana. Pełna struktura, wszystkie strony dla obiektu PK_Orders to :

SELECT page_type_desc, count(*) as PageCnt
FROM  
	(select name, index_id from sys.indexes where object_id = object_id('dbo.Orders') and name = 'PK_Orders' ) i 
		cross apply sys.dm_db_database_page_allocations (db_id(), object_id('dbo.Orders'), index_id, NULL, 'DETAILED') a
	where page_type_desc is not null
group by page_type_desc

statystyki_odczytow_02
Skanowanie indeksu to wczytanie wszystkich stron związanych z jego strukturą do pamięci. Oczywiście są pewne wyjątki, chociażby częściowe skanowanie indeksu (np. SELECT TOP 10 * FROM tabela), ale w przypadku pełnych skanów… to poprostu pełne skany.
Być może powyższy przykład jest mało przekonujący z uwagi na rozmiar indeksu, ale możesz ten fakt potwierdzić, samodzielnie odpytując strukturę bardziej rozdmuchanego indeksu :

SET STATISTICS IO OFF
GO
IF object_id('dbo.CLX_TEST') is not null
	DROP TABLE dbo.CLX_TEST
GO
 
CREATE TABLE dbo.CLX_TEST
(
	id bigint IDENTITY(1,1),
	id2 char(200) default NEWID(), 
	payload char(100),
)
GO
 
insert into dbo.CLX_TEST with (tablockx) (payload)
select top 10000 left(text,100) from sys.messages
GO
 
-- Kompozytowy   żeby trochę rozdmuchać indeks
CREATE CLUSTERED INDEX CLX_ID_ID2_CLX_TEST on dbo.CLX_TEST(id,id2)  
 
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE; 
CHECKPOINT
GO
 
SET STATISTICS IO ON
GO
 
SELECT * FROM dbo.CLX_TEST
 
SELECT  page_type_desc, page_level,  COUNT(*) as PageCnt
FROM  sys.dm_db_database_page_allocations 
         (db_id(), object_id('dbo.CLX_TEST'), 1, NULL, 'DETAILED') a
WHERE page_type_desc is not null
GROUP BY page_type_desc, page_level

Fizyczne odczyty

Wróćmy do odczytów fizycznych, czy na pewno było ich 22 ? Zerknijmy raz jeszcze na statystyki i liczbę stron danych bazy Northwind, które pojawią się w buforze (widok sys.dm_os_buffer_descriptors, strony z innych baz, odczyty systemowe etc. pomijam) :

IF object_id('tempdb..#t1') is not null
	DROP TABLE #t1
IF object_id('tempdb..#t2') is not null
	DROP TABLE #t2
GO
 
dbcc freeproccache
dbcc dropcleanbuffers
checkpoint
GO
 
select * into #t1 from sys.dm_os_buffer_descriptors where database_id = db_id()
select * from dbo.Orders
select * into #t2 from sys.dm_os_buffer_descriptors where database_id = db_id()
 
-- ile nowych stron ?
select count(*) as NowychStron 
from (
	select * from #t2  
	except 
	select * from #t1 
) x

statystyki_odczytow_03

Tym razem widzimy wynik 29. Zobaczmy więc co to są za strony :

;with cte as (
		select * from #t2   
		except 
		select * from #t1
  )  
select  d.page_id, d.page_level, d.page_type, d.row_count, x.extent_page_id, x.is_mixed_page_allocation,  OBJECT_NAME(p.object_id) as TableName , i.name  
from  cte d 
	inner join sys.dm_db_database_page_allocations (db_id(), null , null , NULL, 'DETAILED') x on d.page_id = x.allocated_page_page_id
	left join sys.allocation_units a on d.allocation_unit_id = a.allocation_unit_id 
	left join sys.partitions p on a.container_id = p.partition_id
	left join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
group by d.page_id, d.page_level, d.page_type, d.row_count, x.extent_page_id, x.is_mixed_page_allocation,  p.object_id , i.name  
order by page_id

statystyki_odczytow_04

No i tu niespodzianka. Pomimo, że skanujemy indeks klastrowy PK_Orders to przeczytaliśmy przy okazji 1 stronę zupełnie z innej tabeli (Order Details) plus 6 stron indeksów które nie biorą udziału w realizacji tego zapytania. Jak łatwo się domyślić jest to efekt uboczy mechanizmu read-ahead reads. Te dodatkowe (i niepotrzebne do realizacji zapytania) 7 stron, znajduje się w ekstentach mieszanych (tutaj są to ekstenty numer 456 i 472). Trafiły do buffer cache w wyniku read-ahead reads, co oczywiście nie jest wielkim narzutem ale rozbieżność w liczbie odczytów widocznych w STATISTICS IO pozostaje faktem.

Korzystając z DMV zobaczymy faktyczną liczbę stron wczytanych do bufora :

SELECT st.text, total_logical_reads, 
	total_physical_reads, execution_count,   last_rows
FROM sys.dm_exec_query_stats AS qs  
	 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
WHERE   st.text  not like '%sys%'

statystyki_odczytow_05
Możemy potwierdzić to zachowanie przechwytując informacje o odczytach za pomocą zdarzeń XE : file_read_completed i physical_page_read. Zdefniujmy sesję XE w ten sposób :

dbcc dropcleanbuffers
checkpoint
GO
 
CREATE EVENT SESSION [reads] ON SERVER 
ADD EVENT sqlserver.file_read_completed(SET collect_path=(1)
    WHERE ([sqlserver].[session_id]=( 52 ))),
ADD EVENT sqlserver.physical_page_read(
    WHERE ([sqlserver].[session_id]=( 52 ))) 
ADD TARGET package0.event_file(SET filename=N'C:\TEMP\file_read.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,
	  MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
ALTER EVENT SESSION [reads] ON SERVER STATE = START;
GO
 
SELECT * FROM dbo.Orders
GO
 
DROP EVENT SESSION [reads] ON SERVER 
GO
 
SELECT Event_type,  SUM(size) as Size , COUNT(*) as Events_cnt
FROM   ( 
       SELECT	Event_type =n.event_xml.value('(//@name)[1]','nvarchar(max)'),
				size=e.event_data_XML.value('(//data[@name="size"]/value)[1]','int'), 
				page_id=e.event_data_XML.value('(//data[@name="page_id"]/value)[1]','int') 
 
        FROM    ( 
               SELECT CAST(event_data AS XML) AS event_data_XML 
               FROM sys.fn_xe_file_target_read_file(N'C:\temp\file_read*.xel', NULL, NULL, NULL) 
               )e CROSS APPLY event_data_XML.nodes('//event') n (event_xml)
       )q 
GROUP BY Event_type

Po wykonaniu zapytania z czystym buforem, dostaniemy informacje o następującej liczbie zdarzeń (file_read_completed zawiera parametr size wyrażony w bajtach co po prostym przeliczeniu 237568 / 1024 / 8 daje dokładnie 29 stron) :
statystyki_odczytow_06

No i na koniec jeszcze jeden sposób, ostatecznie potwierdzający że 29 stron to było dokładnie tyle, ile zostało wczytanych z pliku z danymi bazy Northwind. Korzystam tutaj z fajnego narzędzia MS do monitorowania aktywności – procmon dostępnego tutaj. Kilka filtrów (proces, plik bazodanowy) i prosta matematyka daje potwierdzenie poprzedniego rezultatu (3 faktyczne operacje IO, w sumie 29 stron).
statystyki_odczytow_07


Podsumowanie i wnioski

Informacje na temat statystyk odczytu widocznych w parametrach sesji STATISTICS IO powinny być traktowane jako przybliżone. Wartości odczytów dotyczą, obiektów które są widoczne w planie wykonania (+ wyjątki dla tabel tymczasowych pośrednich i funkcji skalarnych) – lepiej więc stosować DMV jeśli chcemy widzieć dokładne wyniki. Rozbieżność nie jest duża, nie ma negatywnego wpływu na wydajność i jest logicznie wytłumaczalna.
Skanowanie indeksów klastrowych to czytanie wszystkich stron związanych z ich strukturą (leaf pages, intermediate, root, IAM’s).

Statystyki rozkładu danych w SQL Server

Silniki bazodanowe wykorzystują statystyki rozkładu danych do utworzenia optymalnego planu wykonania zapytania. Decyzja w jaki sposób wykonywane będzie zapytanie, w głównej mierze opiera się właśnie o informacje na temat liczebności przetwarzanego / zwracanego zbioru. Dotyczy to nie tylko sposobu dostępu do danych – czyli czy wykonać skan tabeli a być może przeszukać właściwy indeks. Na podstawie wielkości szukanego zbioru podejmowane są decyzje o kolejności działań, wyborze algorytmu łączenia tabel czy alokacji pamięci. W artykule tym przedstawię problem aktualności statystyk i ich znaczenie w procesie optymalizacji zapytań.


Po co są statystyki (histogramy rozkładu danych)

Wysyłając nowe zapytanie do serwera bazodanowego, wykonywana jest jego analiza pod kątem składniowym i logicznym. Następnie uruchamiany jest proces tworzenia optymalnego planu wykonania zapytania. Proces ten jest dość rozbudowany i może być czasochłonny. Dlatego raz utworzone plany przechowywane są w pamięci serwera celem ponownego wykorzystania w przyszłości.

Ogólnie rzecz biorąc, tworzenie planu rozpoczyna się od identyfikacji właściwych struktur do odpytania i wyboru metody dostępu do danych. Pomijając plany trywialne, dla których statystyki w ogóle nie są analizowane, histogramy rozkładu pomagają oszacować przydatność poszczególnych indeksów oraz sposób ich wykorzystania.

Weźmy na przykład proste zapytanie odwołujące się do jednej tabeli :

--#START przygotowanie tabeli testowej
IF OBJECT_ID('dbo.test') is not null
	DROP TABLE dbo.test
 
CREATE TABLE dbo.test
(
	id			INT IDENTITY(1,1),
	high_selective_col	INT,	
	descr			VARCHAR(100)
	CONSTRAINT [CLX_PK_Test] PRIMARY KEY CLUSTERED
	(id ASC)
)
GO
 
SET NOCOUNT ON
 
INSERT INTO dbo.test(high_selective_col,  descr) 
VALUES( IDENT_CURRENT('dbo.test'), REPLICATE('x',100))
GO 100000
 
CREATE NONCLUSTERED INDEX NCLX_TEST_HSC ON dbo.test(high_selective_col)
GO
 
  --#END  przygotowanie tabeli
 
-- nasze zapytanie
SELECT * FROM dbo.test
WHERE high_selective_col = 123

stat1

Pierwsze pytanie na jakie optymalizator musi znaleźć odpowiedź to, do jakich struktur (tabel, indeksów) trzeba sięgnąć aby wykonać to zapytanie w sposób optymalny. Optymalny czyli minimalizujący zasoby systemowe niezbędne do jego realizacji. Chodzi głównie o ilość operacji I/O czyli po prostu zasobów dyskowych i pamięci RAM. Ponieważ mamy tu zapytanie do jednej tabeli, pod uwagę bierzemy tylko struktury bezpośrednio z nią związane.

Nasza tabela testowa zorganizowana jest w strukturze indeksu klastrowego i dodatkowo mamy utworzony indeks nieklastrowy na kolumnie high_selective_col który jak łatwo się domyśleć, będzie tutaj wykorzystany. Przeanalizujmy jednak dlaczego i w jakich sytuacjach optymalizator wybiera konkretny indeks. Struktura, wysokość drzew indeksów (index level) wyglądają następująco :

SELECT i.index_id, i.NAME
	,index_type_desc
	,index_level
	,page_count
	,record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.test'), NULL, NULL, 'DETAILED') x
INNER JOIN sys.indexes i ON x.index_id = i.index_id
	AND x.object_id = i.object_id
	order by i.index_id, x.index_level desc

stat2

W zapytaniu testowym, jedynym predykatem czyli wyrażeniem opisującym cechy szukanych obiektów (rekordów) jest warunek high_selective_col = 123. Stanie się on naszym predykatem wyszukiwania (SEEK PREDICATE) bo mamy utworzony na tej kolumnie indeks NCLX_TEST_HSC i optymalizator na podstawie statystyk rozkładu danych wie, że warto go użyć. Jest to indeks nieklastrowy, zbudowany na tabeli z indeksem klastrowym. Oznacza to, że na poziomie liści tego indeksu, znajdują się klucze indeksu klastrowego (wartość kolumny – ID ).

Nasze zapytanie jest wysoce selektywne – zwraca jeden rekord. Jeśli zdecydujemy się na użycie tego indeksu, aby dostać się wszystkich kolumn jednego rekordu (w końcu mamy select *) będziemy musieli przejść przez dwie struktury B-drzewa. Znając liczbę poziomów indeksów, łatwo policzyć że potrzeba minimum 5 odczytów (widać to w statystykach odczytów – logical reads 5). Jest to dużo mniej niż cała tabela, która ma 1516 stron na poziomie liści indeksu klastrowego, więc zysk jest oczywisty. Optymalizator jak widać wybrał właściwy plan :

stat4

stat3

Dzięki aktualnym statystykom rozkładu danych, optymalizator przed wykonaniem samego zapytania jest w stanie oszacować dla ilu rekordów nasz warunek wyszukiwania będzie spełniony. Łatwo więc obliczyć koszty (tutaj głównie liczbę odczytów) wykonania zapytania dla różnych wariantów planów. Statystyki dla kolumny high_selective_col zostały stworzone automatycznie w momencie zakładania indeksu. Są więc w 100% aktualne i pełne. Widać to dobrze we właściwościach operatora przeszukiwania indeksu :

stat5

Estymacja kardynalności w tym przypadku jest = 1 (estimated number of rows) co pokrywa się z rzeczywistą liczbą zwróconych rekordów (actual number of rows = 1). Powyższy plan będzie optymalny dla zapytań o wysokiej selektywności czyli zwracających niewiele rekordów.

Łatwo sobie wyobrazić, że dla zapytań mało selektywnych, zwracających już nawet kilkaset rekordów, plan ten nie będzie tak efektywny (przeszukiwanie w pętli dwóch struktur vs skan tabeli). Widać to w kolejnym przykładzie, gdy wyszukiwać będziemy 5000 rekordów. Pierwsze zapytanie jest zoptymalizowane przez silnik SQL Server, w drugim wymuszam użycie indeksu nieklastrowego. Plany i statystyki odczytów poniżej :

-- zapytanie optymalizowane przez SQL Server :
SELECT * FROM dbo.test   
WHERE high_selective_col <= 5000
 
-- wymuszony, poprzedni plan wykonania zapytania 
SELECT * FROM dbo.test WITH ( INDEX(NCLX_TEST_HSC) )
WHERE high_selective_col <= 5000

stat6
stat7

Widać że stary plan wykonania (Query 2) jest 10x gorszy pod kątem liczby odczytów niż skan całej tabeli. Optymalizator zapytań wykonał dobrą robotę, pokazując przy okazji jak ważną rolę w procesie optymalizacji spełniają aktualne statystyki. Dzięki nim, potrafił oszacować liczbę przetwarzanych rekordów i wybrał przeczytanie całej tabeli zamiast 5000 iteracji po strukturach indeksów.


Problem nieaktualnych statystyk

Do tej pory analizowaliśmy sytuację idealną, gdy nasze statystyki były aktualne. Sprawdźmy co się stanie, gdy zmienimy nieco rozkład naszych danych wykonując szereg typowych działań (DML) dla systemu transakcyjnego.

-- dodajmy 5000 rekordów z wartością 123 
INSERT INTO dbo.test(high_selective_col,descr)
VALUES (123, REPLICATE('x',100))
GO 5000
 
-- powtórzmy nasze zapytanie testowe
SELECT * FROM dbo.test
WHERE high_selective_col = 123

stat8
stat9

Jak widać powyżej, plan wykonania zapytania nie zmienił się, ale drastycznie wzrosła liczba odczytów dyskowych (z 5 na 15015). Dodaliśmy raptem 5000 rekordów które z pewnością nie zajęły 15000 stron. Błąd w wyborze nieoptymalnego planu widać we właściwościach operatorów indeksów.

Optymalizator wciąż oczekuje że zapytanie to zwróci jeden rekord i jego estymata kosztów pozostała na poziomie 5 odczytów. Stąd wybrany został stary plan, a dużo efektywniej byłoby wykonać teraz skan tabeli. Tym razem rzeczywista liczba operacji „doczytania” brakujących danych z indeksu klastrowego to 5001 – tyle mamy rekordów . Ta rozbieżność pomiędzy wartościami „Estimated / Actual number of rows” to typowy symptom nieaktualnych statystyk. Co więcej wprowadza również błąd w porównaniu procentowym kosztów planów wykonania zapytań, obserwowanym w Management Studio (które również oparte jest o tę samą wiedzę z której korzysta optymalizator).

Wykonajmy teraz obydwa zapytania jako jeden batch i porównajmy plany wykonań oraz ich koszty :

-- zapytanie optymalizowane w oparciu o nieaktualne statystyki
SELECT * FROM dbo.test
WHERE high_selective_col = 123 
 
-- zapytanie z wymuszonym skanowaniem indeksu
SELECT * FROM dbo.test WITH (INDEX(0))
WHERE high_selective_col = 123

stat10stat11
Jak widać, informacja o efektywności jest mocno przekłamana. Pierwsze zapytanie uruchomione w oparciu o nieaktualne statystyki i pozostawione optymalizatorowi zajmuje teoretycznie 1% całego batcha czyli wydaje się bardzo efektywne. Drugie zapytanie z wymuszonym skanem indeksu (WITH(INDEX(0)) zabrało niby 99% czasu/kosztu całego batcha, co oczywiście jest nie prawdą. Widać to w statystykach odczytów. Jest to kolejny przykład (po funkcjach skalarnych), że nie należy porównywać wydajności dwóch zapytań w oparciu o informacje z graficznego interfejsu Management Studio.


Informacje o statystykach

Do obejrzenia szczegółowych informacji o statystykach najlepiej sięgnąć po procedurę DBCC SHOW_STATISTICS, mamy też do dyspozycji kilka widoków/funkcji systemowych. Zobaczmy więc jak wyglądają statystyki dla indeksu NCLX_TEST_HSC :

DBCC SHOW_STATISTICS('dbo.test','NCLX_TEST_HSC')

stat12
Procedura DBCC SHOW_STATISTICS domyślnie zwraca trzy recordset’y. W pierwszym widzimy podstawowe informacje na temat statystyki. Kiedy i dla jakiej próbki rekordów została utworzona. Widać też od razu że nie jest w pełni aktualna, bo po ostatnim insercie, mamy w tabeli 105000 rekordów a statystyka została zrobiona dla 100000.

SQL Server może automatycznie aktualizować statystyki, ale algorytm jest dość oszczędny i autoupdate (dla SQL Server 2014 i wcześniej) wykonywany jest po przekroczeniu progu (20%+500) modyfikacji rekordów, czyli w naszym przypadku nastąpiłby po zmodyfikowaniu 20501 rekordów. Ma to swoje uzasadnienie, bo utworzenie/aktualizacja statystyk pociąga za sobą koszty związane choćby z przeczytaniem tabeli lub jej próbki. Dlatego traktujemy mechanizm automatyczny raczej jako element wspierający niż coś co może zastąpić świadomy plan utrzymania.

Warto wspomnieć, że W SQL Server 2016 algorytm ten został zmodyfikowany i dla dużych tabel automatyczny update wykonywany jest częściej. W SQL Server 2008R2 SP1 została też wprowadzona flaga 2371 wymuszająca częstsze auto-aktualizacje.

Drugi recordset zawiera informacje o wektorze gęstości a trzeci – pełne informacje o przyjętych przedziałach histogramu. Kolumna high_selective_col ma bardzo prostą statystykę, którą interpretujemy następująco. Została utworzona w oparciu o próbkę 100% rekordów z tabeli testowej (Rows Sampled/Rows). Histogram jest podzielony tylko na dwa przedziały :

  • pierwszy przedział to rekordy których wartość maksymalna (RANGE_HI_KEY) = 1. W tym przedziale nie ma żadnych rekordów poniżej wartości maksymalnej ( DISTINCT oraz RANGE_ROWS = 0) i jest tylko jeden rekord równy górnej granicy przedziału (EQ_ROWS = 1). Wartość informacyjna tego przedziału jest taka, że jeśli będziemy szukać rekordów dla których wartość kolumny =1 to powinniśmy otrzymać tylko jeden taki rekord i nie ma w tej tabeli rekordów, dla których ta kolumna ma wartości < 1 . stat13
  • drugi przedział jest bardzo podobny jeśli chodzi o wartość informacyjną. Obejmuje rekordy dla których wartość kolumny high_selective_col jest pomiędzy (1, 100000>. Statystyka tego przedziału mówi, że znajduje się w nich 99998 (RANGE_ROWS) rekordów z wartościami poniżej górnej granicy przedziału, z czego unikalnych (DISTINCT_RANGE_ROWS) jest 99998. Jest też jeden (EQ_ROWS=1) rekord o wartości górnej granicy przedziału (RANGE_HI_KEY). Wynika z tego wprost unikalność wartości w tej kolumnie.
    stat14

Powyższa informacja ma kluczowe znaczenia w wyborze algorytmu dostępu do danych. Dzięki niej łatwo oszacować liczbę koniecznych odczytów czy powtórzeń pętli przechodzenia przez drzewo indeksu klastrowego.

Jeśli chodzi o identyfikację nieaktualnych statystyk, najlepiej sięgnąć do funkcji tabelarycznej sys.dm_db_stats_properties dzięki której możemy dowiedzieć się kiedy nastąpiła ostatnia aktualizacja statystyk i ile modyfikacji od tego momentu miało miejsce.

 SELECT
	s.name AS "Statistic name",
	sp.last_updated AS "Last Updated",
	sp.rows,
	sp.rows_sampled,
	sp.unfiltered_rows,
	sp.modification_counter AS "Modifications",
	(sp.rows*0.2)+500-sp.modification_counter as updates_to_auto_refresh_stats
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties (s.object_id,s.stats_id) AS sp
WHERE s.name = 'NCLX_TEST_HSC'

stat16

Statystyka dla indeksu NCLX_TEST_HSC została utworzona w momencie jego utworzenia, w oparciu o pełną próbkę 100000 rekordów. Widać że jest w pewnym stopniu nieaktualna, ponieważ wykonanych zostało 5000 modyfikacji (INSERT). Statystyka będzie traktowana jako nieaktualna i zostanie przeliczona na nowo, dopiero po przekroczeniu progu określonego algorytmem 20%+500 zmian (czyli brakuje jeszcze 15500 operacji) oraz po kolejnym odwołaniu się do tego indeksu.


Aktualizacja statystyk

Aktualizację statystyk możemy wykonać na kilka sposobów. Pierwszy, już wspominany to pozostawienie tego zadania mechanizmom automatycznym. W zdecydowanej większości przypadków są one jednak nieefektywne i dla dużych tabel mogą występować po prostu zbyt rzadko. Lepiej jest identyfikować nieaktualne struktury i dokonywać aktualizacji w ramach planu utrzymania bazy danych. Możemy aktualizować pojedyncze statystyki, lub np. wszystkie dla danej tabeli lub bazy danych (sp_updatestats). Do tego celu najlepiej użyć polecenia UPDATE STATISTICS :

-- pełna aktualizacja wszystkich statystyk dla tabeli dbo.test
UPDATE STATISTICS dbo.test WITH FULLSCAN
 
-- pełna aktualizacja statystyki dla wybranego indeksu
UPDATE STATISTICS dbo.test NCLX_TEST_HSC WITH FULLSCAN

Aktualizację statystyk, jeśli jest konieczna i rozkład danych w kolumnie nie jest jednorodny, najlepiej wykonać w trybie FULLSCAN. Oczywiście nie zawsze możemy sobie na to pozwolić z uwagi na ograniczenia czasowe.

Warto wiedzieć, że przebudowanie indeksu (REBUILD) zawsze aktualizuje statystyki w trybie pełnym (WITH FULLSCAN). Nie ma więc potrzeby powtarzać tej operacji dla indeksów które są przebudowywane w planie utrzymania.

Sprawdźmy jeszcze raz naszą statystykę po aktualizacji i wykonajmy raz jeszcze zapytanie testowe.

DBCC SHOW_STATISTICS('dbo.test','NCLX_TEST_HSC') WITH HISTOGRAM
 
SELECT * FROM dbo.test
WHERE high_selective_col = 123

stat15

Histogram został podzielony po aktualizacji na cztery przedziały (maksymalnie może ich być 200). Użytkownik nie ma wpływu na to w jaki sposób ten podział zostanie wykonany, co czasem bywa problemem (rozkład nierównomierny).

Najciekawszy jest teraz drugi przedział histogramu. Widzimy że w zakresie od (1,123> mamy w teraz 121 (RANGE_ROWS) + 5001 (EQ_ROWS) = 5122 rekordów. Przejdźmy do planu zapytania :

stat17
Tym razem plan jest zupełnie inny. Został utworzony w oparciu o nowe informacje – dla 5001 rekordów nie ma sensu iterować po strukturach indeksu, łatwiej jest wykonać skan tabeli co też zostało uczynione. Optymalizator utworzył optymalny plan bo działał w oparciu o aktualne statystyki.


Podsumowanie

Aktualność statystyk jest bez wątpienia jednym z najważniejszych czynników wpływających na tworzenie optymalnego planu zapytania. Nieaktualne informacje o rozkładzie wpływają również na efektywność wykonań planów równoległych. Źle oszacowana liczebność przetwarzanego zbioru, może powodować nierówny podział pracy dzielonej na wątki co później objawia się zwiększonym czasem oczekiwania (wait’s CXPACKET). Jak widać skutków ubocznych nieaktualnych statystyk jest sporo dlatego warto o nich pamiętać i mieć je na uwadze w planach utrzymania bazy danych.


Źródła :
SQL Server Statistics, MSDN
SQL Server Statistics, Holger Schmeling

Złożoność obliczeniowa i zapytania SQL do dużych tabel

Stat_smallProces optymalizacji zapytania, bazuje między innymi na konkretnych zbiorach testowych. Sprawdzamy różne warianty, mierzymy wydajność, a po jakimś czasie…. okazuje się, że wraz z dużym przyrostem danych zapytanie dramatycznie zwalnia. Nie chodzi tylko o kwestie właściwych indeksów czy aktualnych statystyk. Warto zastanowić się nieco bardziej nad docelowym zbiorem, na którym będzie pracować dana kwerenda.

W artykule tym, rozwinę nieco wątek badania wydajności zapytań SQL o czynnik złożoności obliczeniowej i analizy rozkładu danych. Porównamy zapytania doskonale działające dla małych zbiorów, z takimi, które nie mają konkurencji w przypadku dużych tabel.


Analiza kwerendy grupującej

Do testów posłużę się przykładem prezentowanym w artykule dotyczącym metod pomiarów wydajności zapytań. Weźmy pod uwagę trzy wersje kwerend, do wyszukania informacji o najnowszym zleceniu dla każdego z Klientów. Zakładamy odwoływanie się w zapytaniu tylko do jednej tabeli – dbo.Orders.

USE NORTHWIND
GO
 
-- Q1 - prosty join 
SELECT /* Simple Join */ o.CustomerId, o.OrderId  , o.ShipCountry
FROM dbo.Orders o INNER JOIN 
		( 
		SELECT CustomerID, MAX(OrderID) AS OrderID 
		FROM dbo.Orders 
		GROUP BY CustomerID
		) AS MaxOrd   ON  o.OrderID = MaxOrd.OrderID;
 
-- Q2 - podzapytanie skorelowane
SELECT /* Correlated SubQ */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID = (
          SELECT MAX(OrderID) 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o1.CustomerID 
                )
 
-- Q3 z CROSS APPLY (analogia TVF)
SELECT /* CROSS APPLY */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders o Cross APPLY ( SELECT MAX(OrderID) as MaxOrderID 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o.CustomerID ) t
where o.OrderID = MaxOrderID;
GO 100

W testach udało się ustalić, że najlepszym rozwiązaniem z zaproponowanych, okazało się zapytanie numer 1 – Simple Join.
Zapytania_do_duzych_tabel_01
Testowane kwerendy, działały jednak na bardzo małym zbiorze. W tabeli dbo.Orders jest tylko 830 rekordów. Różnica w wydajności, wynika w tym przypadku z lepszego planu wykonania dla Simple JOINa. Pozostałe dwie kwerendy, posiadają gorszy (ale taki sam) plan, stąd niemal identyczny wynik.

Optymalizator zapytań, wraz ze wzrostem liczebności zbiorów czy rozkładem danych, potrafi wybrać korzystniejszy plan wykonania (jeśli obecny nie spełnia określonych kryteriów). Do dalszych testów, posłużę się analogiczną tabelą jak dbo.Orders (jeśli chodzi o rozkład danych CusomerId). Będziemy zwiększać liczbę zleceń (rekordów), pozostawiając ilość Klientów na tym samym poziomie. Skrypt generujący i zapełniający naszą testową dbo.Orders poniżej :

USE NORTHWIND
GO
 
IF OBJECT_ID('dbo.Orders2') is not null DROP TABLE dbo.Orders2
 
CREATE TABLE dbo.Orders2 (
  OrderId INT NOT NULL PRIMARY KEY  IDENTITY(1,1),
  CustomerID VARCHAR(5) ,
  OrdValue decimal(10,2),
  ShipCountry varchar(10)
)
GO
 
-- odtworzenie analogicznych indeksów co w Northwind.dbo.Orders
CREATE NONCLUSTERED INDEX CustOrd ON [dbo].[Orders2]  ( CustomerId ASC)
 
-- na początek 1000 zleceń dla 100 klientów
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE @i < 1000
BEGIN
  INSERT INTO dbo.Orders2 (  CustomerID, OrdValue, ShipCountry)
  VALUES(
	 CAST(FLOOR(RAND()*100)+1 AS VARCHAR(5)), 
	 FLOOR(RAND()*100000)+1 ,
	 LOWER(LEFT(NEWID(),8))
     )
  SET @i = @i+1;
END

Na początek sprawdzenie czy w naszej testowej tabeli, wyniki będą podobne dla analogicznego zbioru – 1000 zamówień dla 100 Klientów.
Zapytania_do_duzych_tabel_02
Jest podobnie – mniej więcej te same klasy wielkości. Simple JOIN wypada znacznie lepiej niż pozostałe dwie, identyczne pod kątem wydajności (planów/odczytów) kwerendy.
Zobaczmy, jak będzie wypadało porównanie wydajności, zwiększając liczbę rekordów.
Zapytania_do_duzych_tabel_03
Widać dla tego scenariusza, że w pewnym momencie, już od około 40k rekordów, wszystkie zaproponowane rozwiązania, pędzą w tym samym rytmie – takim samym planie wykonania. Oferują praktycznie identyczną wydajność w przypadku większych zbiorów. Jest ona ściśle związana ze złożonością obliczeniową algorytmu dostępu do danych – rośnie tu liniowo (skany indeksów) w stosunku do liczby rekordów.


Stała złożoność obliczeniowa O(1) czyli „The Fifth Element”

Przełomowym rozwiązaniem dla naszego scenariusza, może być zapytanie uwzględniające ten charakterystyczny rozkład danych w tabeli. Stała liczba Klientów/obiektów grupujących i rosnąca liczba zamówień. Za pomocą CTE + funkcji okna OVER(), możemy zapisać naszą kwerendę w taki, na pierwszy rzut oka, mało intuicyjny sposób :

WITH /* Q0 */ customers AS
  (  
    SELECT MIN(CustomerId) AS CustomerId
    FROM Orders2
    UNION ALL
    SELECT a.CustomerId
    FROM (	
		SELECT o.CustomerId, ROW_NUMBER() OVER (ORDER BY o.CustomerId) AS id_Cust
		FROM customers cu INNER JOIN Orders2 o ON o.CustomerId > cu.CustomerId
	) a
    WHERE a.id_Cust = 1
  )
SELECT a.*
FROM customers c
CROSS APPLY(
	    SELECT TOP 1 o.CustomerId, o.OrderId, o.ShipCountry
		FROM Orders2 o
		WHERE o.CustomerId = c.CustomerId
		ORDER BY OrderId desc   ) a
-- na wypadek gdyby było więcej niż 100 klientów ;)
OPTION (MAXRECURSION 0)

Na początek porównanie wydajności dla małej tabeli (1000 zamówień, 100 Klientów). Widać że nowa kwerenda „nieco odstaje” od klasycznie prostych, pięknych rozwiązań :
Zapytania_do_duzych_tabel_04
Prawdziwy zysk widać dopiero przy większych zbiorach, w momencie gdy wcześniejsze rozwiązania odpływają razem w jakimś kosmicznym kierunku, kwerenda na pierwszy rzut oka „przekombinowana” oferuje stałą (nawet przy milionach rekordów) wydajność.
Zapytania_do_duzych_tabel_05

Na koniec, można jeszcze poprawić te wyniki, dodając dodatkowy (właściwy dla wszystkich z tych kwerend) indeks :

CREATE INDEX DemolutionResults ON dbo.Orders2 
(CustomerID, OrderId) INCLUDE (ShipCountry);

Złożoność obliczeniowa zaproponowanego rozwiązania rośnie liniowo wraz z liczbą Klientów dla których trzeba wyszukać najnowsze zamówienie. Jest jednak stała, dla rosnącej w ramach tej grupy Klientów, zleceń. Niezależnie czy jest ich 10k czy 10M !


Podsumowanie

Nie zawsze da się osiągnąć złożoność O(1) i nie zawsze algorytm o takiej złożoności (z uwagi na koszt) będzie adekwatnym rozwiązaniem do problemu (choćby przedstawione tutaj małe tabele). Nie mniej jednak, warto mieć na uwadze przyszłość kwerend, które operować mogą na specyficznych, dużych zbiorach np. statystykach pomiarowych urządzeń.

Pomiar wydajności zapytań w SQL Server

Wydajność Wydajność zapytań SQLzapytań SQL to problem, który prędzej czy później spotyka każdego użytkownika bazy danych, piszącego kwerendy. Nie ważne czy powodem jest źle napisane zapytanie wolno generujące raport, czy ogólne obciążenie serwera, często właśnie na wskutek nieoptymalnych kwerend.
Minimalizowanie wpływu wysyłanych zapytań na obciążenie serwera, to bez dwóch zdań – obowiązek a nie tylko cecha dobrego programisty.

W artykule tym, zaprezentuję przegląd metod pomiarowych wydajności zapytań SQL dostępnych w SQL Server. Pokażę także typowe błędy i niedoskonałości niektórych z narzędzi.


Pomiar i porównanie wydajności zapytań SQL

Podstawowe pytanie w kontekście badania wydajności zapytań to, co będziemy mierzyć i w jaki sposób porównywać dwie kwerendy.

Najbardziej oczywistym wyznacznikiem jakości jest czas, od wysłania zapytania do otrzymania wyników. Wynika on z czasu pracy procesora, liczby odczytów logicznych (pamięć RAM), fizycznych (operacje dyskowe IO) i przesłaniu wyniku do użytkownika.

Ponieważ zakładamy, że porównywane zapytania mają zwracać dokładnie te same zbiory wynikowe, nie będzie nas interesował ruch sieciowy. Skupimy się więc na obciążeniu procesora, całkowitym czasie przetwarzania i licznikach odczytów.

Drugą kwestią jest analiza czasu tworzenia planu wykonania – potrzebnego do „kompilacji” zapytania i jego wykorzystania w przyszłości. Jeśli w naszym środowisku istnieje prawdopodobieństwo, że kwerenda będzie często rekompilowana – trzeba ten czynnik także uwzględnić. Jeśli jednak możemy przyjąć, że raz utworzony plan, będzie raczej rezydował na stałe w buforze – można skupić się tylko na kwestiach odczytów i czasu przetwarzania.

W naszym scenariuszu testowym, będziemy chcieli przekonać się, które rozwiązanie będzie optymalne do rozwiązania klasycznego zadania.

Chcemy wyświetlić informacje o ostatnich zleceniach złożonych przez poszczególnych Klientów. Jest to typowy problem związany z grupowaniem rekordów (Klienci) i operacjach w ramach podgrup (dane o ostatnim zleceniu danego Klienta). Analogicznie moglibyśmy chcieć znaleźć informacje o najnowszych odczytach urządzeń, najlepiej sprzedawanych produktach w ramach grupy itd.

Chyba najbardziej intuicyjny z możliwych przykładów rozwiązania :

USE Northwind
GO
 
SELECT o.CustomerId, o.OrderId  , o.ShipCountry, o.OrderDate
FROM dbo.Orders o INNER JOIN 
     ( 
        SELECT CustomerID, MAX(OrderID) AS OrderID 
        FROM dbo.Orders 
        GROUP BY CustomerID
      ) AS MaxOrd   ON  o.OrderID = MaxOrd.OrderID;

Wydajnosc_zapytan_SQL_00
To samo zapytanie możemy zapisać na wiele innych sposobów np. z wykorzystaniem podzapytania skorelowanego czy funkcji użytkownika. Z definicji, zapytania zawierające funkcje skalarne czy podzapytania skorelowane, są znacznie gorsze niż zwykłe złączenia, ale po to właśnie powstał ten artykuł, aby przekonać się czy tak jest w rzeczywistości.


Metody pomiarowe wydajności zapytań SQL

Pierwsze starcie : SET STATISTICS IO, TIME ON

Na początek porównajmy wydajności zapytań z wykorzystaniem najprostszych (przy okazji najbardziej niedoskonałych) metod. Prezentuję je jako pierwsze, bo są one często stosowane z uwagi na prostotę (dla leniuchów) a prowadzić mogą do błędnych wniosków. Przyznam szczerze, że sam nieraz dałem się nabrać na cuda jakie pokazują.

Polecenia wyświetlania statystyk odczytów IO oraz czasu, włączamy bezpośrednio w sesji połączenia.

-- włączenie statystyk czasu i odczytów
SET STATISTICS IO, TIME ON  
-- czyścimy cache (działamy na środowisku testowym ;))
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT
GO
 
-- Q1 - prosty join 
SELECT /* Q1 Simple Join */ o.CustomerId, o.OrderId, o.ShipCountry
FROM dbo.Orders o INNER JOIN 
     ( 
        SELECT CustomerID, MAX(OrderID) AS OrderID 
        FROM dbo.Orders 
        GROUP BY CustomerID
	 ) AS MaxOrd   ON  o.OrderID = MaxOrd.OrderID;
 
-- jeszcze raz dla drugiego zapytania
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT
GO
 
-- Q2 - podzapytanie skorelowane
SELECT /* Q2 Correlated SubQ */  CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID = (
          SELECT MAX(OrderID) 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o1.CustomerID 
                )

Pomiar wydajności zapytań SQL
Z powyższych wyników, można wywnioskować że drugie zapytanie (skorelowane) jest znacznie bardziej efektywne zarówno pod względem liczby odczytów jak i czasu wykonania.

Pamiętajmy jednak, że testujemy pierwsze uruchomienie zapytania z czystymi buforami, które nie do końca musi być miarodajne w odniesieniu do rzeczywistej pracy systemu. Samo utworzenia planu wykonania i odczytania danych z dysku, może przekroczyć późniejszy czas potrzebny na ponowną realizację zapytania.

Wykonajmy jeszcze raz obydwie kwerendy, bazując już na istniejących planach (bez czyszczenia pamięci) :

SELECT /* Simple Join */ o.CustomerId, o.OrderId  , o.ShipCountry
FROM dbo.Orders o INNER JOIN 
     ( 
        SELECT CustomerID, MAX(OrderID) AS OrderID 
        FROM dbo.Orders 
        GROUP BY CustomerID
	 ) AS MaxOrd   ON  o.OrderID = MaxOrd.OrderID;
 
-- Q2 - podzapytanie skorelowane
SELECT /* Correlated SubQ */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID = (
          SELECT MAX(OrderID) 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o1.CustomerID 
                )

Pomiar wydajności zapytań SQL Server
Tym razem prosty JOIN okazuje się szybszy od podzapytania skorelowanego. Zerknijmy jeszcze na analizę porównawczą, kosztów planów wykonania (w Management Studio : Include Actual Execution Plan – CTRL+M) :
Wydajnosc_zapytan_SQL_03
Według powyższych informacji, stosunek kosztów zapytania skorelowanego do zwykłego JOINa jest niemal jak 2:1 (64%: 36%). Czyli zapytanie skorelowane jest znacznie gorsze od JOINA w każdym kolejnym wywołaniu, pomimo słabości przy pierwszym wywołaniu.

Prawdziwą wadę tej metody pomiarowej zobaczymy (wręcz cuda), porównując kolejną wersję kwerendy. Trzecim rozwiązaniem naszego zadania, może być zapytanie wykorzystujące skalarną funkcję użytkownika. Na początek utwórzmy ją :

USE NORTHWIND
GO
CREATE FUNCTION dbo.GetMax
(
	@CustId char(5)
)
RETURNS INT 
AS
BEGIN
	RETURN (  
		SELECT MAX(OrderID) 
		FROM dbo.Orders as O2 
        WHERE CustomerID = @CustId )
END

Teraz porównanie trzech zapytań zwracających ten sam wynik. Nieco uproszczę pomiar – czytamy wszystko z pamięci (bez czyszczenia buforów i cache) :

-- włączenie statystyk czasu i odczytów
SET STATISTICS IO ON
 
-- Q1 - prosty join 
SELECT /* Simple Join */ o.CustomerId, o.OrderId  , o.ShipCountry
FROM dbo.Orders o INNER JOIN 
     ( 
        SELECT CustomerID, MAX(OrderID) AS OrderID 
        FROM dbo.Orders 
        GROUP BY CustomerID
	 ) AS MaxOrd   ON  o.OrderID = MaxOrd.OrderID;
 
-- Q2 - podzapytanie skorelowane
SELECT /* Correlated SubQ */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID = (
          SELECT MAX(OrderID) 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o1.CustomerID 
                )
 
-- Q3 - podzapytanie z funkcją skalarną
SELECT /* UDF */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID =  dbo.GetMax(CustomerId);

Plany wykonania :
Wydajnosc_zapytan_SQL_04
I jeszcze statystki odczytów :
Wydajnosc_zapytan_SQL_05
Z powyższego wynika, że kwerenda, która wykonuje skalarną funkcję użytkownika, jest najlepsza (co oczywiście jest błędem!). Zdecydowanie przekłamane są tutaj koszty w planach wykonania, jak i sama liczby odczytów zapytania z UDF. Dlatego powyższy sposób „analizy” nie można uznać za wiarygodny.

Podejście drugie – analiza wydajności za pomocą DMV / DMF

W SQL Server, mamy dostępnych szereg dynamicznych widoków i funkcji systemowych (DMV, DMF). Dzięki nim, możemy dotrzeć do statystyk, informacji o tym co się faktycznie dzieje w silniku bazy danych. Wykonajmy jeszcze raz analizę trzech zapytań w oparciu o te widoki :

DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT
go
 
-- żeby nie brać pod uwagę statystyk fizycznych odczytów
select * from dbo.Orders ;
GO 
-- Q1 - prosty join 
SELECT /* Simple Join */ o.CustomerId, o.OrderId  , o.ShipCountry
FROM dbo.Orders o INNER JOIN 
     ( 
        SELECT CustomerID, MAX(OrderID) AS OrderID 
        FROM dbo.Orders 
        GROUP BY CustomerID
	 ) AS MaxOrd   ON  o.OrderID = MaxOrd.OrderID;
 
-- Q2 - podzapytanie skorelowane
SELECT /* Correlated SubQ */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID = (
          SELECT MAX(OrderID) 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o1.CustomerID 
                )
 
-- Q3 - podzapytanie z funkcją skalarną
SELECT /* UDF */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID =  dbo.GetMax(CustomerId);
GO
-- GO 100
SELECT total_worker_time/execution_count AS AvgCPU  
	, total_worker_time AS TotalCPU
	, total_elapsed_time/execution_count AS AvgDuration  
	, total_elapsed_time AS TotalDuration  
	, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads 
	, (total_logical_reads+total_physical_reads) AS TotalReads
	, execution_count   , total_rows, last_rows
	, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1  
	, ((CASE qs.statement_end_offset  WHEN -1 THEN datalength(st.TEXT)  
ELSE qs.statement_end_offset  
END - qs.statement_start_offset)/2) + 1) AS txt  
FROM sys.dm_exec_query_stats AS qs  
	cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st  
WHERE st.TEXT not like '%select * from dbo.Orders%' and st.text  like '%SELECT%'
ORDER BY TotalDuration

Wydajnosc_zapytan_SQL_060
Teraz wyniki wyglądają zupełnie inaczej i pokazują całą prawdę o wcześniejszych próbach oszacowania wydajności.

Olbrzymie przekłamanie poprzedniej metody, widoczne jest w przypadku kwerendy z UDF (pozycja 4). Funkcja skalarna (pozycja 3) została wykonana dla każdego rekordu tabeli dbo.Orders, czyli 830 razy! Koszt tego zapytania (odczyty o dwa rzędy większe niż skorelowanego, czas ok 50 razy gorszy) w porównaniu do dwóch pierwszych kwerend jest kompletnie nie do przyjęcia – a miało być tak pięknie…

Pomiar wydajności zapytania SQL, najlepiej wykonać na kilkudziesięciu / kilkuset powtórzeniach. Analizując takie przebiegi w Management Studio, warto zaznaczyć opcję wykonania zapytania bez zwracania wyników (Tools>Options) :
Wydajnosc_zapytan_SQL_070
Poniżej wynik dla GO 100, naszej trójki konkurentów – zwycięzcą, pod względem czasu wykonania, jest Simple JOIN pomimo sporej liczby logicznych odczytów :
Wydajnosc_zapytan_SQL_080

Extended Events

Pewnym minusem, korzystania z DMV/DMF, jest ulotność statystyk. Widoki te pokazują aktualny status obiektów przechowywanych w pamięci. Co zrobić gdybyśmy zechcieli testować 100 razy pełen cykl kompilacji i wykonania kwerendy ? Po każdym

DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS;

w widokach nie mamy przecież czego szukać. Poza tym nie mamy w pełni kontroli nad tym co jest przechowywane w buforach a co nie. Rozwiązania są dwa – Extended Events (XE) – czyli najlepsze pod kątem wydajności mechanizmy śledzenia w SQL Server oraz Profiler.

Zbiór zdarzeń, które możemy przechwytywać za pomocą XE jest bardzo szeroki. W naszym scenariuszu będziemy analizować zdarzenia sqlserver.sql_statement_completed. Tworzenie sesji XE do przechwytywania potrzebnych statystyk wydajnościowych zapytań :

-- Tworzymy sesję XE
CREATE EVENT SESSION perftest ON SERVER 
ADD EVENT sqlserver.sql_statement_completed 
ADD TARGET package0.event_file(SET filename=N'D:\perftest\perftest.xel',
          max_file_size=(2),max_rollover_files=(100)) 
WITH (  MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS, 
             MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB, 
             MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON); 
 
ALTER EVENT SESSION perftest ON SERVER STATE = START;

Żeby było ciekawiej, dorzućmy do zawodów jeszcze dwie inne wersje kwerendy, zwracających ten sam, poprawny rezultat.

Słabość funkcji skalarnych, można pokonać, przepisując je na tabelaryczne (czasem ma to sens) i wywołując je poprzez CROSS JOIN (Q4). Wiele problemów rozwiązać można również za pomocą funkcji okna OVER() – będzie to ostatnie z testowanych rozwiązań kwerendy (Q5).

Wykonajmy więc wszystkie zapytania, które będą już przechwytywane przez aktywną sesję XE :

DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT
 
select * from dbo.Orders
GO
 
-- Q1 - prosty join 
SELECT /* Simple Join */ o.CustomerId, o.OrderId  , o.ShipCountry
FROM dbo.Orders o INNER JOIN 
		( 
		SELECT CustomerID, MAX(OrderID) AS OrderID 
		FROM dbo.Orders 
		GROUP BY CustomerID
		) AS MaxOrd   ON  o.OrderID = MaxOrd.OrderID;
 
-- Q2 - podzapytanie skorelowane
SELECT /* Correlated SubQ */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID = (
          SELECT MAX(OrderID) 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o1.CustomerID 
                );
 
-- Q3 - podzapytanie z funkcją skalarną
SELECT /* UDF */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders as o1
WHERE  OrderID =  dbo.GetMax(CustomerId);
 
-- Q4 z CROSS APPLY (analogia TVF)
SELECT /* CROSS APPLY */ CustomerId, OrderId, ShipCountry
FROM dbo.Orders o Cross APPLY ( SELECT MAX(OrderID) as MaxOrderID 
          FROM dbo.Orders as O2 
          WHERE CustomerID = o.CustomerID ) t
where o.OrderID = MaxOrderID;
 
-- Q5 Funkcja okna z ROW_NUMBER()
SELECT /* OVER() */ CustomerId, OrderId, ShipCountry
FROM ( 
        SELECT OrderId, CustomerID, ShipCountry,
             ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderID DESC) AS MaxOrder
        FROM dbo.Orders ) AS a
WHERE MaxOrder = 1 ;
GO 100
 
DROP EVENT SESSION perftest ON SERVER;

Pozostaje nam zajrzeć do pliku ze zdarzeniami – w moim przykładzie zapisanym na D:\perftest\ :

SELECT statement, AVG(duration) as AVG_Duration , 
	AVG(cpu_time) as AVG_CPU_time,
	AVG(logical_reads) as AVG_LR,
	AVG(physical_reads) as AVG_PR,
	SUM(cpu_time) as SUM_CPU_TIME,
	SUM(duration) as SUM_DURATION,
	SUM(logical_reads) as SUM_LR,
	SUM(physical_reads) as SUM_PR,
	count(*) as ExecCnt
FROM   ( 
       SELECT  duration=e.event_data_XML.value('(//data[@name="duration"]/value)[1]','int') 
       ,cpu_time=e.event_data_XML.value('(//data[@name="cpu_time"]/value)[1]','int') 
       ,physical_reads=e.event_data_XML.value('(//data[@name="physical_reads"]/value)[1]','int') 
       ,logical_reads=e.event_data_XML.value('(//data[@name="logical_reads"]/value)[1]','int') 
       ,writes=e.event_data_XML.value('(//data[@name="writes"]/value)[1]','int') 
       ,statement=e.event_data_XML.value('(//data[@name="statement"]/value)[1]','nvarchar(max)') 
       ,TIMESTAMP=e.event_data_XML.value('(//@timestamp)[1]','datetime2(7)') 
        FROM    ( 
               SELECT CAST(event_data AS XML) AS event_data_XML 
               FROM sys.fn_xe_file_target_read_file(N'D:\perftest\perftest*.xel', NULL, NULL, NULL) 
               )e 
       )q 
WHERE  q.[statement] LIKE '%SELECT /%' 
GROUP BY statement
ORDER BY SUM_DURATION

Wydajnosc_zapytan_SQL_090
W teście wykorzystującym istniejące plany wykonania (bez każdorazowego czyszczenia pamięci), zwyciężyła kwerenda oparta o prostego JOINa. Pozostałe rozwiązania, poza funkcją skalarną (totalna porażka), oferują porównywalną wydajność (total duration). Wśród nich, wyróżnia się zapytanie z funkcją okna, która najmniej obciąża badane zasoby serwera (CPU + reads).

SQL Server Profiler

Ostatnim narzędziem do pomiaru wydajności zapytań, dostępnym bezpośrednio w SQL Server jest Profiler. Zostawiłem go na koniec, ponieważ jest to dość historyczny tool i nie jest zalecanym narzędziem do testowania obciążenia w środowisku komercyjnym. W odróżnieniu do Extended Events, które są uruchamiane “inside” silnika SQL Server, Profiler jest narzędziem “zewnętrznym”, którego działanie wprowadzać może spory narzut na wydajność monitorowanej instancji. Z pewnością jest to bardzo przyjemny w użyciu graficzny tool, za pomocą którego, możemy przechwytywać zdarzenia podobnie jak za pomocą XE i sprawdza się znakomicie w środowisku developerskim.

Na początek tworzymy nową sesję – plik śladu (można go potem odtwarzać lub np. korelować z perfmonem), definiując interesujące nas zdarzenia :
Wydajnosc_zapytan_SQL_10
Uruchomię teraz naszą testową piątkę zapytań. Poniżej efekt ich przechwycenia (LIVE) :
Wydajnosc_zapytan_SQL_11


Podsumowanie

Badanie wydajności zapytań SQL, możemy zrealizować na wiele sposobów. Każda z zaprezentowanych metod ma swoje zalety i wady. Warto poznać je wszystkie, aby wybrać odpowiednią do konkretnej sytuacji. Polecam w środowiskach komercyjnych widoki dynamiczne i XE z uwagi na wiarygodność i ich “lekkość”.

Temat optymalizacji i pomiarów wydajności zapytań jest znacznie szerszy. Nie zawsze kwerenda działająca świetnie dla tysięcy rekordów, jest również najlepsza w przypadku bardzo dużych tabel (złożoność obliczeniowa zapytań), ale o tym w następnym artykule.

Podstawowe aspekty wydajnościowe zapytań SQL

http://www.dreamstime.com/stock-photos-stock-market-chart-computer-screen-schows-financial-graph-image37475373Poznając możliwości języka SQL, bardzo szybko dochodzimy do wniosku, że identyczny zbiór wynikowy można uzyskać na wiele różnych sposobów. Jest to moim zdaniem jedna z fajniejszych cech tego języka. Podobnie jak w programowaniu czy ogólnie algorytmice, stawiane jest wyzwanie – osiągnąć cel najniższym kosztem.

Artykuł ten jest wprowadzeniem do zagadnień związanych z optymalizacją zapytań. Temat ten jest tak obszerny, że nie sposób umieścić w krótkim rozdziale tego kursu wszystkich (czy nawet najważniejszych) aspektów związanych z badaniem wydajności. Nie mniej jednak, chciałbym Tobie przybliżyć podstawy, związane z fizycznym wykonywaniem zapytań w SQL Server. Omówić metodologię pomiarów oraz narzędzi związanych z badaniem i porównywaniem wydajności zapytań.

Na początek kilka słów na temat przetwarzania zapytań w SQL Server, buforowania planów i wyjaśnienie w jaki sposób przygotować środowisko do testów i badania wydajności zapytań.


Wykonywanie zapytań w SQL Server

Sam proces wykonywania kwerendy jest ostatnim z kilku etapów przetwarzania. Każde nowe zapytanie wysłane do serwera bazy danych jest realizowane w następujących krokach :
Przetwarzanie zapytania SQL

  1. Sprawdzenie poprawności syntaktycznej (parsowanie). Na tym etapie weryfikacji podlega tylko sama składnia, czy zachowany jest szyk słów kluczowych, poprawność użytych znaków (literówki w słowach kluczowych), nawiasów, cudzysłowów itd.
  2. Algebraizacja – wieloetapowe przetwarzanie wstępne. Jeśli kwerenda jest poprawna pod kątem składniowym, weryfikacji podlegają wszystkie obiekty (tabele, kolumny czy funkcje) do których się odwołujemy – rozwiązywanie nazw. Sprawdzane są także szczegółowo uprawnienia a także wykonywana jest analiza pod kątem późniejszych operacji (np. grupowania) – tworzone jest drzewo wyrażeń.
  3. Optymalizacja – przygotowanie planów wykonania zapytania i wybór optymalnego. Do każdego celu można dojść różnymi drogami. Szukając nazwiska w książce adresowej, można posłużyć się indeksem lub przekartkować całość. W zależności od jakości indeksu, może okazać się, że szybciej znajdziemy poszukiwany rekord przeglądając całą książkę. Podobnie jest w SQL. Przygotowywane są różne plany wykonania (ale nie wszystkie możliwe, bo to mogłoby zająć znacznie więcej czasu niż wykonanie nawet najgorszego z nich). Następnie wybierany jest najlepszy spośród znalezionych (może to być pierwszy lepszy, jeśli spełni określone kryteria optymalizatora). Wybrany plan wykonania jest umieszczany w specjalnym buforze – do ewentualnego wykorzystanie w przyszłości. Dzięki temu ponowne wykonanie tej samej kwerendy, może zostać wykonane znacznie szybciej, bo z pominięciem wcześniejszych kroków.
  4. Wykonanie zapytania zgodnie z wybranym planem, fizyczne i logiczne odczytywanie danych.
  5. Zwrócenie rezultatu

Jak widać droga od zadania pytania do otrzymania odpowiedzi jest długa i w praktyce pochłaniać może sporo czasu. Z punktu widzenia wydajności serwera, bardzo ważne jest, aby czas i zasoby poświęcone na utworzenia planu wykonania, nie były zmarnowane. Dlatego istotne jest buforowanie planów i ponowne ich wykorzystanie w momencie pojawienia się tego samego (lub podobnego, ale o tym później) zapytania.


Buforowanie planów wykonania

Temat buforowania planów i umiejętność ich wielokrotnego wykorzystania, jest szczególnie istotna dla programistów, aby świadomie optymalizować obciążenie serwera. Jeśli interesują Cię aspekty wydajnościowe, ważne aby zrozumieć w jaki sposób badać wydajność całego procesu.

Porównując dwie kwerendy, chcemy zazwyczaj mierzyć pełen czas ich „kompilacji” – od wysłania zapytania do otrzymania wyniku, a nie tylko kolejne wywoływania z pamięci podręcznej już przetworzonych zapytań. W dalszej części pokażę, na czym polega buforowanie planów i dlaczego w procesie pomiarowym, należy czyścić pamięć podręczna i bufory SQL Server.

Przygotujmy środowisko do testów. Użyjemy do tego dwóch procedur : DBCC FREEPROCACHE oraz DBCC DROPCLEANBUFFERS związanych z czyszczeniem pamięci podręcznej z planów i buforów (dane). Dodatkowo, można wywołać jawny CHECKPOINT, czyli zrzucenie dirty-pages z pamięci na dysk, aby zapobiec wystąpieniu tego cyklicznego zdarzenia w trakcie wykonywania kwerendy, co mogłoby wypaczyć ewentualne wynik porównań. W dalszej części, będę zawsze wykonywał poniższy tercet, chcąc rozpocząć dowolny test w czystym środowisku.

USE NORTHWIND
GO
 
-- Uwaga ! używaj tylko w celach edukacyjnych na serwerze testowym :)
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT
GO

W tym momencie stan pamięci / buforów naszej instancji SQL Server, jest zupełnie wyczyszczony. Możemy zacząć testować. Wykonajmy pierwsze zapytanie i od razu sprawdźmy, co znajduje się w cache :

SELECT * FROM dbo.Orders
GO
 
-- sprawdzenie informacji o istniejących planach w cache
SELECT qt.TEXT as SQL_Query, usecounts, size_in_bytes ,
	cacheobjtype, objtype
FROM sys.dm_exec_cached_plans p 
	CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) qt
WHERE qt.TEXT not like '%dm_exec%'

Buforowanie01
Za pomocą widoku systemowego (DMV) sys.dm_exec_cached_plans, możemy zajrzeć do bufora planów. Widać w nim nasze zapytanie, z kilkoma podstawowymi informacjami m.in. o rozmiarze skompilowanego planu i liczniku wykonania. Teraz wykonajmy jeszcze kilka, niemal identycznych kwerend :

SELECT * FROM dbo.Orders
GO 3
 
SELECT * FROM Orders
GO 
 
SELECT *  FROM Orders
GO 
 
SELECT qt.TEXT as SQL_Query, usecounts, size_in_bytes ,
	cacheobjtype, objtype
FROM sys.dm_exec_cached_plans p 
	CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) qt
WHERE qt.TEXT like '%Orders%' 
	and qt.TEXT not like '%dm_exec %'

Buforowanie02
Powyższy wynik pokazuje charakterystyczną cechę buforowania planów wykonania. Każda unikalna kwerenda, posiada swój własny unikalny plan (i zajmuje konkretną liczbę bajtów pamięci). Nawet jeśli logicznie jest identyczna a różni się nieznacznie syntaktycznie, np. brakiem schematu (druga) lub tylko dodatkową spacją (ostatnie zapytanie). Każda z nich przeszła pełen proces „kompilacji” i optymalizacji. Wyjątkiem jest kwerenda wyświetlana w wynikach jako ostatnia – ją wykonałem w sumie 4 razy, w oparciu o ten sam plan, ponieważ udało się dopasować w 100% zapytanie do istniejącego, w pamięci podręcznej.

Naturalnie nasuwa się od razu pytanie, co w sytuacji gdy wykonujemy kwerendy z warunkami, czyli ogólnie mówiąc parametryzowane.


Wielokrotne wykorzystanie tych samych planów wykonania

Weźmy teraz pod uwagę dwa zapytania, które ze swej natury muszą być różne, a dla których plan wykonania z pewnością jest taki sam :

DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS;
CHECKPOINT; 
GO
 
SELECT * FROM dbo.Orders WHERE CustomerID = 'ERNSH'
GO
SELECT * FROM dbo.Orders WHERE CustomerID = 'FRANK'
GO
 
SELECT qt.TEXT as SQL_Query, usecounts, size_in_bytes ,
	cacheobjtype, objtype
FROM sys.dm_exec_cached_plans p 
	CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) qt
WHERE qt.TEXT like '%Orders%' 
	and qt.TEXT not like '%dm_exec%'

Buforowanie03
Znów mamy dwa osobne plany, każda z tych kwerend przeszła wszystkie kroki w procesie przetwarzania zapytania. Może być to niepokojące, szczególnie gdy tego typu zapytań są setki czy tysiące. Czy za każdym razem serwer przygotowuje pełną ścieżkę dla tak podobnych zapytań?

Rozwiązaniem problemu parametryzacji jest tzw. kontekst wykonania zapytania, który jest bezpośrednio powiązany z planem. Kwerendy sparametryzowane, spotykamy najczęściej w procedurach składowanych, funkcjach tabelarycznych. Wykorzystują one zamiast czystych zmiennych – parametry, dlatego z punktu widzenia silnika są identyczne i możliwe jest dla nich ponowne użycie już utworzonego planu.

Przeanalizujmy teraz wywołanie naszej wcześniejszej kwerendy, ale zapisanej trochę inaczej (z wykorzystaniem procedury sp_executesql, aby zadać prawdziwie sparametryzowane pytanie) :

DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS; 
CHECKPOINT;
 
EXEC sp_executesql
  N'SELECT * FROM dbo.Orders WHERE CustomerID = @CustID',
  N'@CustID CHAR(5)', N'ERNSH'
 
EXEC sp_executesql
  N'SELECT * FROM dbo.Orders WHERE CustomerID = @CustID',
  N'@CustID CHAR(5)', N'FRANK'
 
SELECT qt.TEXT as SQL_Query, usecounts, size_in_bytes ,
	cacheobjtype, objtype
FROM sys.dm_exec_cached_plans p 
	CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) qt
WHERE   qt.TEXT like '%Orders%' 
	and qt.TEXT not like '%dm_exec%'

Buforowanie04

Różnica jest widoczna na pierwszy rzut oka. Dla każdej z nich, plan zapytania jest ten sam – zawiera parametr @CustID. Co więcej, widać licznik użycia = 2, czyli faktycznie druga kwerenda wykonana zostanie z pewnością szybciej niż pierwsza (skorzysta z istniejącego planu wykonania).


Buforowanie danych

SQL Server buforuje obiekty różnego typu, nie tylko plany zapytania. Od początku istnienia systemów bazodanowych, dostęp do zasobów dyskowych, operacje IO były najwolniejszym ogniwem i najczęstszym powodem problemów wydajnościowych. Dlatego silnik bazodanowy, stara się jak najrzadziej korzystać z fizycznych odczytów. Raz pobrane informacje (rekordy), są przechowywane tak długo jak to tylko możliwe w buforze. Statystyki odczytów są więc jednymi z dwóch głównych (obok czasu) wyznaczników jakości zapytania (im mniej tym lepiej).

Najprostszym sposobem na sprawdzenie statystyk odczytów jest włączenie zmiennej SET STATISTICS IO ON. Zróbmy prosty test :

DBCC DROPCLEANBUFFERS; -- czyszczenie pamięci podręcznej
 
SET STATISTICS IO ON
 
SELECT * FROM dbo.Orders WHERE CustomerID = 'FRANK'  
GO
 
SELECT * FROM dbo.Orders WHERE CustomerID = 'FRANK'  
GO

Buforowanie05
Wykonaliśmy dwa razy to samo zapytanie a statystyki odczytów są różne. W pierwszym wyniku, mamy oprócz odczytów logicznych (z pamięci operacyjnej) obecne także odczyty fizyczne (1+20). Trzeba było w końcu te dane najpierw przeczytać z dysku. Kolejne wykonanie tej samej kwerendy nie sięgania fizycznie do zasobów dyskowych – czyta wszystko z pamięci.

Warto od razu zaznaczyć, że informacje na temat statystyk prezentowane za pomocą tej opcji, bywają przekłamane i nie można w 100% na nich polegać. Opisuję ten problem szczegółowo w artykule na temat metodyki pomiaru wydajności zapytań za pomocą Extended Events, Profilera czy dynamicznych widoków systemowych.


Podsumowanie

Powyższe informacje miały na celu pokazanie, ogólnej koncepcji mechanizmów wykonywania zapytań SQL przez silnik bazodanowy. Podczas testowania zapytań, konieczne jest czyszczenie pamięci podręcznej z planów oraz innych obiektów (danych), aby metrologia procesu porównywania wydajności miała sens.


Źródła MSDN :
http://technet.microsoft.com/en-us/library/ms190623(v=sql.105).aspx
http://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx