SQL Server 2016 – database scoped configurations

Kontynuujemy naszą serię poświęconą najnowszej wersji SQL Server czyli tej oznaczonej numerem 2016. Tematem dzisiejszego artykułu będą nowe ustawienia bazy danych (ang. Database Scoped Configurations), które wcześniej albo nie były dostępne, albo były możliwe do ustawienia na poziomie instancji lub z użyciem odpowiednich flag- zaczynajmy!

Pierwszym pytaniem jakie może się pojawić w waszych głowach to kwestia gdzie omawiane ustawienia się znajduję – odpowiedź jest bardzo prosta wystarczy kliknąć prawym przyciskiem na wybraną bazę danych i wybrać jej właściwości, a następnie sekcję Options. Naszym oczom powinien ukazać się obraz podobny do poniższego.

Jak można zauważyć właściwości tych nie ma zbyt wiele jednak dają nam one bardzo dużo możliwości. Na samym początku omówimy sobie Legacy Cardinality Estimation oraz Legacy Cardinality Estimation For Secondary. Pierwsza z nich powoduje, iż w ramach naszej bazy wykorzystywany będzie stary estymator liczebności. Po co nam takie ustawienie? Zdarza się, iż po migracji bazy danych zapytania z jakiegoś powodu radzą sobie gorzej – w takim wypadku wcześniej musieliśmy albo zmienić tryb zgodności (ang. Compatibility Mode) lub też włączyć flagę 9481. W tym momencie wystarczy ustawić tą właściwość i problem mamy z głowy. Ta sama opcja odnosząca się do repliki (Secondary) oznacza, iż możemy ustawić tę opcję rozdzielnie dla głównego node i repliki w ramach architektury AlwaysOn.

Kolejną możliwą do ustawienia opcją jest MAXDOP. Ustawienie to jest szeroko znane i odpowiada za maksymalną ilość logicznych procesorów jakie pojedyncze zapytanie może wykorzystać. Do tej pory mieliśmy możliwość ustawienia MAXDOP na poziomie zapytania, instancji (używając sp_configure) oraz w Resource Governor – teraz dodatkowo możemy to zdefiniować na poziomie bazy danych i podobnie jak powyżej możemy ustawić w ramach architektury AlwaysOn.

Sprawdźmy jak to ustawienie sprawuje się w praktyce. Najpierw ustawmy sobie MAXDOP na 1 używając TSQL (w przykładach będziemy używać WideWorldImportersDW):

USE WideWorldImportersDW
GO

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
GO

Następnie wykonajmy poniższe zapytanie i sprawdźmy jego plan wykonania:

SELECT
	[Order Date Key],
	SUM(Quantity) AS Quantity
FROM
	[Fact].[Order]
GROUP BY
	[Order Date Key]

Jak możemy się domyślać zapytanie wykonywane jest na jednym procesorze logicznym – świadczy o tym brak na graficznym planie wykonania dwóch charakterystycznych strzałek przy operatorach. Aby mieć jednak pewność zajrzyjmy do właściwości – tam znajdziemy rzeczywistą liczbę używanych procesorów oraz powód dlaczego użyto ich tyle:

Jak widać użyto nie użyto więcej procesorów logicznych ze względu na omawiane ustawienie – na poziomie instancji nie było nic zmieniane (a domyślne ustawienie jest równe 0 co oznacza użycie wszystkich dostępnych zasobów).

Kolejnym omawianym w ramach niniejszego artykułu ustawieniem jest Parameter Sniffing. Jedną z najbardziej użytecznych funkcjonalności SQL Server jest cache planów zapytań. Dzięki temu mechanizmowi możemy zaoszczędzić czas potrzebny na optymalizację zapytań – jednak wiąże się z nim pewna niedogodność. W przypadku gdy mamy do czynienia ze skośnym rozkładem danych w ramach tabeli w cache może znajdować się nieoptymalny dla nas plan np. Możemy wyszukiwać klientów z miasta Wąchock, których jest 10 lub też klientów z Warszawy których jest 100 000 – przypuścmy, że mamy procedurę składowaną służącą do pobrania klientów. Gdy uruchomimy procedurę przekazując jako parametr miasto Wąchock użyty zostanie operator Seek, a plan zapytania zostanie umieszczony  w cache. Co natomiast gdy uruchomimy tą samą procedurę z Warszawą zdefiniowaną jako wartość parametru? Ze względu na to, że plan jest w cache to zostanie on użyty ponownie i będziemy pobierać 100 000 wierszy operatorem seek co może być dla nas bardzo kosztowne pod kątem wydajnościowym. Jak ten problem rozwiązać? Wymusić rekompilację planów poprzez WITH RECOMPILE lub np. użyć wskazówki OPTIMIZE FOR UNKNOWN. W SQL Server 2016 w przypadku gdy większość naszych zapytań charakteryzuje się odpytywaniem tabel o skośnym rozkładzie możemy wyłączyć Parameter Sniffing co jest równoznaczne z opcją OPTIMIZE FOR UNKNOWN (lub włączeniem flagi 4136) i powoduje optymalizację opartĄ wyłącznie o statystyki, a nie o wartość parametru. Opcja również konfigurowalna w ramach AlwaysOn.

Ostatnią omawianą opcją dostępną z poziomu interfejsu graficznego jest Query Optimizer Fixes, która pozwala na wykorzystanie fixów wydanych przez Microsoft w ramach bazy danych bez względu na to jaki jest ustawiony Comaptibility Level. Jest to niezmiernie istotne ustawienie, które niejako uniezależnia nas od Compatibility Level ustawionym na bazie danych.

Dodatkową opcją jaką mamy do dyspozycji w SQL Server 2016 jest usunięcie danych z cache procedur w ramach bazy danych bez konieczności posiadania uprawnienia sysadmin. Sprawdźmy to na przykładzie – najpierw sprawdźmy ilość wpisów w cache procedur powiązanych z naszą bazą danych:

SELECT
    d.name AS BazaDanych,
    count(*) as LiczbaWpisow
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY (
    SELECT CAST(pa.value AS INT) AS database_id
    FROM sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
    WHERE pa.attribute = N'dbid'
) AS DB
INNER JOIN sys.databases AS d
    ON d.database_id = DB.database_id
    where d.database_id=DB_ID()
GROUP BY d.name
ORDER BY d.name

Jak widać mamy 5 wpisów powiązanych z WideWorldImportersDW. Teraz wyczyśćmy liczbę wpisów wykorzystując nową składnię:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Wykonajmy raz jeszcze sprawdzenie liczby wpisów – jak widać została ona zmniejszona do 1.

Oczywiście wszystkie te opcje muszą być monitorowane gdyż ich niepoprawne ustawienie może powodować m.in spadek wydajności. Aby to zrobić można użyć GUI lub skorzystać z nowego widoku systemowego sys.database_scoped_configurations:

SELECT * FROM sys.database_scoped_configurations;
GO

Mam nadzieję, że udało mi się przybliżyć Wam nowe możliwości konfiguracyjne SQL Server. Może nie ma ich wiele jednak są one niezmiernie przydatne i moim zdaniem każdy konsultant zajmujący się na poważnie bazami danych powinien je przynajmniej w podstawowym stopniu znać.