Ostrzeżenia optymalizatora – ColumnsWithNoStatistcs, PlanAffectingConvert, UnmatchedIndexes, NoJoinPredicate

Czasem optymalizator chce nam przekazać komunikat, który może być dla nas ważną wskazówką jeśli chodzi o optymalizację zapytań. W ramach niniejszego artykułu postaram się przedstawić wybrane ostrzeżenia na które możecie natrafić podczas przeglądania Waszych planów wykonań.

ColumnsWithNoStatistcs

Zapewne każdy z Was zdaje sobie sprawę jak ważnym obiektem  dla optymalizatora SQL Servera są statystyki. Obiekty te pozwalają dobrać optymalny plan poprzez wykorzystanie  operatorów odpowiednich dla konkretnych sytuacji. Czasem jednak SQL Server nie może wykorzystać statystyk ze względu na to, że ich po prostu nie ma. Taka sytuacja jest na szczęście dla nas dosyć łatwa do wyśledzenia na planie wykonania i objawia się nam jako ostrzeżenie o wszystko mówiącej nazwie ColumnsWithNoStatistcs. Jest to bardzo niekorzystna sytuacja, która powinna przez nas być jak najszybciej naprawiona. Warto pamiętać, że otrzymanie takiego komunikatu jest efektem niestandardowej konfiguracji naszej bazy lub też manualnego usunięcia statystyk. Prześledźmy to na przykładzie – dla potrzeb demonstracyjnych użyjemy SQL Server 2016 i bazy WideWorldImportersDW, którą możecie ściągnąć ze stron Microsoftu.

Na początku wyłączmy automatyczne tworzenie statystyk poniższym poleceniem:

USE [master]
GO

ALTER DATABASE [WideWorldImportersDW] SET AUTO_CREATE_STATISTICS OFF
GO

W tym miejscu zaznaczymy co tak naprawdę robi ustawienie AUTO_CREATE_STATISTICS. W momencie gdy właściwość ta jest ustawiona na ON to SQL Server gdy chce użyć statystyk, a określone pole ich nie posiada to przed wykonaniem zapytania żądane statystyki zostaną utworzone. Może nasuwać się tu myśl, że wiąże się z to dodatkowym narzutem czasowym – to prawda jednak mimo wszystko brak statystyk w tym i kolejnych zapytaniach może powodować dużo gorsze skutki. Zalecaną praktyką jest włączenie tego ustawienia.

Kolejnym krokiem jest usunięcie istniejących statystyk na polu Postal Code w ramach tabeli Dimension.Customer – zrobimy to wykonując polecenie DROP STATISTICS

USE [WideWorldImportersDW]
GO

DROP STATISTICS [Dimension].[Customer]._WA_Sys_00000008_3F466844
GO

Teraz wystarczy, że uruchomimy nasze zapytanie odpytujące tabelę klientów i włączymy graficzny plan zapytania:

SELECT DISTINCT
       [Postal Code]
FROM
       [Dimension].[Customer]
WHERE
       [Postal Code]='90152'

Na powyższym planie  przy operatorze Clustered Index Scan możemy zauważyć żółty znak oznaczający ostrzeżenie. Po najechaniu na ten operator możemy przeczytać treść komunikatu:

Jak widać optymalizator chciał wykorzystać statystyki jednak nie mógł tego zrobić ze względu na fakt, iż je usunęliśmy i wyłączyliśmy automatyczne ich tworzenie. Co możemy z tym zrobić? To bardzo proste włączyć AUTO_CREATE_STATISTICS na ON i uruchomić zapytanie ponownie lub też stworzyć statystyki ręcznie. My wykorzystamy pierwsze podejście czyli najpierw włączymy wspomniane ustawienie…

USE [master]
GO

ALTER DATABASE [WideWorldImportersDW] SET AUTO_CREATE_STATISTICS ON
GO

…. i uruchomimy zapytanie ponownie wraz z włączonym graficznym planem zapytania. Jak widać poniżej – optymalizator w prawidłowy sposób mógł wykorzystać statystyki i nasze zapytanie jest pod tym względem optymalne:

Pamiętajmy aby mieć włączone automatyczne tworzenie statystyk i aby w naszych planach utrzymaniowych regularnie je aktualizować aby uniknąć wielu problemów związanych z wydajnością naszych zapytań.

PlanAffectingConvert

Prawdopodobnie najczęściej występującym ostrzeżeniem z jakim mamy do czynienia jest ostrzeżenie związane z niejawną konwersją o nazwie PlanAffectingConvert. Pojawia się ono w momencie gdy dla przykładu w klauzuli WHERE dla kolumny o typie INT przekazujemy wartość tekstową, która może być przekonwertowana na liczbę. Sprawdźmy to  – najpierw stwórzmy sobie tabelę tymczasową zawierającą wszystkie daty wymiaru czasu przekonwertowane na typ VARCHAR(20)

SELECT 
     CAST(DATE AS VARCHAR(20)) DATES
INTO 
     #Dates
FROM 
      Dimension.Date

Następnie odpytajmy nowopowstałą tabelę i przefiltrujmy ją tak aby pole DATES było równe bieżącej dacie.

SELECT 
       * 
FROM 
       #Dates
WHERE 
       DATES=GETDATE()

Ze względu na to że DATES jest typu VARCHAR(20), a GETDATE() zwraca wartość typu datetime musi nastąpić niejawna konwersja, która jednocześnie może zachwiać estymację liczebności przez optymalizator.

Może to powodować, iż nasze plany będą mniej optymalne niż mogłyby być. Dlatego też zawsze przywiązujmy wagę do typów danych naszych obiektów i parametrów.

Unmateched Index

W przypadku używania indeksów filtrowanych często spotykanym ostrzeżeniem jest tzw. Unmateched Index. Ostrzeżenie to oznacza, iż optymalizator nie wie czy użyć indeksu ze względu na to, iż nie zna wartości fitlrującej w zapytaniu. Na samym początku stwórzmy sobie indeks filtrujący na tabeli Fact.Order, który pokaże zamówienia nie przypisane do żadnego klienta:

USE [WideWorldImportersDW]
GO

CREATE NONCLUSTERED INDEX [NCI_CustomerKeyZero] ON [Fact].[Order]
(
       [Customer Key] ASC
)
WHERE [Customer Key]=0

GO

Następnie wykonajmy zapytanie z parametrem, które w teorii powinno skorzystać z indeksu filtrowanego.

DECLARE @CustomerKey INT
SET @CustomerKey=0
SELECT
       [Customer Key]
FROM
       [Fact].[Order]
WHERE 
       [Customer Key]=@CustomerKey

Sprawdźmy czy rzeczywiście tak się stało analizując plan zapytania.

Jak widać nowopowstały indeks filtrowany nie jest używany. Zamiast tego przeszukiwany jest indeks FK_Fact_Order_Customer_Key! Towarzyszy temu ostrzeżenie, które jednak nie pokazuje swojego komunikatu po najechaniu na operator SELECT. Aby go zobaczyć należy w dowolnym miejscu planu kliknąć prawym przyciskiem myszy i wybrać Show Execution Plan XML. Po wyświetleniu planu w wersji XML należy znaleźć sekcję  Warnings.

Informacja ta jest dosyć precyzyjna i pokazuje nam, który indeks mógłby użyty przez optymalizator gdyby ten wiedział w czasie wykonania jaka jest wartość parametru. Dzieje się tak ze względu na fakt, iż zbudowany plan musi być uniwersalny i użyteczny bez względu na fakt jaką wartość przyjmie parametr. Niestety nie ma przejrzystego sposobu na rozwiązanie tego problemu – dlatego też indeksy filtrowane powinny być stosowane wtedy gdy nasze zapytania jawnie wskazują wartość dla kolumny(kolumn) na której założony został ten właśnie indeks.

Częściowym wyjściem z tej sytuacji jest wymuszenie użycia indeksu wtedy gdy mamy pewność, że dane ze względu na wartość parametru będą zawierać się w indeksie filtrowanym.

NoJoinPredicate

Ostatnim ostrzeżeniem jaki omówimy w ramach niniejszego artykułu jest NoJoinPredicate. Część programistów, którzy chcą połączyć co najmniej dwie tabele używa starej składni zdefiniowanej w specyfikacji ANSI SQL-89 tj. zamiast  standardowej klauzuli JOIN … ON  wypisuje wszystkie tabele w sekcji FROM, a warunki złączenia przenosi do WHERE. Jak możecie się domyślić składnia ta jest niezalecana, dodatkowo jej użycie wiąże się z ryzykiem związanym z faktem, iż zapomnimy jakiegoś warunku w sekcji WHERE i otrzymamy iloczyn kartezjański. Przypuśćmy, że chcemy pobrać zamówienia wraz z ich datami, klientem który je złożył oraz osobą która obsługiwała to zamówienie od strony handlowej. Użyje starej składni i celowo pominę sekcję WHERE.

SELECT
       O.[Customer Key],
       O.[Salesperson Key],
       O.[Quantity],
       D.Date
FROM
       [Fact].[Order] AS O,
       [Dimension].[Date] AS D

Jak widać tym razem ostrzeżenie pojawiło się na operatorze złączenia Nested Loops i jest reprezentowane graficznie przez czerwony znaczek. Po najechaniu na ten operator otrzymamy komunikat o braku operatora złączenia.

Jak uniknąć tego problemu? To bardzo proste! Zapomnijmy o składni ANSI SQL-89 i używajmy JOIN ON! Silnik nie pozwoli nam na podobny problem w nowej składni – gdy spróbujemy wykonać JOIN bez ON to SQL Server zwróci nam błąd składnikowy. W przypadku gdy chcemy uzyskać iloczyn kartezjański również nie używajmy starej składni, a specjalnie przygotowanej do tego celu klauzuli CROSS JOIN.

To wszystkie ostrzeżenia optymalizatora, które chciałem Wam przedstawić. Oczywiście artykuł nie obejmuje całej wiedzy z zakresu ostrzeżen, jest ich zdecydowanie więcej -miał on jedynie za zadanie przybliżyć Wam najpopularniejsze z nich, przedstawić powody ich występowania i sposoby na ich uniknięcie. Mam nadzieję, że niniejszy artykuł okaże się dla Was przydatny.