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.

Konfiguracja Database Mail w SQL Server

Mail_smallWysyłanie powiadomień mailowych z poziomu serwera bazodanowego, jest niezwykle przydatną funkcjonalnością. Typowe zastosowanie to automatyczne przekazywanie informacji o statusie zadań administracyjnych, procesów utrzymaniowych czy ETL (zazwyczaj błędach lub ostrzeżeniach).

Możesz je wykorzystać równie dobrze w typowych aplikacjach klienckich. Informować użytkowników o pojawiających się zdarzeniach np. zleceniach, zmianach statusów czy przekroczeniu określonych wskaźników, bezpośrednio z poziomu SQL Server.

W artykule tym, znajdziesz opis koncepcji Database Mail w SQL Server. Zaprezentuję także przykładową konfigurację za pomocą procedur wbudowanych. Co istotne, Database Mail jest dostępny także w darmowej wersji SQL Server Express, choć bez graficznego wizzarda i widocznej osobnej gałęzi obiektów w Management Studio.


Architektura Database Mail

Konfiguracja, widoki systemowe oraz procedury składowane dotyczące Database Mail, znajdują się są w bazie systemowej MSDB. Działanie oparte jest na usłudze Brokera (Service Broker), czyli wbudowanemu w SQL Server mechanizmowi do przesyłania wiadomości, kolejkowania i obsługi zdarzeń.

Wysyłanie maili, z punktu widzenia użytkownika, sprowadza się do wywołania procedury składowanej sp_send_dbmail, która wrzuca wiadomość do kolejki. Pojawienie się w niej nowego rekordu, wyzwala proces odpowiedzialny za fizyczne wysłanie maila. Tym procesem jest zewnętrzny program DatabaseMail.exe, uruchamiany automatycznie przez proces SQL Server.

Z konfiguracją Database Mail, związane są obiekty kont pocztowych i profili – przedstawione na poniższym schemacie :

Database_Mail_00

Profile – mogą mieć przypisane jedno lub wiele kont pocztowych z których będą wysyłane wiadomości. Jest to proste zwiększenie niezawodności, na wypadek gdy podstawowy serwer (konto) SMTP jest nie dostępny.

W momencie przypisania konta do profilu (procedura sysmail_add_profileaccount_sp), określany jest parametr sequence_number (priorytet) zgodnie z którym, następuje próba wysłania wiadomości. Najpierw przez konto z najniższym sequence numer. W przypadku niepowodzenia, podejmowana jest próba wysłania przez kolejne.

Konta pocztowe, mogą być przypisane do jednego lub wielu profili (współdzielone) np. konto backupowe.

Każdemu użytkownikowi, możemy nadać uprawnienia (proc. sysmail_add_principalprofile_sp
) do wielu profili pocztowych. Jeden z nich, może być określony jako domyślny – wtedy nie trzeba będzie podawać jawnie nazwy profilu w momencie wywoływania procedury wysyłającej maila. Nadanie uprawnień do profilu nie jest jednoznaczne z tym, że już użytkownik będzie mógł wysyłać maile. Musi mieć także uprawnienia do wykonywania procedury msdb.dbo.sp_send_dbmail czyli dostęp do bazy MSDB ze stosownymi uprawnieniami.

Do zarządzania uprawnieniami w tym zakresie, istnieje dedykowana rola w bazie MSDB DatabaseMailUserRole. Najwygodniej jest dopisać do niej każdego użytkownika (grupę), która ma mieć możliwość wysyłania i dostępu do widoków związanych z mailami (np. sysmail_allitems). Widoki te są o tyle bezpieczne, że użytkownik będzie widział tylko te wiadomości, których jest autorem.

Wprowadzona architektura zależności pomiędzy kontami, profilami a użytkownikami daje elastyczność i jest łatwo skalowalna.


Konfiguracja Database Mail w SQL Server

Pierwszym krokiem, jest przygotowanie instancji SQL Server, czyli włączenie obsługi Database Mail.

sp_configure 'show advanced options',1
reconfigure
go
 
sp_configure 'Database Mail XPs',1;
reconfigure
go

Jak już wspominałem, Database Mail bazuje na usłudze Brokera i musi on być również aktywny w bazie MSDB. Jego status możemy sprawdzić za pomocą :

select name ,   is_broker_enabled 
from sys.databases
where name = 'msdb'

Database_Mail_01
W przypadku gdy nie jest aktywny (0), należy go włączyć :

USE master
GO
ALTER DATABASE msdb SET  ENABLE_BROKER WITH NO_WAIT
GO

Teraz, możemy zabrać się za właściwą konfigurację profili i skojarzonych z nimi kont pocztowych. Od razu podpowiem, że większość problemów z wysyłaniem maili, jest związane właśnie z błędami w tym kroku. Należy upewnić się co do parametrów konta SMTP , poprzez które chcemy wysyłać maila.

Na początek dodam dwa nowe konta – na serwerach home.pl oraz Gmail. Konfiguracja portów, konieczność uwierzytelniania SSL są z reguły identyczne dla większości providerów.

Use MSDB
GO
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'Gmail',
     @email_address = 'mojekonto@gmail.com',
     @display_name = 'Gmail Public', 
     @description = 'Konto Main', 
     @mailserver_name = 'smtp.gmail.com',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'mojekonto@gmail.com',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'SQLPEDIA',
     @email_address = 'adresemail@sqlpedia.pl',
     @display_name = 'backup', 
     @description = 'Konto Main', 
     @mailserver_name = 'domena.home.pl',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'adresemail@sqlpedia.pl',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
-- weryfikacja kroku
select * from sysmail_server

Kolejnym krokiem, jest utworzenie profili. Zróbmy dwa – jeden będzie wykorzystywany jako publiczny, drugi prywatny dla procesów utrzymaniowych.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Public',
@description = 'Dla wszystkich userów' ;
 
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Priv',
@description = 'Tylko admin' ;
 
-- weryfikacja kroku
select * from sysmail_profile

Database_Mail_02
Teraz trzeba powiązać konta pocztowe z profilami. Profil publiczny będzie posiadał jedno konto a prywatny dwa, podstawowe i backupowe.

-- Public
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Public', -- nazwa profilu
@account_name = 'Gmail',  -- nazwa konta
@sequence_number =1 ;
 
-- Priv
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'SQLPEDIA',
@sequence_number =1 ;
 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'Gmail',
@sequence_number =2 ;
 
-- weryfikacja kroku
select * from sysmail_profileaccount

Database_Mail_03

Ostatnią rzeczą która trzeba wykonać aby zwykły użytkownik mógł wysyłać maile, jest nadanie stosownych uprawnień. Nadajmy więc uprawnienia do profilu publicznego dla wszystkich (Principal_id = 0 to rola public w bazie MSDB).

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Public',
@principal_id = 0,
@is_default = 1 ;

Poza uprawnieniami do profilu, zwykły użytkownik musi mieć jeszcze dostęp do bazy MSDB oraz przypisanie do roli DatabaseMailUserRole

USE [msdb]
GO
CREATE USER [UserName] FOR LOGIN [UserName]
GO
USE [msdb]
GO
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [UserName]
GO

I to już wszystko. Teraz możemy na wysłać maila testowego.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Public',
@recipients = 'JakisAdresEmail@wp.pl',
@subject = 'SQL Server Databse Mail - Test',
@body = 'Dzieńdobry i dowidzenia ...';

Weryfikacja poprawności działania i typowe problemy

Do weryfikacji poprawności wysłania maili mamy dostępnych kilka widoków systemowych. Szczególnie użyteczne są dwa :

  • msdb.dbo.sysmail_allitems, pokazujący aktualny status i zawartość wiadomości w kolejce.
  • msdb.dbo.sysmail_event_log – tu znajdziemy szczegółowe informacje o błędach, odpowiedziach serwera SMTP.
USE msdb
go
 
SELECT sent_status, mailitem_id, profile_id, recipients,
	subject, body
FROM sysmail_allitems
ORDER BY mailitem_id desc
 
SELECT * FROM sysmail_event_log
ORDER BY log_id  desc

Database_Mail_04
W przypadku pojawienia się problemów z wysyłaniem wiadomości sprawdź przede wszystkim :

  1. Informację o błędach, odpowiedziach serwera w widoku sysmail_event_log.
  2. Wiem, że to banał ale literówki, nieprawidłowa konfiguracja konta to naprawdę najczęstsza przyczyna problemów (bywa że username to pełna nazwa adresu email ;)).
  3. Sprawdź czy usługa brokera jest aktywna.
  4. Sprawdź status kolejki – powinna być STARTED.

    EXECUTE msdb.dbo.sysmail_help_status_sp

    Jeśli nie jest, możesz uruchomić ją za pomocą

    EXECUTE msdb.dbo.sysmail_start_sp

  5. Ustawienia firewall lub co bardziej kłopotliwe – szczególnie w korporacjach, blokowanie portów pocztowych 587. Łatwo to zweryfikować, telnetując się na np. na port 578 serwera SMTP. Jeśli się nie powiedzie, to znaczy że ruch na dany port jest wycinany.
    Database_Mail_06
  6. Szczególne ustawienia serwerów pocztowych SMTP, np. GMAIL, w których podstawowa autentykacja jest traktowana jako niebezpieczna.

    W Gmail, wprowadzono standard Oauth 2.0 i jeśli chcemy korzystać z Basic Authentication musimy jawnie określić, aby z naszego konta mogły korzystać tzw. mniej bezpieczne aplikacje.
    Więcej na ten temat znajdziesz tutaj : https://support.google.com/accounts/answer/6010255?hl=en
    Akceptację aplikacji mniej bezpiecznych, możesz ustawić po zalogowaniu do GMail tutaj : https://www.google.com/settings/security/lesssecureapps

  7. Sprawdź kto jest ownerem bazy systemowej MSDB. Jeśli jakiś „zwykły” user – warto zmienić na SA :)

    select name, suser_sname(owner_sid) as Owner  
    from sys.databases
     
    USE msdb
    GO
     
    EXEC sp_changedbowner [sa]
    GO


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

Powyższy artykuł dotyczy wersji SQL Server 2008 R2 – 2014