In einem Workshop, den ich mit einem Kunden – auf Grund der aktuellen Situation – remote durchgeführt habe, kam unter anderem die Frage auf, ob Einstellungen, die explizit für Datenbanken vorgenommen werden können (Database Scoped Configuration) durch gleiche Einstellungen auf Ebene des Servers überschrieben werden. In diesem Fall ging es konkret um die Konfigurationseinstellungen für Parallelisierung. Ich habe hierzu diverse Tests durchgeführt und in diesem Artikel veröffentlicht.

Max Degree of Parallelism

Wenn eine Instanz von SQL Server mit mehreren Cores ausgeführt wird, für die Ausführung einer einzelnen Abfrage bei Erreichen eines Schwellwerts (Cost Threshold for Parallelism) eine parallele Ausführung durchgeführt. Dies gilt für jede Ausführung eines parallelen Plans. Mithilfe der Option Max. Grad an Parallelität kann die Anzahl der Prozessoren beschränkt werden, die bei der Ausführung paralleler Pläne verwendet werden.

Hinweis

Mit SQL Server 2019 (15.x) ist es erstmals möglich, dass bereits bei der Installation diese Option konfiguriert werden kann.

Datenbankspezifische Konfigurationseinstellungen können in Eigenschaften -> Optionen angepasst werden.

Die nachfolgenden Tests habe ich mit einer Demo-Datenbank (ab Microsoft SQL Server 2012) durchgeführt, die hier heruntergeladen werden kann: https://bitly.com/CustomerOrdersDB

Zunächst werden auf Instanzebene die Standardeinstellungen für die Parallelitätsoptionen konfiguriert. Mit dieser Einstellung wird sichergestellt, dass Microsoft SQL Server immer ALLE zur Verfügung stehenden Cores verwendet, wenn eine Abfrage parallelisiert.

EXEC sp_configure N'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure N'cost threshold for parallelism', 5;
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure N'max degree of parallelism', 0;
RECONFIGURE WITH OVERRIDE;
GO

Die in der Abfrage verwendeten Tabellen verwenden KEINE Indexe. Alle Tabellen sind HEAPS. Damit ist gewährleistet, dass die Kosten für die Ausführung der Abfrage so hoch sind, dass eine Parallelisierung bevorzugt wird!

SELECT   C.Name,
         YEAR(CO.OrderDate)              AS  YearOfOrder,
         COUNT_BIG(*)                    AS  NumOfOrders,
         SUM(COD.Quantity * COD.Price)   AS  TotalAmount
 FROM    CustomerOrders.dbo.Customers AS C
         INNER JOIN CustomerOrders.dbo.CustomerOrders AS CO
         ON (C.Id = CO.Customer_Id)
         INNER JOIN CustomerOrders.dbo.CustomerOrderDetails AS COD
         ON (CO.Id = COD.Order_Id)
 WHERE    C.Id <= 10
 GROUP BY
         C.Name,
         YEAR(CO.OrderDate)
 ORDER BY
         C.Name
 OPTION (RECOMPILE);
 GO

Ziel erreicht! Die Kosten für die Abfrage sind so hoch, dass Microsoft SQL Server die Abfrage parallelisiert. In den Eigenschaften des Table Scan Operators kann man sehr gut erkennen, wie die Last auf alle Cores (4) verteilt wird.

Im nächsten Schritt wird explizit für die Datenbank der Wert für die Anzahl der Cores konfiguriert, die für eine parallele Ausführung verwendet werden sollen.

USE [CustomerOrders]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
GO

Wie schaut es aber aus, wenn auf Instanzebene eine restriktivere Einstellung für die Option gewählt wird? Leider gibt es einige Applikationen, die ausdrücklich diesen Wert auf Instanzebene abfragen und einen Wert von 1 verlangen.

Microsoft weist z. B. ausdrücklich darauf hin, dass Microsoft SQL Server für Sharepoint-Installationen für die Option „Max Dop“ den Wert 1 verwenden sollen!

Zitat

Legen Sie für Instanzen von SQL Server, die SharePoint-Datenbanken hosten, einen MAXDOP-Wert (maximaler Grad an Parallelität) von 1 fest, um sicherzustellen, dass jede Anforderung von einem einzigen SQL Server-Prozess verarbeitet wird.

https://docs.microsoft.com/de-de/sharepoint/administration/best-practices-for-sql-server-in-a-sharepoint-server-farm

Also wird für die Testinstanz ebenfalls der Wert für „Max Dop“ auf 1 gesetzt und erneut geprüft, wie sich die Abfrage in der Datenbank verhält.

EXEC sp_configure N'max degree of parallelism', 1;
RECONFIGURE WITH OVERRIDE;
GO

Das Ergebnis sollte nicht überraschen; die Einstellungen der Datenbank gelten für die ausgeführte Abfrage. Trotz instanzweiter Einschränkungen auf maximal 1 Core verwendet die Datenbank auch weiterhin die 2 Cores. Diese Konfiguration wird auch beibehalten, wenn man sich nicht im Kontext der Datenbank selbst befindet! (Beispiel: Die Abfrage wird aus der master-Datenbank ausgeführt)

Einwirkung von Cost Threshold for Parallelism

Für die Parallelisierung gelten zwei wesentliche Konfigurationsparameter. Neben der Anzahl der Cores, die für eine Ausführung verwendet werden können, entscheidet der „Kostenfaktor“ der Abfrage, OB eine Abfrage parallelisieren kann.

Die „geschätzten“ Kosten basieren auf einem Kostenmodell von Microsoft SQL Server Version 7.0

Zitat

Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.

http://sqlblog.com/blogs/joe_chang/archive/2008/03/29/execution-plan-costs.aspx

Leider ist die Konfiguration von „Cost Threshold for Parallelism“ nur auf Instanzebene möglich; eine datenbankbasierte Konfiguration ist – bis jetzt – noch nicht möglich!

EXEC sp_configure N'cost threshold for parallelism', 100;
RECONFIGURE WITH OVERRIDE;
GO

Zusammenfassung

Einstellungen von „Database Scoped Configuration“-Werten überschreiben die Einstellungen von Instanzkonfigurationen; das gilt auch, wenn Instanzwerte restriktiver sind, als die Einstellungen in den Benutzerdatenbanken.

Im Zusammenhang mit Parallelisierung würde ich mir wünschen, wenn neben der Konfigurationsmöglichkeit für die Anzahl der Cores auch eine Möglichkeit besteht, den Kostenschwellwert auf Datenbankebene zu konfigurieren.

Ich persönlich finde „Database Scoped Configuration“ sehr gut; endlich müssen sich nicht mehr die Applikationen den Instanzanforderungen einer restriktiven Anwendung beugen, die solche Restriktionen nicht benötigen.

Ein typisches Szenario, dass ich immer wieder sehe, ist die Verwendung von Sharepoint. Wenn dann Kunden – aus Kostengründen – weitere Applikationsdatenbanken in der gleichen Instanz betreiben wollten, waren sie immer davon abhängig, welche Vorgaben Sharepoint verlangte.

Herzlichen Dank fürs Lesen

Bisher erschienene Artikel zu „Database Scoped Configuration“

• Max Degree of Parallelism
• Parameter Sniffing

Geplante Artikel

• OPTIMIZE_FOR_AD_HOC_WORKLOADS
• Identity Cache
• LEGACY_CARDINALITY_ESTIMATION
• INTERLEAVED_EXECUTION_TVF
• TSQL_SCALAR_UDF_INLINING
• DEFERRED_COMPILATION_TV
• VERBOSE_TRUNCATION_WARNINGS