SQL Server 2022 - Parameter Sensitivity Plan

In diesem Jahr wird die neue SQL Server Version SQL Server 2022 veröffentlicht. Als Premium Partner durften wir bereits in den Genuss der neuen Features kommen und stellen Ihnen ein neues Feature in diesem Artikel etwas näher vor.

Die Parameter Sensitivity Plan (PSP) Optimierung ist ein Teil der Funktionsfamilie Intelligente Abfrageverarbeitung. Sie befasst sich mit dem Szenario, einen zwischengespeicherten Plan zur Parametrisierung von Abfragen so zu optimieren, sodass er für jegliche eingehende Parameterwerte funktioniert. Dies ist der Fall bei uneinheitlichen Datenverteilungen.

Verständnis der Parametrisierung

In der SQL Server-Datenbank-Engine verbessert die Verwendung von Parametern oder Parametermarkierungen in Transact-SQL-Anweisungen die Fähigkeit der relationalen Engine. Sie fördern die Abgleichung neuer Transact-SQL-Anweisungen mit vorhandenen Ausführungsplänen. Sind diese erfolgreich, werden sie wiederverwendet.

Das standardmäßige einfache Parametrisierungsverhalten von SQL Server kann außer Kraft gesetzt werden, indem Sie angeben, dass alle SELECT-, INSERT-, UPDATE- und DELETE-Anweisungen in einer Datenbank mit bestimmten Einschränkungen parametrisiert werden.

Wie PSP funktioniert

Die PSP-Optimierung ist standardmäßig aktiviert. Ist dies nicht der Fall, können sie diesen mit dem folgenden Befehl aktivieren/deaktivieren:

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON

Mainzer Datenfabrik - SQL Server 2022 - Parameter Sensitivity Plan

Mit SQL Server 2019 oder älteren Versionen haben Sie oft einige gespeicherte Prozeduren, die sehr empfindlich auf die Art und Verwendung der Eingabeparameterwerte reagieren. SQL Server verwendet dafür das Parameter-Sniffing, um einen Parameterwert zu ermitteln und basierend darauf einen ausreichenden Abfrageplan zu erstellen.

Bei einigen Parameterwerten, die nur wenige Zeilen zurückgeben, kann SQL Server beispielsweise eine Indexsuche verwenden. Bei Parameterwerten, die viele Zeilen zurückgeben, verwendet SQL Server standardmäßig einen Tabellenscan. Welcher Parameterwert bei der ersten Ausführung der Abfrage verwendet wird, bestimmt welcher Typ des Abfrageplans (seek oder scan) in den Plan-Cache geht.

Parameter-Sniffing funktioniert im Allgemeinen sehr gut, führt aber manchmal ohne ersichtlichen Grund zu enormen Leistungseinbußen. Zunächst ist die Performance gut, bricht dann allerdings durch schlechte, zwischengespeicherte Abfragepläne rapide ein. Eine Möglichkeit zur Vermeidung des Performanceeinbruchs ist das Einfrieren des Abfrageplans im Abfragespeicher.

Die parameterabhängige Planoptimierung speichert absichtlich mehrere aktive Abfragepläne pro gespeicherte Prozedur oder Abfrage. Damit haben Sie die Möglichkeit, Probleme mit parameterabhängigen Abfrageplänen ohne Codeänderungen und ohne Verwendung des Abfragespeichers zu entschärfen. Alles, was Sie dafür tun müssen ist, Ihre Datenbankkompatibilitätsstufe auf 160 zu ändern. Um die Änderung vorzunehmen, empfehlen wir nachfolgenden Code zu verwenden:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160

Tests

Zum Testen nutzen wir die AdventureWorks2019-Datenbank. Um die Kerne des Laptops auszulasten, haben wir zwei neue Tabellen mit größeren Datenmengen erstellt. Dafür haben wir das folgende Skript verwendet und Tabellen in dbo.bigProduct und dbo.bigTransactionHistory erstellt.

USE AdventureWorks2019
GO

SELECT
	p.ProductID + (a.number * 1000) AS ProductID,
	p.Name + CONVERT(VARCHAR, (a.number * 1000)) AS Name,
	p.ProductNumber + '-' + CONVERT(VARCHAR, (a.number * 1000)) AS ProductNumber,
	p.MakeFlag,
	p.FinishedGoodsFlag,
	p.Color,
	p.SafetyStockLevel,
	p.ReorderPoint,
	p.StandardCost,
	p.ListPrice,
	p.Size,
	p.SizeUnitMeasureCode,
	p.WeightUnitMeasureCode,
	p.Weight,
	p.DaysToManufacture,
	p.ProductLine,
	p.Class,
	p.Style,
	p.ProductSubcategoryID,
	p.ProductModelID,
	p.SellStartDate,
	p.SellEndDate,
	p.DiscontinuedDate
INTO bigProduct
FROM Production.Product AS p
CROSS JOIN master..spt_values AS a
WHERE
	a.type = 'p'
	AND a.number BETWEEN 1 AND 50
GO

ALTER TABLE bigProduct
ALTER COLUMN ProductId INT NOT NULL	
GO

ALTER TABLE bigProduct
ADD CONSTRAINT pk_bigProduct PRIMARY KEY (ProductId)
GO

SELECT 
	ROW_NUMBER() OVER 
	(
		ORDER BY 
			x.TransactionDate,
			(SELECT NEWID())
	) AS TransactionID,
	p1.ProductID,
	x.TransactionDate,
	x.Quantity,
	CONVERT(MONEY, p1.ListPrice * x.Quantity * RAND(CHECKSUM(NEWID())) * 2) AS ActualCost
INTO bigTransactionHistory
FROM
(
	SELECT
		p.ProductID, 
		p.ListPrice,
		CASE
			WHEN p.productid % 26 = 0 THEN 26
			WHEN p.productid % 25 = 0 THEN 25
			WHEN p.productid % 24 = 0 THEN 24
			WHEN p.productid % 23 = 0 THEN 23
			WHEN p.productid % 22 = 0 THEN 22
			WHEN p.productid % 21 = 0 THEN 21
			WHEN p.productid % 20 = 0 THEN 20
			WHEN p.productid % 19 = 0 THEN 19
			WHEN p.productid % 18 = 0 THEN 18
			WHEN p.productid % 17 = 0 THEN 17
			WHEN p.productid % 16 = 0 THEN 16
			WHEN p.productid % 15 = 0 THEN 15
			WHEN p.productid % 14 = 0 THEN 14
			WHEN p.productid % 13 = 0 THEN 13
			WHEN p.productid % 12 = 0 THEN 12
			WHEN p.productid % 11 = 0 THEN 11
			WHEN p.productid % 10 = 0 THEN 10
			WHEN p.productid % 9 = 0 THEN 9
			WHEN p.productid % 8 = 0 THEN 8
			WHEN p.productid % 7 = 0 THEN 7
			WHEN p.productid % 6 = 0 THEN 6
			WHEN p.productid % 5 = 0 THEN 5
			WHEN p.productid % 4 = 0 THEN 4
			WHEN p.productid % 3 = 0 THEN 3
			WHEN p.productid % 2 = 0 THEN 2
			ELSE 1 
		END AS ProductGroup
	FROM bigproduct p
) AS p1
CROSS APPLY
(
	SELECT
		transactionDate,
		CONVERT(INT, (RAND(CHECKSUM(NEWID())) * 100) + 1) AS Quantity
	FROM
	(
		SELECT 
			DATEADD(dd, number, '20050101') AS transactionDate,
			NTILE(p1.ProductGroup) OVER 
			(
				ORDER BY number
			) AS groupRange
		FROM master..spt_values
		WHERE 
			type = 'p'
	) AS z
	WHERE
		z.groupRange % 2 = 1
) AS x

ALTER TABLE bigTransactionHistory
ALTER COLUMN TransactionID INT NOT NULL
GO

ALTER TABLE bigTransactionHistory
ADD CONSTRAINT pk_bigTransactionHistory PRIMARY KEY (TransactionID)
GO

CREATE NONCLUSTERED INDEX IX_ProductId_TransactionDate
ON bigTransactionHistory
(
	ProductId,
	TransactionDate
)
INCLUDE 
(
	Quantity,
	ActualCost
)
GO

Wir verwenden folgende Abfrage mit verschiedenen Parametern, um PSP zu aktivieren:

EXEC sp_executesql 
	N'
	select TransactionId, Quantity, ActualCost, TransactionDate
	from dbo.bigTransactionHistory
	where TransactionDate = @date'
	, N'@date datetime'
	, '2005-06-01 00:00:00';
GO
EXEC sp_executesql 
	N'
	select TransactionId, Quantity, ActualCost, TransactionDate
	from dbo.bigTransactionHistory
	where TransactionDate = @date'
	, N'@date datetime'
	, '2010-06-01 00:00:00';
GO

Nun erhalten wir 2 verschiedene Ausführungspläne, ohne damit ein Recompile zu erzwingen.

Basierend auf dem Parameterwert, der beim Ausführen der Abfrage angegeben wurde, wählt SQL Server den optimalen Plan aus, der passen zur Laufzeit verwendet werden soll.

Test Conclusion

Die parameterabhängige Planoptimierung funktioniert wie in SQL Server 2022 CTP2.0 beschrieben. Allerdings ist es hilfreich, viele Tests durchzuführen, allein um die Breite der Möglichkeiten der Funktion zu verstehen. Letztlich lässt sich festhalten, dass es sich um eine vielversprechende Funktion handelt, die zur Stabilisierung der Datenbankperformance beitragen kann.

Möchten Sie mehr zu den neuen Features rund um SQL Server 2022 erfahren? Dann kontaktieren Sie uns gerne über das Kontaktformular - wir stehen Ihnen rund um die Themen SQL Server Optimierung zur Seite.

SQL Server 2022 - Parameter Sensitivity Plan

Verständnis der Parametrisierung

Wie PSP funktioniert

Tests

Test Conclusion

Auf dieser Seite

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.