Linked Server – problemy i możliwości

W ostatnim czasie miałem okazję pracować u jednego z moich klientów nad optymalizacją zapytań. Okazało się, że część z nich działało na bazie transakcyjnej i było uruchamiane cyklicznie aby pobrać dane do bazy służącej jako źródło dla raportów. Zapytania te w dużej mierze opierały się na serwerach połączone (ang. Linked Server) – czy to dobrze? Mimo, że temat jest powszechnie znany to nie każdy zdaje sobie sprawę jak serwery połączone działają i jaki mają wpływ na ogólną wydajność zapytań. Postaram się przybliżyć ten temat w dalszej części niniejszego artykułu.

Na samym początku trochę teorii na temat serwerów zlinkowanych, które pozwalają na wykonywanie tzw. Distributed Queries czyli nic innego jak zapytań w całości lub częściowo wykonywanych na zdalnych serwerach. Dzięki takiemu podejściu możemy stworzyć pojedyncze zapytanie odpytujące kilka serwerów jednocześnie.

Architektura serwerów zlinkowanych (LS) przedstawiona została na powyższej grafice. Narzędzie klienckie odpytuje SQL Server, który wykorzystując OLEDB łączące się z określonymi źródłami danych. Należy mieć na uwadze, że źródłem w cale nie musi być SQL Server, ale również inne produkty takie jak ORACLE, DB2 czy chociażby Access. Warto pamiętać, że niektóre sterowniki są szybsze od innych i w naszym interesie jest to aby znaleźć ten właściwy. Ciekawym zastosowaniem jest również użycie Linked Server do pobrania danych z Active Directory, który postaram się w przyszłości opisać w ramach niniejszego bloga

Aby sprawdzić jakie mamy zdefiniowane serwery w ramach naszego bieżącego serwera możemy tradycyjnie użyć GUI lub widoków systemowych.  Z poziomu Management Studio wystarczy, że rozwiniemy węzeł Server Object, a następnie Linked Servers. W tym miejscu powinniśmy zobaczyć serwery połączone jeżeli takowe zostały przez nas zdefiniowane. Oprócz tego znajduje się jeszcze węzeł Providers, który listuje dla nas wszystkie zainstalowane sterowniki, których możemy użyć do połączenia w ramach serwera połączonego.

Do dyspozycji mamy również widok systemowy sys.servers, który zawiera interesujące nas dane. Dodajmy sobie nowy linked server klikając prawym przyciskiem myszy na Linked Server i z menu kontekstowego wybierając New Linked Server – naszym oczom powinno ukazać się następujące okno:

Jako Linked Server podajemy nazwę tworzonego obiektu – ważne aby ta nazwa była przemyślana gdyż będziemy jej używać w naszych zapytaniach i nie da się jej później zmienić. W przypadku gdy łączymy się do SQL Server wybieramy właśnie SQL Server jako Server type – wtedy też nazwa serwera zlinkowanego musi być nazwą serwera do którego się łączymy. W innym przypadku musimy podać nazwę serwera w Data Source i ewentualnie bazy danych do której się łączymy. Innym sposobem jest podanie łańcucha połączeniowego w sekcji Provider String – na ten moment wybierzemy pierwszą opcję.W celach informacyjnych podaję również składnię TSQL definiującą Linked Server – aby to zrobić używamy sp_addlinedserver:

EXEC master.dbo.sp_addlinkedserver 
	@server = N'SQLEXPRESS14', 
	@srvproduct=N'', 
	@provider=N'SQLOLEDB', 
	@datasrc=N'.\sqlexp14', 
	@catalog=N'AdventureWorksDW2012'

Po podaniu podstawowych informacji przejdźmy na zakładkę Security, która jest niezmiernie interesująca:

W górnej części możemy zmapować login na naszym bieżącym serwerze do loginu na serwerze połączonym. W przypadku gdy uwierzytelnienie chcemy oprzeć o Active Directory możemy zaznaczyć opcję Impersonate. Pamiętajmy o typowych problemach w tego typu sytuacjach – w momencie gdy łączymy się zdalnie przez Management Studio do naszego serwera i z jego poziomu za pomocą impersonacji łączymy się do serwera połączonego – wymaga to skonfigurowania Kerberosa – czyli dodatkowej konfiguracji po stronie sieciowej aby uniknąć problemu związanego z tzw. “double hop” czyli połączenia do określonego serwera, który z kolei ma się połączyć do następnego serwera. Wracając do naszego okna konfiguracyjnego – możecie sobie wyobrazić, że w wielu sytuacjach nie jesteśmy w stanie skonfigurować mapowania pomiędzy wszystkimi loginami wtedy też możemy skorzystać z dolnej części okna gdzie definiujemy opcję połączenia dla wszystkich loginów nie wymienionych w mapowaniu.  W tym miejscu zazwyczaj widzimy konto SQL Server dedykowane do tego typu połączeń. Wszyscy wiemy, że wiąże się to z wieloma wadami np. z tym, że nie mamy możliwości identyfikacji konkretnych działań na serwerze zlinkowanym. Sytuacja jest tym gorsza, że w serwerach przed SQL Server 2012 SP1 konto, które w tego typu połączeniach nie miało uprawnień sysadmin, db_owner lub db_ddladmin nie miało nie miało dostępu do statystyk! Tak więc w starszych implementacjach stoi przed wyborem bezpieczeństwa lub wydajności… na szczęście w nowszych wersjach zostało to naprawione.

Ostatnią zakładką konfiguracyjną serwera połączonego jest Server Options, którą można zauważyć poniżej.

Z mojego doświadczenia wiem, że w wielu przypadkach opcje te są zostawione na domyślnych wartościach. Nie jest to dobre we wszystkich przypadkach gdyż ustawienia te mają bezpośredni wpływ na wydajność i zachowanie zapytań rozproszonych. Większość z nich jest łatwa w interpretacji już po samej nazwie jednak wytłumaczymy trzy z nich.

Pierwszą opcją jest Collation Compatibile – gdy ustawimy ją na True to SQL Server zakłada, że Collation na serwerze połączonym jest kompatybilne z serwerem lokalnym. Należy na tą opcję bardzo uważać gdyż w przypadku złego założenia zgodności możemy otrzymać niepożądane wyniki. Kolejną opcją wartą uwagi jest Use Remote Collation –
– w skrócie rzecz ujmując jeśli ustawimy tą opcję na TRUE to porównywania znaków będą wykonywane po stronie LS, w przeciwnym wypadku filtracja będzie wykonywana na serwerze głównym – przyjrzymy się temu zachowaniu w dalszej części artykułu. Ostatnią opcją jest Enable Promotion of Distributed Transactions, która pozwala na rozproszone transakcje z wykorzystaniem procesu Microsoft Distributed Transaction Coordinator.

Sprawdźmy Linked Server w akcji – na mojej głównej maszynie mam SQL Server 2016 Enterprise, która ma zdefiniowany Linked Server do drugiej instancji zawierającej SQL Server 2014 Express. Na serwerze Express znajduje się baza AdventureWorksDW2012 – na naszym głównym serwerze stwórzmy sobie bazę i tabelę FactInternetSales:

CREATE DATABASE LinkedServer
GO

USE LinkedServer
GO

SELECT * INTO FactInternetsales FROM [SQLEXPRESS14].AdventureWorksDW2012.dbo.FactInternetSales
GO

Jako pierwsze ćwiczenie wykonajmy proste zapytanie, które odpytuje tabelę DimDate poprzez Linked Server – aby to zrobić używamy nazwy czteroczłonowej o strukturze:
[Nazwa Serwera].[Nazwa Bazy].[Nazwa schematu].[Nazwa tabeli]

SELECT * FROM [SQLEXPRESS14].AdventureWorksDW2012.dbo.DimDate
GO

Jak widać zapytania do LS na planie widoczne są jako operator Remote Query. Przyglądając się bliżej właściwościom tego operatora możemy zobaczyć, iż szacunkowa ilość wierszy się zgadza, tak więc mieliśmy dostęp do aktualnych statystyk obiektu. Sam plan jest bardzo prosty i nie ma w nim niczego co mogłoby nas niepokoić.

Spróbujmy teraz do naszego zapytania dodać warunek wyszukania na tyle selektywny, aby uzyskać operator Index Seek – wyszukajmy konkretną datę po kluczu głównym indeksu zgrupowanego – mamy pewność, że fizycznie pojedynczy rekord jest wybierany operatorem index seek.

SELECT * FROM [SQLEXPRESS14].AdventureWorksDW2012.dbo.DimDate
WHERE DateKey=20050104
GO

Jak widać na pierwszy rzut oka plan nie różni się zbyt wiele od poprzedniego. Jednakże ze względu na fakt, iż nie mamy dodatkowego operatora jak np. Filter wiemy, że filtrowanie zostało przekazane do serwera źródłowego. Pewność mamy patrząc na właściwość Remote Query operatora o tej samej nazwie – widać tam jakie zapytanie jest wywoływane na serwerze źródłowym – w naszym przypadku jest to zapytanie:

SELECT 
"Tbl1002"."DateKey" "Col1005",
"Tbl1002"."FullDateAlternateKey" "Col1006",
"Tbl1002"."DayNumberOfWeek" "Col1007",
"Tbl1002"."EnglishDayNameOfWeek" "Col1008","Tbl1002"."SpanishDayNameOfWeek" "Col1009","Tbl1002"."FrenchDayNameOfWeek" "Col1010","Tbl1002"."DayNumberOfMonth" "Col1011","Tbl1002"."DayNumberOfYear" "Col1012","Tbl1002"."WeekNumberOfYear" "Col1013","Tbl1002"."EnglishMonthName" "Col1014","Tbl1002"."SpanishMonthName" "Col1015","Tbl1002"."FrenchMonthName" "Col1016","Tbl1002"."MonthNumberOfYear" "Col1017","Tbl1002"."CalendarQuarter" "Col1018",
"Tbl1002"."CalendarYear" "Col1019",
"Tbl1002"."CalendarSemester" "Col1020",
"Tbl1002"."FiscalQuarter" "Col1021",
"Tbl1002"."FiscalYear" "Col1022",
"Tbl1002"."FiscalSemester" "Col1003" 
FROM 
"AdventureWorksDW2012"."dbo"."DimDate" "Tbl1002" 
WHERE "Tbl1002"."DateKey"=(20050104)

Filtracja przebiegła pomyślnie – zróbmy to samo tylko tym razem użyjmy funkcji na warunku filtrującym:

SELECT * FROM [SQLEXPRESS14].AdventureWorksDW2012.dbo.DimDate
WHERE DATEADD(DAY,1,FullDateAlternateKey)=CAST('20050203' AS DATE)
GO

Zapewne wielu z Was zapalni się czerwone światło mówiące, że zapytanie zapisane w ten sposób nie jest przyjazne dla wyszukiwania i z całą pewnością skończy się przeszukaniem całej tabeli – macie rację tak też się stanie! Natomiast w tym przypadku chciałem zwrócić uwagę na coś innego – spójrzmy na plan zapytania:

Widać coś niepokojącego? Oczywiście! Z serwera źródłowego pobrane zostały wszystkie wiersze, przesłane przez sieć do serwera głównego i dopiero tam nastąpiła filtracja! Jest to bardzo niepokojące zjawisko, które przy większych tabelach może doprowadzić do całkowitego załamania wydajności przy okazji przy pesymistycznym modelu blokowania – zablokować całą tabelę..  Teraz zobaczymy jak zachowa się wspomniane już wcześniej porównywanie stringów – na samym początku ustawmy opcję use remote collation na false używając służącej do tego typu operacji procedury sp_serveroption:

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'SQLEXPRESS14', @optname=N'use remote collation', @optvalue=N'false'
GO

Następnie wykonajmy zapytanie wyszukujące rekordy po wartości tekstowej:

SELECT *  FROM [SQLEXPRESS14].AdventureWorksDW2012.dbo.DimReseller
WHERE BusinessType='Warehouse'

Jak można było się domyślać, filtracja została wykonana dopiero na serwerze głównym. Po zmianie właściwości tak aby porównywanie wykonywane było na LS plan przybiera już lepsze kształty:

Tak więc przekazujmy do serwera źródłowego wszelkie porównania aby wykorzystać tamtejsze indeksy i przesyłać przez sieć jak najmniejszą ilość wierszy – pamiętajmy o tym ustawieniu bo jest ono niezmiernie istotne. Istnieje również inny sposób wymuszania wykonania tego typu porównań na serwerze połączonym – wystarczy zamiast składni czteroczłonowej użyć OPENQUERY:

SELECT *  FROM OPENQUERY([SQLEXPRESS14],'select * from AdventureWorksDW2012.dbo.DimReseller
WHERE BusinessType=''Warehouse''')

Jak widać całe zapytanie zostało wykonane zdalnie, a na planie wykonania widzimy operator Remote Scan. Wszystko wydaje się w porządku natomiast z OPENQUERY mamy jeden poważny problem – chodzi  mianowicie o statystyki.

Jak widać na powyższym zrzucie ekranowym poprzez OpenQuery szacowana ilość wierszy równa jest 10 000… Optymalizator nie wie ile wierszy będzie zwrócone i nie ma eleganckiego sposobu aby to obejść. Tak więc od razu może nam się nasuwać scenariusz użycia OPENQUERY – używajmy go wtedy gdy zdecydowana większość zapytania wykonywana jest na LS i rezultat nie jest łączony z innymi tabelami gdyż ze względu na błędne szacunki może nam to nieco popsuć wydajność – szczególnie gdy w dalszej części zapytania wiersze te będą łączone z innymi danymi czy też sortowane.

Zobrazujmy to na przykładzie – odpytajmy tabelę źródłową wykorzystując OpenQuery, a następnie posortujmy wiersze:

SELECT *  FROM 
OPENQUERY(
	[SQLEXPRESS14],
	'select * from AdventureWorksDW2012.dbo.FactInternetSales'
	)
ORDER BY OrderDateKey DESC, CustomerKey ASC

Na poniższym planie widzimy, iż pojawiło się ostrzeżenie na operatorze Sort. Związane jest to z błędnymi szacunkami pochodzącymi z operatora Remote Scan. Podobne ostrzeżenia mogą się pojawić przy użyciu złączeń typu Hash i powoduje to problemy z wydajnością całego zapytania.

Jeżeli przy tak prostych zapytaniach natrafiamy na problemy to możecie sobie wyobrazić co się dzieje w przypadku naprawdę skomplikowanych konstrukcji.

Porównajmy wykonanie prostego złączenia między tabelą FactInternetSales oraz DimDate:

select * from dbo.FactInternetsales AS F
JOIN
	[SQLEXPRESS14].[AdventureWorksDW2012].dbo.DimDate AS D
ON
	D.DateKey=F.DueDateKey

select * from dbo.FactInternetsales AS F
JOIN
	OPENQUERY([SQLEXPRESS14],'SELECT * FROM [AdventureWorksDW2012].dbo.DimDate') AS D
ON
	D.DateKey=F.DueDateKey

Zignorujmy fakt, że nasze tabele są stertami i dostaliśmy ostrzeżenie “missing index”. Porównując koszty łatwo można dostrzec, że zapytanie czteroczłonowe wykonało się dużo szybciej i w zdecydowanej ilości przypadków tak właśnie będzie. Istnieje jeszcze hint REMOTE jednakże raczej go staram się unikać dlatego wspomnę o nim bardzo krótko. Pozwala on na to, że zapytanie będzie wykonane na serwerze tabeli znajdującej się po prawej stronie JOIN, wszystko było by dobrze gdyby nie fakt, iż HINT ten wymaga aby złączenie było wewnętrzne co bardzo często wyklucza jego zastosowanie. Ponadto nie ma możliwości użycia tej wskazówki gdy podamy jawnie COLLATION przy złączeniu.

Przechodząc dalej w analizie serwerów połączonych przetestujmy sobie również statystki oczekiwań czyli waitsy związane z Linked Server.  Aby to zrobić w ramach jednej sesji uruchomimy sobie długotrwałe zapytanie takie jak na przykład to:

SELECT *  FROM OPENQUERY([SQLEXPRESS14],
'select A.* from AdventureWorksDW2012.dbo.FactResellerSales A
cross join AdventureWorksDW2012.dbo.FactResellerSales B
order by 1
')

w drugim oknie (osobnej sesji) możemy odpytać widok sys.dm_os_waiting_tasks jako warunek podając identyfikator sesji (w moim wypadku 55)

SELECT 
	session_id,
	wait_type,
	wait_duration_ms 
FROM sys.dm_os_waiting_tasks
WHERE session_id=55

Pamiętajmy o tym, że widok ten ukazuje aktualne statystyki oczekiwań – dlatego też możemy wielokrotnie klikać F5 aby na bieżąco otrzymywać rezultat. W większości przypadków otrzymamy statystyki oczekiwań o nazwie OLEDB które jawnie wskazują na to, iż nasz SQL Server oczekuje na rezultat pochodzący z Linked Servera. Tak więc widząc podczas troubleshootingu, że OLEDB jest bardzo często występującym waitsem należy mieć na uwadze, że jednym ze spowalniających elementów są właśnie Linked Servery. Oczywiście opóźnienie to może być związane np. z oprogramowaniem monitorującym SQL Server ale zazwyczaj to Linked Server powoduje to opóźnienie… Obok OLEDB możemy dostrzec również ASYNC_NETWORK_IO czyli krótko rzecz ujmując opóźnienie związane z generowaniem rezultatu w SSMS – czyli tak naprawdę coś co wynika ze sposobu przetwarzania rezultatu w aplikacji klienckiej.

Podsumowując ten krótki wywód chciałbym abyście pamiętali, żeby nie nadużywać serwerów połączonych. Bardzo często widzę, iż niektórzy próbują ominąć potrzebę hurtowni danych tworząc większość zapytań raportowych opartych o ten właśnie mechanizm. Błędne statystyki, przeniesienie operacji na serwer główny czy też zagrożenia bezpieczeństwa powinny w jasny sposób do nas przemawiać i traktować omawiany mechanizm jako jedno z rozwiązań, a nie jako coś prostego i pozbawionego kosztów. Myślę, że to nie ostatni mój artykuł związany z Linked Server – z całą pewnością poruszymy jeszcze tematykę rozproszonych transakcji o których jedynie wspomniałem. Stay tuned!