SQL Server 2019 – Intelligent Query Processing – Table Variable Deferred Compilation

Trzeci artykuł z serii poświęconej SQL Server 2019 i jego rodziny usprawnień znany jako Intelligent Query Processing. Dziś powiemy sobie o funkcjonalności znanej jako Table Variable Deferred Compilation czyli tłumacząc na nasz ojczysty język Opóźniona kompilacja zmiennych tabelarycznych – zapraszam do lektury.

Problemów ze zmiennymi tabelarycznymi raczej nie trzeba przedstawiać ale w skrócie powiem, że wszystko opiera się o fakt, że obiekty tego typu nie posiadają statystyk. W przypadku gdy odpytujemy takie zmienne to SQL Server zakłada, że jest tam jeden wiersz co dla małych zbiorów jest w porządku ale gdy mamy już więcej wierszy to może to być poważny problem.

Zobrazujmy sobie ten przypadek na konkretnym przykładzie – na wstępie wygenerujemy sobie odpowiednio dużą tabelę źródłową. Zrobimy to poprzez multiplikację tabeli FactInternetSales z AdventureworksDW2017:

SELECT *
INTO #tmpFIS
FROM
[dbo].[FactInternetSales];


INSERT INTO #tmpFIS
SELECT * 
FROM 
[dbo].[FactInternetSales];
GO 15

Po wykonaniu powyższego zapytania nasza tabela źródłowa powinna zawierać około miliona wierszy. Z całą pewnością to wystarczy aby osiągnąć pożądany efekt. W dalszym kroku wywołamy zapytanie, które uzupełni naszą zmienną tabelaryczną i użyje ją w prostym złączeniu. Pamiętamy, że cykl życia zmiennej tabelarycznej zamyka się w pojedynczym batchu dlatego też wykonujemy wszystko “na raz” z włączonym planem wykonania – dodatkowo przełączyłem compatibility level na 140 aby Table Variable Deferred Compilation nie zadziałało:

ALTER DATABASE AdventureworksDW2017 SET COMPATIBILITY_LEVEL =150

DECLARE @FactInternetSales TABLE
(
	[ProductKey] [int] NULL,
	[OrderDateKey] [int] NULL,
	[DueDateKey] [int] NULL,
	[ShipDateKey] [int] NULL,
	[CustomerKey] [int] NULL,
	[PromotionKey] [int] NULL,
	[CurrencyKey] [int] NULL,
	[SalesTerritoryKey] [int] NULL,
	[SalesOrderNumber] [nvarchar](20) NULL,
	[SalesOrderLineNumber] [tinyint] NULL,
	[RevisionNumber] [tinyint] NULL,
	[OrderQuantity] [smallint] NULL,
	[UnitPrice] [money] NULL,
	[ExtendedAmount] [money] NULL,
	[UnitPriceDiscountPct] [float] NULL,
	[DiscountAmount] [float] NULL,
	[ProductStandardCost] [money] NULL,
	[TotalProductCost] [money] NULL,
	[SalesAmount] [money] NULL,
	[TaxAmt] [money] NULL,
	[Freight] [money] NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL,
	[OrderDate] [datetime] NULL,
	[DueDate] [datetime] NULL,
	[ShipDate] [datetime] NULL
)
INSERT INTO @FactInternetSales
SELECT *
FROM
#tmpFIS;

SELECT
	 FIS.ProductKey
	,FIS.DueDate
	,FIS.DiscountAmount
	,FIS.CarrierTrackingNumber
	,FIS.OrderDate
	,FIS.OrderQuantity
	,FIS.SalesAmount
	,DP.EnglishProductName AS ProductName
FROM
	@FactInternetSales AS FIS
JOIN [dbo].[DimProduct] AS DP
	ON DP.ProductKey=FIS.ProductKey
ORDER BY 
	FIS.OrderDate DESC

Na powyższym zrzucie ekranowym możemy dostrzec co najmniej kilka problemów. Pierwszym z nich jest to, że estymowana liczba wierszy wynosi 1, a aktualna ponad 900 tysięcy co jest spowodowane brakiem statystyk na zmiennej tabelarycznej. Kolejne dwa problemy wiążą się z pierwszym i są to kolejno zrzut na dysk operatora Sort (ponieważ SQL Server “myślał” że mamy do czynienia z 1 wierszem dlatego zbyt mało pamięci zostało zarezerwowane dla tego operatora) oraz operator złączenia Nested Loops (byłyby efektywny przy mniejszym zbiorze danych ale w przypadku około miliona wierszy raczej nie jest to najbardziej optymalny wybór). Problemem może być również wykonanie jednowątkowe bo SQL Server uznał, że nie warto angażować więcej niż jednego wątku skoro mamy do obrobienia tak mało wierszy.

W praktyce często możemy spotkać opinie, że zmienne tabelaryczne są spoko bo są “in-memory” co w przypadku tradycyjnych konstruktów takich jak powyżej całkowicie mija się z prawdą. Co prawda da się osiągnąć taki efekt ale przy wykorzystaniu obiektów “in-memory OLTP”, a nie zwykłych zmiennych tabelarycznych. Jak więc sobie poradzić z brakiem statystyk na zmiennych tabelarycznych? Odpowiedzią na to pytanie mogą być oczywiście tabele tymczasowe, które takowe statystyki posiadają i z całą pewnością jest to dobra odpowiedź. Jeśli jednak nie możemy w łatwy sposób się przepiąć to mamy do dyspozycji trzy rozwiązania.

Pierwszym z nich jest użycie klauzuli OPTION (RECOMPILE), która spowoduje rekompilację planu i otrzymanie prawidłowych statystyk:

DECLARE @FactInternetSales TABLE
....
SELECT
	 FIS.ProductKey
	,FIS.DueDate
	,FIS.DiscountAmount
	,FIS.CarrierTrackingNumber
	,FIS.OrderDate
	,FIS.OrderQuantity
	,FIS.SalesAmount
	,DP.EnglishProductName AS ProductName
FROM
	@FactInternetSales AS FIS
JOIN [dbo].[DimProduct] AS DP
	ON DP.ProductKey=FIS.ProductKey
ORDER BY 
	FIS.OrderDate DESC
OPTION (RECOMPILE)

W efekcie otrzymaliśmy zdecydowanie inny plan wykonania:

Co prawda nadal mamy za mały przydział pamięci przy operatorze Sort ale widzimy, że plan wykonania jest wielowątkowy, plus dodatkowo jako algorytm złączenia wybrany został Hash Match. Podobne rezultaty otrzymamy uruchamiając flagę 2453, która jest dostępna dla SQL Server 2012 z co najmniej zainstalowanym SP2, SQL Server 2014 CU3 oraz od RTM dla SQL Server 2016 i wyżej.

DBCC TRACEON (2453,-1)

DECLARE @FactInternetSales TABLE
...
SELECT
	 FIS.ProductKey
	,FIS.DueDate
	,FIS.DiscountAmount
	,FIS.CarrierTrackingNumber
	,FIS.OrderDate
	,FIS.OrderQuantity
	,FIS.SalesAmount
	,DP.EnglishProductName AS ProductName
FROM
	@FactInternetSales AS FIS
JOIN [dbo].[DimProduct] AS DP
	ON DP.ProductKey=FIS.ProductKey
ORDER BY 
	FIS.OrderDate DESC

DBCC TRACEOFF (2453,-1)

Jeśli ktoś byłby zainteresowany odsyłam do dokumentacji Microsoftu na temat tej właśnie flagi (link). Ogólnie rzecz biorąc różnica pomiędzy flagą i wymuszeniem rekompilacji polega na tym kiedy dana rekompilacja występuje. Przy OPTION (RECOMPILE) każde wywołanie zapytania powoduje rekompilację, a flaga powoduje rekompilację tylko wtedy gdy przekroczony zostanie wewnętrzny próg ilości wierszy.

Kolejna możliwość pojawia się wraz z SQL Server 2019 i jest nią Table Variable Deferred Compilation. Opcja ta jest dostępna oczywiście dla compatibility level 150. Technologia ta polega na tym, że moment kompilacji jest nieco opóźniony dzięki czemu do budowania planu używane są aktualna ilość wierszy znajdująca się w zmiennej tabelarycznej. Wcześniej kompilacja następowała wtedy gdy innych zapytań w batchu czyli nie było wiadomo z jaką ilością wierszy mamy do czynienia dlatego SQL przyjmował, że jest tam jeden wiersz. Nie oznacza to oczywiście, że nagle pojawiły się statystyki przypisane do zmiennej, a jedyne co się zmieniło to moment kompilacji.

Sprawdźmy co się stanie w momencie gdy zmienimy CL (bo właściwie tylko tyle musimy zrobić żeby technologia ta została włączona):

ALTER DATABASE [AdventureworksDW2017] SET COMPATIBILITY_LEVEL = 150;

Na początku wykonajmy proste zliczenie wierszy:

DECLARE @FactInternetSales TABLE
(
	[ProductKey] [int] NULL,
	[OrderDateKey] [int] NULL,
	[DueDateKey] [int] NULL,
	[ShipDateKey] [int] NULL,
	[CustomerKey] [int] NULL,
	[PromotionKey] [int] NULL,
	[CurrencyKey] [int] NULL,
	[SalesTerritoryKey] [int] NULL,
	[SalesOrderNumber] [nvarchar](20) NULL,
	[SalesOrderLineNumber] [tinyint] NULL,
	[RevisionNumber] [tinyint] NULL,
	[OrderQuantity] [smallint] NULL,
	[UnitPrice] [money] NULL,
	[ExtendedAmount] [money] NULL,
	[UnitPriceDiscountPct] [float] NULL,
	[DiscountAmount] [float] NULL,
	[ProductStandardCost] [money] NULL,
	[TotalProductCost] [money] NULL,
	[SalesAmount] [money] NULL,
	[TaxAmt] [money] NULL,
	[Freight] [money] NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL,
	[OrderDate] [datetime] NULL,
	[DueDate] [datetime] NULL,
	[ShipDate] [datetime] NULL
)
INSERT INTO @FactInternetSales
SELECT *
FROM
#tmpFIS;


SELECT COUNT(*) FROM @FactInternetSales

Na planie widzimy prawidłową liczbę wierszy:

Dla pełnego zapytania również widzimy pełną zgodność estymacji:

Naprawdę dobrze to wygląda! Czy mamy jeszcze jakieś opcje związane z opisywanym mechanizmem. Oczywiście tak jak pozostałe technologie w ramach Intelligent Query Processing tak również omawianą funkcjonalność możemy wyłączyć na poziomie ustawień bazy danych:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

lub też wyłączyć na poziomie pojedynczego zapytania:

OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

To by było na tyle. Czy to oznacza koniec problemów ze zmiennymi tabelarycznymi? W pewnym sensie tak jednakże ja nadal pozostanę przy moich tabelach tymczasowych i statystykach, które one posiadają. technologia ta jak wszystkie wchodzące w skład Intelligent Query Processing są krokiem do przodu, a omawiany mechanizm w szczególności pomaga już istniejącemu kodowi i należy o tym pamiętać. Pozdrawiam!