SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

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

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

Wielokrotne grupowanie – GROUPING SETS, ROLLUP, CUBE

Tworząc raporty czy zestawienia bezpośrednio z bazy transakcyjnej, wchodzimy na teren analityki biznesowej BI (Business Intelligence) w zwykłej bazie OLTP (OnLine Transaction Processsing). W środowiskach komercyjnych, granica pomiędzy OLTP a bazami OLAP (OnLine Analytical Processing) jest czasem całkowicie zatarta. Zdarza się tak w sytuacji gdy po prostu nie ma wdrożonych, dedykowanych rozwiązań BI. Całość raportowania odbywa się wtedy wprost z bazy transakcyjnej.

Analityka biznesowa (raportowanie), cechuje się wieloma operacjami przekształcania danych i przekuwania ich na konkretną wiedzę. Takie zapytania bywają kosztowne jeśli chodzi o wydajność. Dlatego żeby nie obciążać systemu transakcyjnego, wykonywana są w osobnych strukturach. Jest to powód dla którego tworzone są hurtownie danych i wszelkiej maści rozwiązania BI.

W artykule tym opisuje rozszerzenia T-SQL pozwalające na wielokrotne grupowanie (wstęp do kostek analitycznych). Opisywane tu metody, bazują na zagadnieniach dotyczących grupowania danych i łączenia pionowego zbiorów, prezentowanych w tym kursie we wcześniejszych artykułach.


Wielokrotne grupowanie

W T-SQL mamy dostępnych kilka rozszerzeń składni, pozwalających na sięgnięcie do wiedzy analitycznej. Obok funkcji szeregujących i analitycznych, jednymi z ciekawszych są trzy funkcje pozwalające na wykonywanie wielokrotnych grupowań w zwykłej (pojedynczej) kwerendzie.

Wyobraźmy sobie taki scenariusz (działając na danych z bazy Northwind) :

Chcemy utworzyć raport, przedstawiający informacje o sprzedaży w latach 1997-1998 na kilku poziomach szczegółowości. Zagregowane dane na temat liczby zleceń, chcemy wyświetlić względem lat, kwartałów i na koniec całościowo (jako najwyższy poziom agregacji).

Wielokrotne_grupowanie_01

Tego typu rozbicie wielopoziomowej agregacji, można wykorzystać np. w aplikacji raportującej, w której mamy możliwość „drążenia danych”, czy po prostu w zwykłym raporcie. Powyższy przykład to nic innego jako fragment kostki analitycznej z kilkoma wymiarami czasu.

Znając już operacje na zbiorach (UNION) i możliwości grupowania (GROUP BY), mógłbyś to zadanie rozwiązać za pomocą tych elementarnych konstrukcji, np. w taki sposób :

USE Northwind
GO
 
-- Query1
SELECT 1 as Poziom, NULL as Rok, NULL as Kw , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
 
UNION
 
SELECT 2, Year(Orderdate) as Rok, NULL, count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate) 
 
UNION
 
SELECT 3,Year(Orderdate)  as Rok, DatePart(q,Orderdate), count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY Year(Orderdate)  , DatePart(q,Orderdate)
ORDER BY 2,1

Zaprezentowane powyżej rozwiązanie, łączenia wyników grupowań tej samej kwerendy w różnych kombinacjach, jest mało efektywne.

Z punktu widzenia przetwarzania są to trzy osobne kwerendy, które odpytują te same dane (dublujące się odczyty) i procesowane są niezależnie. Z pomocą przychodzą nam rozszerzenia T-SQL, pozwalające zrealizować tego typu scenariusze znacznie prościej i wydajniej.


ROLLUP

ROLLUP rozszerza funkcjonalność klauzuli GROUP BY, o możliwość tworzenia tzw. kostek analitycznych połówkowych. Funkcja ta, przyjmuje jako parametry, analogicznie jak w zwykłym grupowaniu, atrybuty (nazwy kolumny) tabel wejściowych lub ich przekształcenia.

Jeśli zastosujesz ROLLUP (a,b,c), wykonane zostaną grupowania dla kolejnych kombinacji atrybutów :

  • GROUP BY (a,b,c)
  • GROUP BY (a,b)
  • GROUP BY (a)
  • oraz po całości, czyli GROUP BY () – co w praktyce zapisujemy bez jawnego grupowania (wszystkie kolumny tworzą część danych surowych)

ROLLUP to równoważnik realizacji N+1 grupowań. W przypadku podania trzech (a,b,c) atrybutów, zostaną wykonane 3 + 1 = 4 operacje. Co warto podkreślić, wydajnościowo będzie to znacznie bardziej efektywne zapytanie, niż osobne grupowania i łączenia zbiorów za pomocą UNION, pokazane w poprzednim przykładzie (Query1).

Zobaczmy teraz rozwiązanie naszego scenariusza, czyli na trzech poziomach za pomocą ROLLUP :

-- Query2
SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP( Year(Orderdate)  , DatePart(q,Orderdate) )

Wielokrotne_grupowanie_02

Sam zapis kwerendy, jak również jej plan wykonania (Query2) przy tym podejściu jest znacznie prostszy i co ważniejsze – bardziej efektywny. Wystarczy zerknąć na porównanie wydajności zapytań :

Wielokrotne_grupowanie_03

a także statystyki odczytów :

Wielokrotne_grupowanie_04

Odnosząc się do poprzedniego przykładu –3 krotnie została zmniejszona liczba odczytów (tu też widać, że każda kwerenda w UNION, była realizowana niezależnie).

Sam wynik może jeszcze nie być do końca satysfakcjonujący (brakuje informacji o poziomach agregacji), ale tym zajmiemy się w dalszej części tego artykułu – przy okazji omówienia funkcji GROUPING_ID().


CUBE

Drugą funkcjonalnością wielokrotnego grupowanie to CUBE czyli pełna kostka analityczna. Stosujemy ją podobnie jak ROLLUP w poleceniu GROUP BY. Jej działanie to wykonanie grupowania we wszystkich wymiarach. Czyli jeśli podamy 3 atrybuty, to wykonanych zostanie 2^3 = 8 operacji.

Chciałbym przy okazji przypomnieć, jedną z podstawowych zasad pisania zapytań. Zawsze pobieramy tylko takie dane, jakie w danym momencie potrzebujemy. CUBE wykonuje dla nas 2^N-tej grupowań – to kosztuje. Odnieśmy się do naszego przykładu i zamieńmy ROLLUP na CUBE :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY CUBE(Year(Orderdate)  , DatePart(q,Orderdate))

Wielokrotne_grupowanie_05

Dla dwóch atrybutów w CUBE(), wykonane zostały 4 grupowania. W porównaniu do ROLLUP, pojawił się dodatkowy poziom, tylko po kolumnie Q – kwartały. Niezależnie od roku, mamy informacje o całkowitej liczbie zleceń w kwartałach.

W praktyce, CUBE stosujemy w procesach ETL, rzadko w zwykłych widokach czy zapytaniach, właśnie ze względu na ilość grupowań (pełna kostka).


GROUPING SETS

Na koniec zostawiłem wisienkę na torcie, czyli najbardziej elastyczny i chyba najczęściej wykorzystywany w praktyce sposób na wielokrotne grupowanie.

Za pomocą GROUPING SETS możemy określić konkretne poziomy grupowań. ROLLUP i CUBE opisane wcześniej miały z góry narzuconą liczebność grup. W tym przypadku, możemy jawnie określić zbiory atrybutów po których wykonamy agregacje. Jest to więc także bardziej wydajna, bo oszczędna metoda, gdy nie potrzebujemy tylu wymiarów, ile dają dwie poprzednie metody. Z tego też powodu, jest chętnie stosowana na przykład w widokach, czy zwykłych kwerendach.

Odpowiednikiem ROLLUP i rozwiązaniem naszego scenariusza za pomocą GROUPING SETS będzie poniższa kwerenda :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(),
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))
 
)
ORDER BY Rok, Q

Sama struktura jest intuicyjna i wymaga tylko określenia zbiorów grupujących. Będą to atrybuty tabel wejściowych lub ich przekształcenia, analogicznie jak w zwykłym grupowaniu, po których będą wykonywane kolejne agregacje.

Załóżmy, że nie interesuje nas grupowanie podsumowujące całą sprzedaż w zadanym okresie (po całości), ale chcemy wyświetlić tylko raport na dwóch poziomach szczegółowości . Zagregowane dane na temat ilości zamówień w poszczególnych latach i kwartałach.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY GROUPING SETS
(
	(Year(Orderdate)) ,
	(Year(Orderdate)  , DatePart(q,Orderdate))
 
)
ORDER BY Rok, Q

Wielokrotne_grupowanie_06

W takim scenariuszu, najlepiej właśnie użyć GROUPING SETS – zgodnie z zasadą, pobierania tylko takich danych jakie są nam potrzebne.


Funkcje GROUPING() oraz GROUPING_ID()

Z przedstawionymi powyżej trzema metodami grupowania wielokrotnego, skojarzone są dwie specjalne funkcje skalarne. W grupowaniu wielokrotnym, nie mamy jawnie podanej informacji, na jakim poziomie dana operacja się odbywa. Za pomocą tych funkcji, możemy zidentyfikować, czy dana kolumna, tworzy sekcję grupującą czy nie. Jest to szczególnie przydatne, w ostatecznym sortowaniu wyniku, aby np. otrzymać taki raport jak w pierwszym przykładzie tego artykułu.

Funkcja GROUPING(), przyjmuje jako parametr, dowolny atrybut (zazwyczaj nazwę kolumny), używany w grupowaniu. Zwraca wartość 0 lub 1, w zależności od tego, czy dana kolumna, wchodzi w skład sekcji grupującej (0) czy nie(1). Trochę to na odwrót w przyjętej powszechnie logice, ale można się przyzwyczaić.

Zobaczmy jej działanie na przykładzie :

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 
 
 
	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING( Year(Orderdate) ) +  GROUPING( DatePart(q,Orderdate) ) as Poziom 
 
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, Poziom DESC

Wielokrotne_grupowanie_07

Na najwyższym poziome grupowania, czyli bez wnikania w wartości lat i kwartałów, zarówno atrybut YEAR(OrderDate) jak i DATEPART(q,OrderDate) nie tworzą sekcji grupującej. Funkcja GROUPING() dla tych atrybutów zwraca wartość 1.

Analogicznie w wierszu drugim i siódmym, mamy grupowanie tylko po latach, bez wnikania w wartości kwartałów. Jest to odpowiednik GROUP BY YEAR(OrderDate). Wartość GROUPING(YEAR(OrderDate)) będzie w tym przypadku 0, ponieważ ten atrybut tworzy sekcję grupującą.

Jak widać na tym przykładzie, za pomocą GROUPING(), możemy obliczyć wartość identyfikującą poziom grupowania.

Drugą funkcją, która już bez zbędnych ceregieli wyznaczy nam od razu identyfikator, jest GROUPING_ID().

Wynik takiej funkcji jest zawsze w postaci bitowej zamienionej na dziesiętną. Poniżej przykład obliczania jej wartości dla ROLLUP ( a, b, c ).

Wielokrotne_grupowanie_08

Odnieśmy się na koniec do naszego przykładu. Będziemy mieli dwa atrybuty grupujące, zatem wartości GROUPING_ID, będzie dla ROLLUP (a , b) zwracała wartość 0, 1 oraz 3.

SELECT Year(Orderdate)  as Rok, DatePart(q,Orderdate)  as Q , count(OrderId) as OrderQty , 
 
 
	GROUPING( Year(Orderdate) ) as [Grupowanie po roku],
	GROUPING( DatePart(q,Orderdate) )  as [Grupowanie po kwartale],
	GROUPING_ID( Year(Orderdate) ,  DatePart(q,Orderdate) ) as PoziomBinarny
 
FROM dbo.Orders
WHERE YEAR(OrderDate) between  1997 and 1998
GROUP BY ROLLUP(Year(Orderdate)  , DatePart(q,Orderdate))
ORDER BY Rok, PoziomBinarny DESC

Wielokrotne_grupowanie_09


Podsumowanie

Wielokrotne grupowanie za pomocą GROUPING SETS, ROLLUP I CUBE sprawdza się szczególnie w procesach ETL (Extract, Transform and Load). Rozszerza także możliwości zwykłych kwerend, skracając ich zapis i poprawiając wydajność w porównaniu do tradycyjnych metod wykorzystujących pojedyncze grupowanie i łączenie zbiorów za pomocą UNION.

Zapytania rozproszone i zewnętrzne źródła danych

W SQL Server mamy dostępnych kilka metod, dających nam dostęp do danych zewnętrznych.
W artykule tym, przedstawiam możliwości funkcji OPENQUERY, za pomocą której możemy tworzyć zapytania „Ad Hoc” (spontanicznie) do innych zasobów.

Zaprezentuję także metody wykorzystujące obiekty serwerów powiązanych (Linked Servers). Te z kolei stosujemy, aby utworzyć „stałe” powiązanie z innymi zasobami i w wygodny sposób, regularnie sięgać do zewnętrznych źródeł.


Zapytania rozproszone (Distributed Queries)

Komunikacja w środowiskach rozproszonych odbywa się zawsze w oparciu o interfejs pośredniczący. Standardem są sterowniki ODBC (Open DataBase Connectivity), za pomocą których możemy łączyć się z innymi bazami danych. Każdy producent serwera bazodanowego, udostępnia zazwyczaj w pakiecie klienckim wszystkie wymagane komponenty. Jeśli masz potrzebę łączenia się np. z bazą MySQL czy Oracle, powinieneś zainstalować właściwe dla danego dostawcy biblioteki.

Microsoft, od lat rozwijał równolegle własną koncepcje warstwy pośredniczącej – tzw. dostawców OLE DB (Object Linking and Embedding Database). Biblioteki te, rozszerzają możliwości ODBC i dotyczą połączeń nie tylko do serwerów bazodanowych ale także dostępu do zasobów plikowych (Excel, Access czy nawet zwykłych, płaskich plików tekstowych).

Jeśli chodzi o komunikację z SQL Server, w wersjach 2005-2012, to właśnie dostęp za pośrednictwem Native Client OLE DB był sugerowaną (szybszą, o większych możliwościach) metodą dostępu do danych. Sterowniki Native Client, zawierają w sobie bibliotekę OLE DB oraz ODBC. Dla wielu z pewnością dużym zaskoczeniem, był nagły zwrot w kierunku standardu ODBC w SQL Server 2014. Native Client 11 OLE DB z SQL Server 2012 – jest ostatnim w tej architekturze sterownikiem (więcej na ten temat, możesz przeczytać nas stronach Microsoft dot. kierunku rozwoju oraz oficjalnego powrotu do standardu ODBC)

Komunikacja, pomiędzy klientami, instancją SQL Server (do wersji 11) i środowiskami rozproszonymi odbywa się za pośrednictwem OLE DB.
SQL_SERVER_DISTRIBUTED_QUERIES


Funkcja OPENROWSET

Za pomocą funkcji OPENROWSET, możemy tworzyć zapytania rozproszone (Distributed Queries), odpytujące praktycznie dowolne zbiory zewnętrzne. Jedynym ograniczeniem jest dostępność na serwerze właściwych bibliotek OLE DB lub sterowników ODBC. Funkcja OPENROWSET umożliwia nie tylko odpytywanie zasobów, ale również pozwala na wykonywanie za jej pośrednictwem poleceń typu DML (Insert, Update, Delete). Ponieważ jest to funkcja tabelaryczna, stosować możemy ją analogicznie jak zwykłą tabelę (np. łącząc ją z innymi zbiorami).

Wymagania początkowe – konfiguracja serwera

Warunkiem koniecznym na wykorzystanie tej funkcji, jest włączenie na poziomie serwera, możliwości pisania zapytania rozproszonych (Ad Hoc Distributed Queries). To zadanie wykonamy za pomocą instrukcji :

exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
 
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE WITH OVERRIDE;
GO

Od tego momentu, możemy tworzyć zapytania „Ad hoc”, korzystające z OPENROWSET. W dalszej części zaprezentuję typowe przykłady zastosowań.

OPENROWSET w praktyce

Odpytywanie źródeł zewnętrznych za pomocą tej funkcji, wymaga podania kilku parametrów. Nie będę omawiać tu wszystkich możliwych opcji, zwrócę uwagę tylko na najważniejsze i najczęściej wykorzystywane w praktyce.

Typowe użycie funkcji OPENROWSET – odpytywanie innego SQL Servera :

SELECT *
FROM OPENROWSET(
         -- Provider_name
	'SQLNCLI',  				
 
         -- Connection String
	'SERVER=ServerZdalny\Instancja; TRUSTED_CONNECTION=yes;',
 
         -- Query
         'SELECT * FROM Northwind.dbo.Customers'
	) AS tabela

Pierwszym parametrem jest określenie dostawcy OLE DB (provider_name) za pośrednictwem którego wykonywane będzie połączenie.

Regułą jest stosowanie dedykowanego dostawcy dla określonego źródła. Jeśli nie jest on dostępny, możesz skorzystać z uniwersalnej biblioteki MSDASQL i połączenia via ODBC.

Listę dostawców OLE DB których masz zainstalowane na swoim serwerze, odnajdziesz nawigując w drzewie hierarchii obiektów w Management Studio :

OLE_DB_Providers

lub za pośrednictwem specjalnej procedury składowanej :

EXEC xp_enum_oledb_providers

OLE_DB_Providers2

W zależności z jakiego oprogramowania korzystasz, ta lista może się różnić.

Warto tutaj wspomnieć, że można utworzyć połączenia ze zdalnymi serwerami na różne sposoby. Już choćby z tej listy widać, że do SQL Server, można się odwołać za pomocą przynajmniej trzech dostawców – SQLOLEDB (ten jest stosowany domyślnie), SQLNCLI oraz uniwersalnego MSDASQL, wykorzystującego pośrednio połączenia ODBC. Wybór dostawcy, powinien być podyktowany konkretnymi potrzebami programistycznymi (więcej na temat różnic znajdziesz np. tutaj ).

Drugim parametrem jest connection string, czyli charakterystyczny dla danego dostawcy zbiór opcji, które określają warunki połączenia. W przypadku SQL Server, będzie to na pewno nazwa sieciowa serwera / instancji, tryb uwierzytelniania, czy wybór konkretnej bazy danych.

Trzecim parametrem jest nasza zdalna kwerenda. Tutaj od razu bardzo ważna uwaga – pamiętaj o tym, ze cały ten zbiór, zwracany przez to zdalne zapytanie, będzie transportowany do serwera z którego je wywołujesz. Może to być przyczyną problemów m.in. sieciowych, trwać długo czyli również przekładać się bezpośrednio na czas trwania zapytania, blokad etc… Zapytania rozproszone, powinny zwracać zawsze minimalny, konieczny zbiór danych. Chyba również z tego powodu, domyślnie możliwość ich wykonywania jest wyłączona.

Zapytania do bazy ORACLE

Jak to bywa, możemy zrealizować tego typu zapytanie ad hoc na kilka sposób. Poniżej parę przykładów – dla każdego coś miłego. Pierwsze trzy zakładają istnienie wpisu TNS w pliku {ORA_HOME}\Network\Admin\tnsnames.ora. W ostatnim przykładzie, jawnie podaje wszystkich parametry komunikacyjne do serwera zdalnego.

-- z providerem OLEDB Oraclowym + wpis w {ORA_HOME}\Network\Admin\tnsnames.ora
SELECT * 
FROM OPENROWSET(
	'OraOLEDB.Oracle',
	'NaszOracleSrv';'username';'paswword', 
	'SELECT ''works fine'' as Kol1 FROM Dual' )
 
-- z providerem MSowym do Oracle 
Select * 
FROM OPENROWSET(
	'MSDAORA',
	'NaszOracleSrv';'username';'paswword', 
	'SELECT ''works fine'' as Kol1 FROM Dual' )
 
-- a tu inaczej - wszystko w Connection Stringu + połączenie via ODBC z wpisem w {ORA_HOME}\Network\Admin\tnsnames.ora
SELECT * 
FROM OPENROWSET(
	'MSDASQL', 
	'DRIVER={Microsoft ODBC for Oracle}; UID=username; PWD=password; SERVER=NaszOracleSrv;', 
	'SELECT ''works fine'' as Kol1 FROM Dual' 
	) as Tabela
 
-- a tu inaczej - wszystko w Connection Stringu + połączenie via ODBC
SELECT * 
FROM OPENROWSET(
	'MSDASQL', 
	'DRIVER={Microsoft ODBC for Oracle}; UID=username; PWD=password; SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))(CONNECT_DATA=(SID=NaszOracleSID)));', 
	'SELECT ''works fine'' as Kol1 FROM Dual' 
	) as Tabela

Zapytania do baz MySQL i innych z użyciem MSDASQL + ODBC

Jeśli nie mamy dostępnego dedykowanego dostawcy OLE DB, możemy łączyć się ze zdalnym serwerem za pomocą MSDASQL i połączenia wykorzystującego sterowniki ODBC. Listę dostępnych driverów, zainstalowanych na maszynie, sprawdzisz w panelu administracyjnym ODBC :

ODBC_Drivers

W przypadku odpytania bazy MySQL z poziomu SQL Server, potrzebne będą sterowniki ODBC (jak widać powyżej, mam takie zainstalowane). Sama kwerenda jest bardzo podobna do poprzedniej. Zastosowałem inny sterownik ODBC (MySQL) i dlatego connection string, którego struktura jest określana przez producenta, różni się w stosunku do poprzedniej, gdy odpytywałem bazę Oracle.

  SELECT  * 
  FROM OPENROWSET(
	'MSDASQL',
       'Driver={MySQL ODBC 5.2 ANSI Driver}; 
  Server=MyServerMySQL; Database=MojaBaza; USER=Username; PASSWORD=alamakota',
	'SELECT * from tabelka limit 10' )

Za pomocą OPENROWSET, możemy odpytywać także zawartości plików. Mogą to być zarówno pliki tekstowe, strukturyzowane (CSV), dokumenty Excel czy „bazy danych” Access. Dość wygodnie, za pomocą OPENQUERY przeczytamy również całe pliki XML. Pobieranie danych z Excela oraz plików tekstowych, opisuje szczegółowo w kolejnych rozdziałach tego kursu.

Poniżej kilka prostych, ale praktycznych przykładów :

-- Czytanie zwykłego pliku txt
SELECT F1 as FileContent 
FROM OPENROWSET(
	'Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=D:\data\;HDR=NO', 
    'SELECT *  FROM test.txt')
 
-- załadowanie pliku XML (można go przypisać do zmiennej
SELECT CONVERT(xml, BulkColumn ) as XmlDocs
FROM 
OPENROWSET (BULK 'D:\DaneXML\NBP\a051z140314.xml',SINGLE_BLOB) as T1

OPENQUERY_1


Serwery powiązane – Linked Servers

Do tej pory, korzystaliśmy z tzw. zapytań Ad hoc – spontanicznych kwerend, do pobrania określonych danych z zewnętrznego źródła. W środowiskach produkcyjnych, istnieją serwery, które regularnie odpytujemy. Pisanie za każdym razem, długich connection stringów, jest niewygodne.

Za pomocą obiektów serwerów powiązanych, możemy zrealizować integrację danych z różnych środowisk w wygodniejszy sposób.

Na wstępie warto jeszcze raz podkreślić, że zapytania rozproszone, niezależnie od metody (OPENROWSET, Linked Server) powinny być stosowane z rozwagą, bo mogą być przyczyną problemów wydajnościowych (sieć, czasy blokad etc.) w środowiskach transakcyjnych.

Świadomi tego :), możemy zabrać się za tworzenie obiektów serwerów powiązanych. Jeśli jesteś zwykłym użytkownikiem bazy danych, potrzebujesz do tego uprawnień serwerowych ALTER ANY LINKED SERVER.

Serwery powiązane, możesz tworzyć w T-SQL (służy do tego procedura sp_addlinkedserver) lub za pomocą wizarda w Management Studio.

Linked_server_01

Całość konfiguracji, ogranicza się zazwyczaj do określenia dostawcy OLE DB, nazw serwera, ewentualnie połączenia ODBC oraz ustawień związanych z bezpieczeństwem. W przypadku SQL Server, wystarczy podać nazwę sieciową instancji – zostanie utworzony serwer powiązany, domyślnie z uwierzytelnianiem zintegrowanym, bazujący na natywnym kliencie SQLNCLI OLE DB.

Pisanie zapytań z wykorzystaniem Linked Servers

Odwoływanie się w kwerendach rozproszonych do obiektów serwerów powiązanych, jest możliwe na dwa sposoby.
Za pomocą funkcji tabelarycznej OPENQUERY(), możemy wysyłać zapytanie do dowolnego obiektu Linked Server.

SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA')

W tej sytuacji, zostanie wykonane zapytanie na serwerze zdalnym w dokładnie takiej postaci, jak parameter query funkcji OPENQUERY. Czyli zostanie pobrana CAŁA zawartość odpytywanej tabeli i przesłana przez sieć do klienta. Porównaj takie dwa zapytania z filtracją rekordów :

-- źle
SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA')
WHERE KolID = 12345
 
-- dobrze :)
SELECT * 
FROM OPENQUERY(SerwerPowiazany,'SELECT * FROM TABELA WHERE KolID = 12345 ')

Zwrócą to samo, ale o ile lżejsze jest zapytanie drugie, z filtracją po stronie zdalnego serwera….
Funkcja OPENQUERY, pozwala również na wysyłanie poleceń typu DML.

Drugim sposobem, jest odwoływanie się w poleceniach SQL do obiektów (tabel, widoków czy procedur), po w pełni kwalifikowanej, czteroczłonowej nazwie. W ten sposób, możemy wywoływać także procedury składowane na zdalnym serwerze.

           <SERVER>.<BAZA_DANYCH>.<SCHEMAT>.<OBIEKT>

Zarówno pierwszy jak i drugi sposób, pozwala na pobieranie danych z zewnętrznych źródeł podobnie jak ze zwykłych tabel. Można łączyć je w zapytaniach, odwołując się jednocześnie do różnych serwerów (UWAGA na wydajność !!!).

SELECT c.*, o.OrderID  
FROM Server1.Northwind.dbo.Customers c 
		inner join Server2.Northwind.dbo.Orders o on c.CustomerID = o.CustomerID

Metadane związane z Linked Servers

Z obiektami Linked Servers, związanych jest kilka przydatnych widoków i procedur składowanych, które warto poznać. Listę dostępnych obiektów Linked Servers, loginów z nimi związanych, możesz obejrzeć poprzez widoki systemowe w bazie msdb (sys.servers, sys.linked_logins oraz sys.remote_logins).

Mamy także dostępnych kilka specjalnych procedur składowanych. Z bardziej praktycznych są sp_catalogs oraz sp_tables_ex, które pozwalają na podejrzenie wszystkich baz danych / tabel, dostępnych po przez dany obiekt (o ile dostawca OLE DB/ODB oferuje takie funkcjonalności). Poniżej przykład wywołania sp_catalogs do zdalnego SQL Servera o nazwie RFN.

  -- bazy danych
  sp_catalogs 'RFN'

Linked_server_02

Za pośrednictwem serwerów powiązanych, podobnie jak funkcji OPENROWSET, możemy odnosić się również do plików. Jest wiele dróg na import danych z plików. Jedno z nich to rozwiązanie oparte o Linked Servers. Poniżej przykład utworzenia obiektu „serwera”, będącego katalogiem z plikami. Do tego celu wykorzystam dostawcę Jet.OLEDB.4.0.

EXEC sp_addlinkedserver  PlikiWymiany, 
	'Jet 4.0', 
         'Microsoft.Jet.OLEDB.4.0',
         'D:\Dane\',
         NULL,
         'Text';

W katalogu D:\Dane\ – są umieszczone dwa pliki tekstowe :

LinkedServer_TXT_FILE_01

Na koniec zastosowanie procedury składowanej sp_tables_ex oraz odczytanie zawartości pierwszego z plików.

EXEC sp_tables_ex PlikiWymiany;
GO
 
SELECT * 
FROM PlikiWymiany...[test#txt];

LinkedServer_TXT_FILE_02


Podsumowanie

Opisane powyżej metody pracy w środowisku rozproszonym są z pewnością bardzo praktyczne i często stosowane. Korzystając z nich, pamiętaj o aspektach wydajnościowych. Pobieranie danych (czasem całych tabel) poprzez sieć ze zdalnych serwerów, z pewnością nie jest najlepszym pomysłem.