Loading…

READY TO DEAL WITH YOUR DATA?

Get some tips on how to do that!
Start exploring

SQL Server 2016 – konfiguracje na poziomie bazy danych

Jedną z funkcjonalności systemu SQL Server 2016 prezentowanych przeze mnie na doskonałej konferencji SQLDay 2016 były konfiguracje na poziomie bazy danych. Celem tego wpisu jest omówienie owych konfiguracji oraz przedstawienie metody ich monitorowania na instancjach SQL Server współdzielonych przez wiele baz danych.

Geneza wpisu

Konfiguracje baz danych wprowadzone w SQL Server 2016 pojawiły się na obu moich prezentacjach w czasie SQLDay 2016. Uważam, że wiedza na temat tych konfiguracji jest niezbędna zarówno administratorom, jak i programistom. Błędne ustawienie wartości konfiguracji może prowadzić do problemów wydajnościowych o skali trudnej do przewidzenia. Pomyślałem też, że dla pamięci dobrze byłoby mieć gdzieś w sieci kod, który umożliwi monitorowanie tych opcji, jako że są one dostępne w sposób zupełnie odstający od znanych to tej pory opcji baz danych (te widoczne są w widoku systemowym sys.databases).

O konfiguracjach

Dla każdej bazy danych w SQL Server 2016 dostępne są cztery opcje konfiguracyjne nazwane Database Scoped Configurations. Widoczne są one w oknie właściwości baz danych w SQL Server Management Studio:

image

Wspomniane cztery opcje to:

  • Legacy Cardinality Estimation – włączenie powoduje przełączenie estymatora liczby wierszy w tryb 70 (sprzed SQL Server 2014), opcja doskonała w przypadkach występujących czasami po migracji bazy danych problemów z psującymi się na skutek nowych estymat liczby wierszy planami wykonania, domyślnie wyłączone (używany estymator w trybie 130),
  • Max DOP – ograniczenie maksymalnej liczby logicznych procesorów wykorzystywanych jednocześnie do wykonania pojedynczego zapytania w bazie danych, domyślnie 0 (brak ograniczeń),
  • Parameter Sniffing – włączanie / wyłączanie efektu kompilacji planów wykonania procedur składowanych i zapytań parametryzowanych dla wartości parametrów z pierwszego wykonania, po wyłączeniu tej opcji efekt jest analogiczny do używania we wszystkich zapytaniach hinta OPTIMIZE FOR UNKNOWN (przygotowanie planów wykonania w oparciu o statystyki zamiast wartości parametrów), opcja zastępująca trace flagę 4136, domyślnie włączona,
  • Query Optimizer Fixes – włączanie / wyłączanie działania poprawek (hotfixes) dotyczących optymalizatora zapytań (w tym estymatora liczby wierszy), opcja zastępująca trace flagę 4199, domyślnie wyłączona w RC3 (ale w RTM może być domyślnie włączona).

Wszystkie cztery opcje mogą są skonfigurowane dla instancji, na której znajduje się baza danych oraz dla ew. replik bazy danych na instancjach będących węzłami architektury AlwaysOn (Secondary Replicas).

Zmiana wartości opcji to kwestia wykonania polecenia ALTER DATABASE SCOPED CONFIGURATION. Składnia polecenia:

ALTER DATABASE SCOPED CONFIGURATION
{      
     {  [ FOR SECONDARY] SET   }  
}
| CLEAR PROCEDURE_CACHE
[;]  
 
< set_options > ::=  
{
    MAXDOP = {  | PRIMARY}  
    | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}  
    | PARAMETER_SNIFFING = { ON | OFF | PRIMARY}  
    | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}  
}

Przykłady użycia składni:

--MAXDOP na poziomie bazy danych
ALTER DATABASE SCOPED CONFIGURATION 
SET MAXDOP = 1;
 
--Estymator liczby wierszy w trybie 130 (SQL Server 2014+)
ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = OFF;
 
--Włączone uwzględnianie poprawek 
--dotyczących optymalizatora zapytań
ALTER DATABASE SCOPED CONFIGURATION 
SET QUERY_OPTIMIZER_HOTFIXES = ON;
 
--Wyłączanie efektu Parameter Sniffing
ALTER DATABASE SCOPED CONFIGURATION
SET PARAMETER_SNIFFING = OFF;
 
--Nowa metoda czyszczenia cache'u planów wykonania per baza
ALTER DATABASE SCOPED CONFIGURATION
CLEAR PROCEDURE_CACHE;

Monitorowanie konfiguracji

W mojej ocenie wszystkie cztery opcje powinny być używane z zachowaniem odpowiedniej ostrożności i czujności. I oczywiście przy całkowitej świadomości spodziewanych efektów działania… Spodziewam się, że sporo problemów może wynikać z nieprzemyślanych i nie do końca świadomych zmian tych opcji (np. wyłączenie Parameter Sniffingu może spowodować spadek wydajności wielu zapytań w jednej chwili). Dlatego warto już teraz zaopatrzyć się w narzędzie do monitorowania tych opcji.

Wartości opcji są dostępne przez widok systemowy sys.database_scoped_configurations, który pokazuje opcje dla bazy danych, w kontekście której pracuje użytkownik na bieżącej sesji:

USE AdventureWorks2016CTP3;
GO
SELECT * FROM sys.database_scoped_configurations;
GO

Żeby zebrać informacje o ustawieniach opcji dla wszystkich baz danych na instancji, można użyć poniższego kodu:

DECLARE @SQL varchar(max);
SET @SQL = '
DROP TABLE IF EXISTS #temp
CREATE TABLE #temp (
  db sysname,
  name sysname,
  value sql_variant
);'
SELECT @SQL = @SQL + '
INSERT INTO #temp (db, name, value) SELECT TOP 4 ' + 
QUOTENAME(name, '''') + ', name, value FROM ' + 
QUOTENAME(name) + '.sys.database_scoped_configurations;'
FROM sys.databases;
SET @SQL = @SQL + '
SELECT 
  db, 
  [LEGACY_CARDINALITY_ESTIMATION], [MAXDOP], 
  [PARAMETER_SNIFFING], [QUERY_OPTIMIZER_HOTFIXES]
FROM #temp 
PIVOT (MAX(value) 
  FOR name IN (
    [LEGACY_CARDINALITY_ESTIMATION], 
    [MAXDOP], 
    [PARAMETER_SNIFFING], 
    [QUERY_OPTIMIZER_HOTFIXES]
  )
) AS pvt
ORDER BY db;';
EXEC (@SQL);
GO

Uwaga: w SQL Server 2016 RC3 jest problem z wstawianiem danych z sys.database_scoped_configurations do tabeli tymczasowej – bez użycia klauzuli TOP do tabeli wstawiany jest tylko jeden wiersz. Zastanawiać może również, z jakiego powodu te opcje nie są dostępne w widoku sys.databases. Być może w finalnej wersji produktu ulegnie to zmianie.

Fragment przykładowego wyniku:

image

Taki widok zapewni szybki wgląd w opcje i pozwoli wykryć niepokojące ustawienia, nad którymi warto się zastanowić w kontekście optymalizacji działania baz danych.

Podsumowanie

Wprowadzenie opcji konfiguracyjnych na poziomie bazy danych to zdecydowanie dobry krok. W mojej ocenie zwłaszcza opcje MAXDOP i LEGACY_CARDINALITY_ESTIMATION będą przydatne, ponieważ usprawnią procesy migracji (LEGACY_CARDINALITY_ESTIMATION) i umożliwią sterowanie wydajnością na poziomie pojedynczych baz danych (MAXDOP). Aczkolwiek, nieumiejętne używanie wspomnianych we wpisie opcji może prowadzić do niejednej katastrofy – dlatego ich monitorowanie wydaje mi się istotnym elementem działań administratorów i konsultantów, którzy będą pracować z SQL Server 2016.

2 thoughts on “SQL Server 2016 – konfiguracje na poziomie bazy danych

  1. Mariusz, no to mamy pewnie buga do zgłoszenia :-) Choć nie jest to wielki problem na tle niektórych zgłoszeń od lat widniejących na connect ;-)

Leave a Reply