SQL Server 2019 – Intelligent Query Processing – Scalar Function Inlining

Premiera SQL Server 2019 za nami, a wraz z nią pojawił się cały szereg nowych funkcjonalności. Jedną z nich jest rozszerzenie koncepcji Adaptive Query Processing czyli zestawu technologii, które umożliwiają dostosowanie sposobu wykonania zapytania podczas jego wykonania. Obecnie cała rodzina tych technologii nazywana jest Intelligent Query Processing, a jej schemat poglądowy możecie zobaczyć na poniższej grafice:

Część z tych funkcjonalności zdołałem już opisać w ramach krótkiej serii trzech artykułów poświęconych Adaptive Query Processing (jeśli ktoś nie jest z nimi zaznajomiony linki poniżej):

Dziś chciałbym poszerzyć nieco całą koncepcję i opisać kolejną funkcjonalność, którą jest TSQL Scalar UDF Inlining.

O tym jak dużym problemem były skalarne funkcje użytkownika raczej nie trzeba mówić. W wielu przypadkach wydajność była niewystarczająca z powodu problemu z wielowątkowością. Pokażmy to na konkretnym przykładzie, na wstępie ustawimy cost treshold of parallelism na 0 tzn. każde zapytanie będzie rozpatrywane jako wielowątkowe:

sp_configure
'show advanced',
1;
RECONFIGURE;
GO
sp_configure
'cost thre';
GO
sp_configure
'cost thre',
0;

W dalszej kolejności użyjemy bazy WideWorldImportersDW i zmienimy jej Compatibility Level na 140 (czyli zgodny z SQL Server 2017, a więc bez Scalar Function Inlining):

use WideWorldImporters
GO

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140;
GO

Do wykonania mamy zapytanie, które wywołuje kilka i złączeń i funkcji okna:

SELECT 
	SI.InvoiceDate,
	StockItemName,
	ROW_NUMBER() OVER( ORDER BY StockItemName),
	SUM(SUM(QUantity)) OVER( ORDER BY SI.InvoiceDate),
	SUM(SUM(QUantity)) OVER(ORDER BY SI.InvoiceDate 
							ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING),
	SUM(SUM(QUantity)) OVER(ORDER BY SI.InvoiceDate 
							ROWS BETWEEN 10 PRECEDING AND 3 FOLLOWING),
	SUM(QUANTITY) AS Qty
FROM [Sales].[Invoices] AS SI
JOIN [Sales].[InvoiceLines] AS IL ON SI.InvoiceID = IL.InvoiceID
JOIN [Warehouse].[StockItems] AS WSI ON IL.StockItemId = WSI.StockItemID
GROUP BY SI.InvoiceDate,
StockItemName
ORDER BY SI.InvoiceDate ASC;

Plan wykonania (a raczej jego część) wygląda następująco:

Nie wgłębiając się w poszczególne elementy samego planu możemy zauważyć, że poszczególne operatory są wykonywane na wielu wątkach. Powiedzmy, że chcemy usunąć spacje znajdujące się zarówno z przodu jak i z tyłu w zadanym tekście (pomijamy całkowicie fakt, że od SQL Server 2017 istnieje funkcja TRIM):

CREATE FUNCTION dbo.Trim(@text nvarchar(100))
RETURNS nvarchar(100)
AS
BEGIN
RETURN LTRIM(RTRIM(@text));
END

Powyższa funkcja jest funkcją skalarną czyli zwracającą pojedynczy wynik. Nasze zapytanie wraz z nią wygląda następująco:

SELECT 
	SI.InvoiceDate,
	dbo.Trim(StockItemName) AS StockItemName,
	ROW_NUMBER() OVER( ORDER BY StockItemName),
	SUM(SUM(QUantity)) OVER( ORDER BY SI.InvoiceDate),
	SUM(SUM(QUantity)) OVER(ORDER BY SI.InvoiceDate 
							ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING),
	SUM(SUM(QUantity)) OVER(ORDER BY SI.InvoiceDate 
							ROWS BETWEEN 10 PRECEDING AND 3 FOLLOWING),
	SUM(QUANTITY) AS Qty
FROM [Sales].[Invoices] AS SI
JOIN [Sales].[InvoiceLines] AS IL ON SI.InvoiceID = IL.InvoiceID
JOIN [Warehouse].[StockItems] AS WSI ON IL.StockItemId = WSI.StockItemID
GROUP BY SI.InvoiceDate,
StockItemName
ORDER BY SI.InvoiceDate ASC;

Plan wykonania nieco różni się od tego, który przedstawiłem w poprzednim przypadku:

Co się stało z wielowątkowością? A no właśnie, cały plan stał się seryjny właśnie przez użycie stworzonej funkcji skalarnej.

Spróbujmy teraz zobaczyć jak wygląda sytuacja w SQL Server 2019 czyli zmieniamy Compatibility Level na 150:

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 150;
GO

Po uruchomieniu naszego zapytania i podejrzeniu planu możemy dostrzec na nim następujące operatory:

Mimo wywołania skalarnej funkcji użytkownika plan wykonywany jest na wielu wątkach o czym świadczy występowanie operatora Parallelism oraz graficzny znak z dwoma strzałkami wskazujący na wielowątkowość każdego ze wskazanych operatorów. Dodatkowo we właściwościach możemy dostrzec nową właściwość tj. Contains Inline Scalar Tsql Udfs ustawioną na TRUE:

Co się tak naprawdę stało i jak działa Scalar Function Inlining? W skrócie chodzi o to, że funkcja jest jest niejako interpretowana przez SQL i jej kod jest wywoływany tak jakby znajdował się w naszym zapytaniu zamiast ciągłego wywoływania zapisanej funkcji. Fakt ten możemy dostrzec w kodzie XML planu zapytania – tak to wygląda w starym podejściu:

<ScalarOperator ScalarString="[WideWorldImporters].[dbo].[Trim]([WideWorldImporters].[Warehouse].[StockItems].[StockItemName] as [WSI].[StockItemName])">
     <UserDefinedFunction FunctionName="[WideWorldImporters].[dbo].[Trim]">
      <ScalarOperator>
        <Identifier>
         <ColumnReference Database="[WideWorldImporters]" Schema="[Warehouse]" Table="[StockItems]" Alias="[WSI]" Column="StockItemName" />
        </Identifier>
      </ScalarOperator>
     </UserDefinedFunction>
</ScalarOperator>

W zapytaniu z SQL Server 2019 widzimy wywołanie “ciała” stworzonej przez nas funkcji skalarnej:

<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(100),ltrim(rtrim([WideWorldImporters].[Warehouse].[StockItems].[StockItemName] as [WSI].[StockItemName])),0)">
                      <Convert DataType="nvarchar" Length="200" Style="0" Implicit="true">
                        <ScalarOperator>
                          <Intrinsic FunctionName="ltrim">
                            <ScalarOperator>
                              <Intrinsic FunctionName="rtrim">
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Database="[WideWorldImporters]" Schema="[Warehouse]" Table="[StockItems]" Alias="[WSI]" Column="StockItemName" />
                                  </Identifier>
                                </ScalarOperator>
                              </Intrinsic>
                            </ScalarOperator>
                          </Intrinsic>
                        </ScalarOperator>
                      </Convert>
                    </ScalarOperator>

Naprawdę wygląda to całkiem nieźle. Pod kątem porównania kosztowego widzimy znaczącą różnicę:

Przy większych zapytaniach również powinniśmy dostrzec różnicę wydajnościową. Oczywiście nie wszystkie funkcje będą mogły być przepisane w ramach mechanizmu Scalar Function Inlining, sprawdzimy to za pomoca widoku sys.sql_modules:

SELECT 
	 definition
	,inline_type
	,is_inlineable
FROM sys.sql_modules
WHERE definition not like '%CREATE PROCEDURE%'
		and definition not like '%CREATE VIEW%'

Ten systemowy widok zwraca nam nasze funkcje oraz Atrybut is_inlineable wskazującą na to, czy stworzona przez nas funkcja w ogóle może być przepisana. Inline_type wskazuje czy opisywana funkcjonalność jest włączona (1) lub nie (0). Tutaj pojawia się pytanie kiedy funkcja nie będzie mogła być przepisana? Pełną listę wymogów znajdziecie tutaj – oto wybrane z nich:

funkcja nie używa funkcji niedeterministycznych opartych o czas jak GETDATE()
funkcja używa domyślnego ustawienia EXECUTE AS CALLER,
funkcja nie odwołuje się do zmiennych tabelarycznych lub parametrów tabelarycznych,
funkcja użytkownika nie jest używana w sekcji ORDER BY.
funkcja nie jest funkcją partycjonującą.

Pełną listę obostrzeń i ogólny opis znajdziecie oczywiście w dokumentacji (klik). Na ten moment oceniam opisywany mechanizm jako coś naprawdę wartego uwagi. Może on pomóc w wielu przypadkach bo z doświadczenia wiem, że funkcje użytkownika (w tym również te skalarne) są lubianą i popularną strukturą. Scalar Function Inlinging daje nam nadzieję na to, że niektóre problemy z nimi związane powoli odchodzą do lamusa.Pozdrawiam!