Verschleißerscheinungen beim SQL Server?

Überblick

Manchmal hat es für unsere Kunden tatsächlich den Anschein, als würde ihre SQL Server Instanz / ihre Datenbank im Laufe der Zeit immer langsamer werden. Die Reaktion ist dann oft die Forderung an das Management: “Wir brauchen neue, bessere Hardware, mehr Hauptspeicher, mehr CPUs”, was im Fall der CPUs implizit zu höheren laufenden Kosten in Form von Lizenzgebühren führt. Ist das wirklich notwendig? Unsere Antwort: Neue Hardware könnte helfen, aber sie wird in den seltensten Fällen tatsächlich benötigt. Das A und O ist in der Regel eine saubere Indizierung. Hier ist es wie beim Kochen: Ohne Salz geht nichts, aber zu viel Salz ist auch nicht gut.

Um fehlende oder überflüssige Indizierung schnell aufzudecken empfehlen wir unser SQL Server Assessment, damit Ihre Datenbank, wie es eine unserer Assessment-Kundinnen formulierte, wieder “schnurrt wie ein Kätzchen”.

Wo liegt das Problem?

Wie bereits erwähnt, muss in den meisten Fällen bei Performanceproblemen die Indizierung betrachtet werden. Hier müssen für eine Entscheidung, wo ein Index hilfreich ist, die where-Kriterien der Abfragen betrachtet werden. Das sollte soweit bekannt sein. Was aber die Performance in gleichem Maße beeinträchtigt, sind

Sortierungen (order by)
Eliminierung von Doubletten (distinct)
Vereinigung von Ergebnismengen unter Ausschluss von Doubletten (union, nicht union all)
Gruppierungen (group by)

Im Fall von 1. ist es klar ersichtlich, aber auch die anderen Operationen benötigen im Hintergrund Sortierungen und Sortierungen sind Gift für die Performance relationaler Datenbanken, da deren Komplexität exponentiell wächst. Der Applikationshersteller wird dies in den meisten Fällen nicht bemerken, denn für ihn und seine Testumgebung ist es subjektiv irrelevant, ob er 100.000 Datensätze mit oder ohne einen unterstützenden Index sortiert: In beiden Fällen dürfte sein Ergebnis innerhalb einer Sekunde bereitstehen.

Setup

Zur Darstellung der Auswirkung geeigneter Indizes wird die von Microsoft bereitgestellte OLTP Beispieldatenbank AdventureWorks2022.bak verwendet. In dieser befindet sich im Schema Sales eine Tabelle mit dem Namen SalesOrderDetail. Eine teilweise Kopie dieser Tabelle wird die Grundlage dieses Beitrags sein. Dazu wird zunächst ihre Struktur kopiert. Die Fremdschlüsselbeziehungen und die erweiterten Eigenschaften (sys.sp_addextendedproperty) werden hierbei nicht berücksichtigt. Außerdem wird die Spalte LineTotal entfernt, diese verfälscht das Ergebnis leicht, weil sie nicht persistiert wird und damit im Ausführungsplan zu zwei weiteren Schritten des Typs “Compute Scalar” führt.

Die Kopie der Tabelle wird mit folgenden Befehlen angelegt:

USE [AdventureWorks2022]
GO

CREATE TABLE [dbo].[SalesOrderDetail](
	[SalesOrderID] [int] NOT NULL,
	[SalesOrderDetailID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[OrderQty] [smallint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[SpecialOfferID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[UnitPriceDiscount] [money] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED 
(
	[SalesOrderID] ASC,
	[SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount]  DEFAULT ((0.0)) FOR [UnitPriceDiscount]
GO

ALTER TABLE [dbo].[SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_rowguid]  DEFAULT (newid()) FOR [rowguid]
GO

ALTER TABLE [dbo].[SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

ALTER TABLE [dbo].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_OrderQty] CHECK  (([OrderQty]>(0)))
GO

ALTER TABLE [dbo].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_OrderQty]
GO

ALTER TABLE [dbo].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_UnitPrice] CHECK  (([UnitPrice]>=(0.00)))
GO

ALTER TABLE [dbo].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPrice]
GO

ALTER TABLE [dbo].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount] CHECK  (([UnitPriceDiscount]>=(0.00)))
GO

ALTER TABLE [dbo].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount]
GO

USE [AdventureWorks2022]
GO

CREATE TABLE [dbo].[SalesOrderDetail](
	[SalesOrderID] [int] NOT NULL,
	[SalesOrderDetailID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[OrderQty] [smallint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[SpecialOfferID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[UnitPriceDiscount] [money] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED 
(
	[SalesOrderID] ASC,
	[SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount]  DEFAULT ((0.0)) FOR [UnitPriceDiscount]
GO

ALTER TABLE [dbo].[SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_rowguid]  DEFAULT (newid()) FOR [rowguid]
GO

ALTER TABLE [dbo].[SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

ALTER TABLE [dbo].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_OrderQty] CHECK  (([OrderQty]>(0)))
GO

ALTER TABLE [dbo].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_OrderQty]
GO

ALTER TABLE [dbo].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_UnitPrice] CHECK  (([UnitPrice]>=(0.00)))
GO

ALTER TABLE [dbo].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPrice]
GO

ALTER TABLE [dbo].[SalesOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount] CHECK  (([UnitPriceDiscount]>=(0.00)))
GO

ALTER TABLE [dbo].[SalesOrderDetail] CHECK CONSTRAINT [CK_SalesOrderDetail_UnitPriceDiscount]
GO

In einem zweiten Schritt werden die ersten 100.000 Datensätze von Sales.SalesOrderDetail nach dbo.SalesOrderDetail übernommen. Hierbei wird die Spalte SalesOrderDetailID ausgenommen, weil diese von der DB vergeben wird.

insert into [dbo].[SalesOrderDetail](
	  [SalesOrderID]
	, [CarrierTrackingNumber]
	, [OrderQty]
	, [ProductID]
	, [SpecialOfferID]
	, [UnitPrice]
	, [UnitPriceDiscount]
	, [rowguid]
	, [ModifiedDate])
select top(100000)
          [SalesOrderID]
	, [CarrierTrackingNumber]
	, [OrderQty]
	, [ProductID]
	, [SpecialOfferID]
	, [UnitPrice]
	, [UnitPriceDiscount]
	, [rowguid]
	, [ModifiedDate]
from [Sales].[SalesOrderDetail]

Wie zu Beginn dieses Abschnitts ausgeführt, hier eine Abfrage der Tabellendaten inkl. der berechneten, nicht persistierten Spalte LineTotal: Im zweiten und dritten Knoten tritt jeweils ein Schritt mit dem Namen “Compute Scalar“ auf, der zur Berechnung des Ausdrucks "(isnull((UnitPrice)[OrderQty],(0.0)))" (vgl. Design der Originaltabelle) auf.

Ausführungsplan ohne Index und inkl. der berechneten Spalte LineTotal

Weitere vorbereitende Schritte

Der Agent-Job wurde für die Dauer der Testreihen deaktiviert, so dass eventuelle DB-Jobs (TLog-Backups) nicht zu einer Verfälschung der Ergebnisse führen.
Die Parallelverarbeitung wurde für die Testdatenbank deaktiviert. Wir wollen hier nicht die optimale Performance erreichen, sondern eine Abfrage unter zwei unterschiedlichen Bedingungen miteinander vergleichen. Im Fall der Sortierung verwendet die MSSQL Instanz im Normalfall ab einem vorgegebenen Schwellwert Parallelverarbeitung, d.h. alle verfügbaren Prozessorkerne. Im Fall der Nutzung eines Index und ohne Verwendung von Partitionierung wird aber serielle Verarbeitung verwendet. Außerdem erfolgt die Sortierung zu Beginn zwar noch im Hauptspeicher, ab einem bestimmten zu sortierenden Datenumfang muss aber die Tempdb hinzugenommen werden, was zu einem zusätzlichen IO-Aufwand führt.
Zum Vergleich der verwendeten Abfragen werden neben den Optimizer-Kosten die Ausführungszeit (Realzeit) und die IO verwendet. Zur Ermittlung von Realzeit und IO wird in der Session, in der die Abfragen durchgeführt werden, initial die folgenden beiden Statements verwendet:
set statistics time on
set statistics io on

Testdurchführung

Beginnend mit den 100.000 Datensätzen wird eine sortierte Liste der Datensätze gebildet. Die Sortierung erfolgt über die Spalte CarrierTrackingNumber. Hierbei wird die sortierte Liste jeweils ohne und mit einem Index auf dieser Spalte erstellt. Die jeweiligen Laufzeiten werden erfasst (s.o. Kapitel Weitere vorbereitende Schritte und s.u. Kapitel Statistiken) und anschließend wird der Datenumfang verdoppelt, indem die Daten der Tabelle dbo.SalesOrderDetail zu derselben Tabelle hinzugefügt werden. Dieser Vorgang wird mehrfach durchlaufen. Jede Abfrage wird zweimal mit und zweimal ohne unterstützenden Index ausgeführt. Vor der jeweils ersten Abfrage wird der Cache bereinigt und es werden neue Statistiken gebildet. Die Statistiken werden in diesem Fall nicht auf Grund von Stichproben (Default) sondern unter Berücksichtigung aller Datensätze aktualisiert (fullscan-Option) und die Messwerte werden jeweils vom zweiten Aufruf verwendet, das heißt es werden, so weit verfügbar, Daten aus dem Cache verwendet.

Zur Erfassung der benötigten CPU-Zeit wurde zu Beginn der Session das Statement set statistics time on verwendet.

In jeder der Testreihen wurde select-Statement mit der Sortierung zweifach aufgerufen, wobei der gemessene Wert jeweils von dem zweiten Aufruf verwendet wurde, so dass der Cache jeweils schon einige der benötigten Daten enthielt und damit kein erneuter Storage-Zugriff erforderlich war.

Der Index wird mit dem folgenden Statement erstellt und vor jeder ersten, dritten, fünften, … Sortierung angelegt bzw. aktiviert und bei jeder zweiten, vierten, … Sortierung deaktiviert. Das Inkludieren von Spalten bewirkt hier zwar ein Aufblähen des Index, erspart aber im Fall seiner Nutzung einen Zugriff auf die zu Grunde liegende Tabelle.

CREATE NONCLUSTERED INDEX [idx_CarrierTrackingNumber] ON [dbo].[SalesOrderDetail]
(
	[CarrierTrackingNumber] ASC
)
INCLUDE( [OrderQty]
        ,[ProductID]
        ,[SpecialOfferID]
        ,[UnitPrice]
        ,[UnitPriceDiscount]
        ,[rowguid]
        ,[ModifiedDate]
        ) WITH ( PAD_INDEX = OFF
               , STATISTICS_NORECOMPUTE = OFF
               , SORT_IN_TEMPDB = OFF
               , DROP_EXISTING = OFF
               , ONLINE = OFF
               , ALLOW_ROW_LOCKS = ON
               , ALLOW_PAGE_LOCKS = ON
               , OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
ON [PRIMARY]

Statistiken

In diesem Abschnitt geht es ausnahmsweise nicht um die Statistiken Ihres SQL Servers, die unabdingbar für den SQL Server Optimizer und damit für seine Performance sind, sondern um das Wachstum von Optimizer-Kosten und CPU-Ausführungszeiten in Abhängigkeit von der Größe Ihrer Datenmengen.

Hier die Ergebnisse:

Anzahl Zeilen	Optimizer Kosten (mit Index)	CPU-Zeit (ms) mit Index	Optimizer Kosten (ohne Index)	CPU-Zeit (ms) ohne Index
100.000	0,85032	0	8,514	109
200.000	1,69736	0	17,9307	234
400.000	3,39217	0	37,6827	485
800.000	6,78106	0	79,0235	1016
1.600.000	13,5588	0	165,476	1984
3.200.000	27,1144	0	345,425	3750
6.400.000	54,2262	109	1334,46	7703
12.800.000	108,45	329	2727,67	17828

Vergleich der Optimizer-Kosten für Sortierungen mit und ohne unterstützenden Index

Vergleich der CPU-Zeiten in Millisekunden für Sortierungen mit und ohne unterstützenden Index

Wie man der vorstehenden Tabelle und den entsprechenden Graphiken entnehmen kann, steigt der Aufwand zur Erstellung einer sortierten Liste im Fall eines fehlenden geeigneten Index erheblich schneller als wenn ein Index verfügbar ist, der die Sortierung reflektiert.

Ausführungspläne im Detail

In diesem Kapitel werden die Ausführungspläne für die letzte Testreihe, also die 12.800.000 Datensätze ohne und mit dem optimierenden Index dargestellt.

Ausführungsplan ohne Index

Wird die Abfrage ohne Index ausgeführt, so sehen wir folgende Informationen (Werte gerundet):

Operation	I/O Kosten	CPU Kosten	Subtree Kosten
Clustered Index Scan	126	14	140
Sort	2184	432	2756

Ausführungsplan mit Index

Im Fall der Abfrage mit Index erhalten wir die folgenden gerundeten Werte. Wie man sieht, ist der aufwändige Sortierschritt entfallen. Die Realzeit ist von 6:33 Minuten auf 2:50 Minuten gesunken. Bei der Betrachtung der Realzeit muss jedoch berücksichtigt werden, dass ein Großteil der benötigten Zeit für die Übertragung der umfangreichen Daten an den Client, also in diesem Fall das SQL Server Management Studio, benötigt wurde.

Operation	I/O Kosten	CPU Kosten	Subtree Kosten
Index Scan	94	14	108

Ausführungsplan mit Index. Die aufwändige Sortierung ist entfallen

Fazit

Geeignete Indizierung ist nicht nur zur Unterstützung von where-Kriterien, sondern auch für Sortieroperationen von großem Vorteil. Die hier dargestellten Werte wurden von einem einzigen Benutzer ermittelt, der die Datenbank (und Instanz) exklusiv verwendet hat. Im Fall vieler Benutzer die gleichzeitig sortierte Daten benötigen, ergibt sich ein zusätzlicher Bottleneck durch die umfangreiche Nutzung der tempdb in der die Sortieroperationen stattfinden. Dies umfasst auch das relativ aufwändige Lesen und Schreiben in die Dateien der tempdb. In größeren Datenbanken ist die Identifizierung der größten Performance-Painpoints eine sehr zeitaufwändige Arbeit die Monate in Anspruch nehmen kann. Hier möchten wir nochmals auf unser SQL Server Assessment hinweisen, das die benötigten Informationen innerhalb weniger Minuten erfasst und dessen Ergebnisse Ihnen nach wenigen Auswertungstagen in einer Präsentation vorgestellt werden.

Wenn Sie mehr zu diesem Thema erfahren möchten, stehen Ihnen unsere Experten gerne zur Verfügung. Vereinbaren Sie unverbindlich ein Beratungsgespräch über unser Kontaktformular. Wir unterstützen Sie gerne!