Dienstag, 13. November 2012

Optimierung von Datenbankmodellen–Richtige Wahl von Datentypen und Indexen (Teil 3)

In Teil 1 habe ich den Einfluss der Datentypen auf die Performance von Abfragen beschrieben und in Teil 2 wurde beschrieben, wie sich die Auswahl des geeigneten “Clustered Index” auf die Performance und das Datenvolumen auswirkt. In diesem Teil möchte ich die Besonderheiten von Indexen im Zusammenhang mit “Parameter Sniffing” etwas genauer beleuchten und durch Beispiele die Fallen aufzeigen.

Ausgangssituation

Als Basis für die nachfolgenden Tests und deren Untersuchung dient die bereits in Teil 1 und Teil 2 verwendete Datenstruktur der Tabelle [dbo].[tbl_Companies] wie nachfolgend abgebildet.

CREATE TABLE dbo.tbl_Companies
(
    Id         int           IDENTITY(1,1) NOT NULL,
    Name       nvarchar(128) NULL,
    TaxNo      varchar(24)   NULL,
    CostCenter char(7)       NULL,
    UpdateBy   varchar(20)   NUL
L

);
GO

CREATE UNIQUE CLUSTERED INDEX ci_tbl_Companies ON dbo.tbl_Companies (Id);

Die Relation dbo.tbl_Companies besitzt aktuell einen “Clustered Index” auf dem Attribut [Id]. Insgesamt befinden sich in der Datenbank ~2.450 Datensätze.

Anforderung

Sehr häufig werden Abfragen auf die Relations ausgeführt, die das Attribut [CostCenter] einschränken. Hierbei handelt es sich meistens um simple Abfragen der folgenden Art:

SELECT * FROM dbo.tbl_Companies WHERE CostCenter = 'C001000'
SELECT * FROM dbo.tbl_Companies WHERE CostCenter = 'C049000'

Die Aufgabe besteht darin, die Suche nach Kostenstellen zu optimieren.

Bestandsaufnahme

Schaut man auf die Verteilung der Kostenstellen (Kardinalität) so fällt auf, dass eine große Streuung gibt.

SELECT  CostCenter,
        COUNT(1)        AS    Anzahl
FROM    dbo.tbl_Companies
GROUP BY
        CostCenter
HAVING COUNT (1) > 500 OR COUNT(1) = 1
ORDER BY
        COUNT(1) DESC

CostCenter - Kardinalitaet

Während die Kostenstelle “C001000” weit über 500 Mal verwendet wird, kommen kleinere Kostenstellen, wie z. B. “C049000” nur ein einziges Mal in der Relation vor. Zunächst werden die Ausführungspläne sowie das IO der beiden obigen Abfragen an, um zu sehen, wie der SQL Server aktuell die Abfragen bearbeitet. Um immer mit gleichen Voraussetzungen zu arbeiten, wird vor jeder neuen Ausführung der Plancache geleert! Alle weiteren Tests werden mit den beiden obigen Abfragen durchgeführt.

DECLARE @dbid int = DB_ID()
DBCC FLUSHPROCINDB(@dbid)

SET STATISTICS IO ON;
GO

SELECT * FROM dbo.tbl_Companies WHERE CostCenter = 'C001000'
GO
SELECT * FROM dbo.tbl_Companies WHERE CostCenter = 'C049000'
GO

SET STATISTICS IO OFF;

Die erste Abfrage wird 586 Datensätze liefern während die zweite Abfrage nur einen Datensatz listet. Schaut man sich jedoch den IO sowie die Abfragepläne an, stellt man fest, dass unabhängig von der Ergebnismenge beide Abfragen identische Ausführungsmerkmale besitzen.

Abfrage 1: tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 35, physische ....
Abfrage 2: tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 35, physische ....

Basierend auf dem Ergebnis der IO-Statistiken mag das Resultat des Abfrageplans nicht weiter überraschen.

Abfrageplan - 1

Obwohl doch beide Abfragen eine unterschiedliche Datenmenge liefern, unterscheiden sich weder die IO noch die Abfragepläne voneinander. Das Ergebnis ist logisch, da das Attribut [CostCenter] nicht indiziert ist. Das Vorgehen des SQL Server kann man mit dem Suchen nach Begriffen in einem Buch vergleichen, das keinen Seitenindex besitzt. In diesem Fall müsste ja auch das Buch für jeden Begriff immer vollständig gelesen werden.

Weiterhin gilt in Bezug auf die gleichen Abfragepläne zu berücksichtigen, dass Abfragepläne gespeichert werden und wiederverwendet werden können. Das Thema habe ich sehr ausführlich in dem Artikel “Auswirkung von vollständig qualifizierten Objekten auf den Plancache von SQL Server” beschrieben. Ein Blick auf den Inhalt des Plancaches zeigt das deutlich

SELECT  st.text,
        cp.refcounts,
        cp.usecounts
FROM    sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
WHERE dbid = db_id() AND
      (
       st.text NOT LIKE '%sys.dm_exec_cached_plans%' AND
       st.text NOT LIKE '%STATISTICS%'
      );

Planchache - 1

Man sieht an dem Ergebnis, dass SQL Server die Abfragen parametrisieren konnte und dieser Abfrageplan zwei Mal verwendet werden konnte. Die Parametrisierung konnte jeweils auf beide Abfragen einmal angewendet werden (Zeile 1 und Zeile 2).

Lösungsansatz: Implementieren eines Indexes

Nun wird das Attribut [CostCenter] mit einem Index versehen, um zu sehen, ob die Abfrage dadurch optimiert werden kann.

CREATE INDEX ix_tbl_Companies_CostCenter ON dbo.tbl_Companies (CostCenter);

Nachdem der Index erstellt wurde, werden die identischen Abfragen erneut ausgeführt und das Ergebnis der Optimierung begutachtet. Dabei ist zunächst interessant, ob sich der IO geändert hat. Grundsätzlich sollte nun die Erwartungshaltung hoch sein, schließlich ist das Ergebnis der zweiten Abfrage ja nur ein Datensatz!

DECLARE @dbid int = DB_ID()
DBCC FLUSHPROCINDB(@dbid)

SET STATISTICS IO ON;
GO

SELECT * FROM dbo.tbl_Companies WHERE CostCenter = 'A060000'
GO

SELECT * FROM dbo.tbl_Companies WHERE CostCenter = 'C049000'
GO

SET STATISTICS IO OFF;

Es ist wichtig, dass der Plancache vor der Ausführung wieder gelöscht wird. Deswegen unbedingt darauf achten, dass bei der Ausführung auch die oberen beiden Befehle mit ausgeführt werden! Das Ergebnis für die IO-Messung lautet wie folgt:

Abfrage 1: tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 35, physische ....
Abfrage 2: tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 4, physische ....

Das Ergebnis beeindruckt sehr. Der IO für die zweite Abfrage wurde um 89% reduziert! Basierend auf dem reduzierten IO für “kleine” Datenmengen sollten sich die Verbesserungen auch im Abfrageplan widerspiegeln.

Abfrageplan - 2

Tatsächlich sieht man, dass sich die Kosten für den gesamten Batch deutlich “zum Nachteil” der ersten Abfrage verschoben haben. Aber auch ein anderes – wichtiges – Detail sollte genau beachtet werden. Der Abfrageplan für beide Abfragen unterscheidet sich deutlich. Während für die erste Abfrage ein Scan des “Clustered Index” als ideale Strategie von SQL Server bewertet wurde, hat SQL Server sich bei der zweiten Abfrage für eine andere Strategie entschieden (Key Lookup). Tatsächlich ist dieser Vorgang deutlich schneller, wenn nur wenige Datensätze zurückgeliefert werden.

Lösungsansatz: Verwenden von Parametern

Wie in der ersten Abbildung mit dem Inhalt des Abfrageplans gezeigt, wird bei der automatischen Parametrisierung von SQL Server nicht immer der ideale Parametertyp und die ideale Parametergrösse verwendet (hier ein varchar(8000)). Dem kann man entgegen wirken, indem man die Abfragen, wie sie von uns zu Testzwecken verwendet wurden, wie folgt ändert und ausführt:

DECLARE @dbid int = DB_ID()
DBCC FLUSHPROCINDB(@dbid)

SET STATISTICS IO ON;
GO

DECLARE @CostCenter char(7)

SET @CostCenter = 'A060000'
SELECT * FROM dbo.tbl_Companies WHERE CostCenter = @CostCenter

SET @CostCenter = 'C049000'
SELECT * FROM dbo.tbl_Companies WHERE CostCenter = @CostCenter
GO

SET STATISTICS IO OFF;

Wird die obige Abfrage ausgeführt, werden wir eine böse Überraschung feststellen (bitte unbedingt vorher wieder den Plancache leeren!). Schauen wir uns zunächst einmal wieder die IO an und “staunen”:

Abfrage 1: tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 35, physische ....
Abfrage 2: tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 35, physische ....

Unglaublich – statt einer weiteren Verbesserung zeigt das Ergebnis eine Verschlechterung für die zweite Abfrage (die ja im vorherigen Beispiel deutlich performanter gewesen ist). Ein Blick auf den Abfrageplan bestätigt das schlechte Ergebnis.

Abfrageplan - 1

Ich möchte darauf hinweisen, dass diese Abbildung keine Kopie der weiter oben gezeigten Abbildung ist sondern das Ergebnis der Abfrage repräsentiert. Es fällt auf, dass SQL Server für die zweite Abfrage genau den gleichen Abfrageplan verwendet hat wie für die erste Abfrage! Das Geheimnis dieses – seltsamen – Ergebnisses nennt sich “Parameter Sniffing” und erlaubt es SQL Server, einen Abfrageplan zu erstellen, der ideal auf den Parametertypen und den Parameterwert passt. Dieser Plan wird im Plancache abgelegt und wiederverwendet. Wo zunächst einmal das Wiederverwenden von Abfrageplänen ein Segen ist, kann es auf der anderen Seite aber auch zum Fluch werden.

Betrachten wir einfach unsere beiden Abfragen in der Chronologie. Zunächst wurde die Abfrage auf die Kostenstelle “AD06000” ausgeführt. Wie schon vorher gesehen, ist der optimale Plan für die Menge der Daten ein “Clustered Index Scan”. Dieser Plan wird nun von SQL Server für die Wiederverwendung gecached. Anschließend wurde die zweite Abfrage ausgeführt. Das Ergebnis für die ausgewählte Kostenstelle ist nur ein Datensatz, dennoch wird der zuvor gespeicherte Abfrageplan verwendet, in dem SQL Server einen “Clustered Index Scan” als optimal erachtet hat – ein Teufelskreis.

Um SQL Server zu veranlassen, für jede Ausführung der Abfrage einen neuen Abfrageplan zu erstellen und zu nutzen, gibt es mehrere Möglichkeiten. Eine dieser Lösungen kann die Verwendung der Option “OPTION (RECOMPILE)” sein. Diese Option zwingt SQL Server, einen neuen Abfrageplan zu erstellen und zu verwenden. Auf unser Beispiel angewendet würde die Abfrage wie folgt geändert:

SET STATISTICS IO ON;
GO

DECLARE    @CostCenter char(7)

SET        @CostCenter = 'A060000'
SELECT * FROM dbo.tbl_Companies WHERE CostCenter = @CostCenter OPTION (RECOMPILE);

SET        @CostCenter = 'C049000'
SELECT * FROM dbo.tbl_Companies WHERE CostCenter = @CostCenter OPTION (RECOMPILE);
GO

SET STATISTICS IO OFF;

Schaut man sich dann das Ergebnis von IO und den Abfrageplan an, wird man feststellen, dass wieder die in Beispiel zwei verwendeten idealen Abfragepläne verwendet werden.

Fazit

Es ist nicht immer damit getan, Indexe zu verwenden und darauf zu vertrauen, dass die Performance sich verbessert. Vielmehr muss bei der Auswahl von Indexen auf einige Punkte geachtet werden, die nicht sofort erkenntlich sind. Beispielsweise muss – wie das obige Beispiel deutlich gemacht hat – bei der Programmierung von Abfrage auf die Kardinalität der Daten geachtet werden. Nur so ist es für SQL Server möglich, einen Index ideal zu nutzen.

Herzlichen Dank für’s Lesen
Das Script für die Datenbank und ihr Testdaten können Sie hier herunterladen:
http://www.db-berater.de/files/database-Indexing.zip

 

Parameter Sniffing http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx
CREATE INDEX http://msdn.microsoft.com/de-de/library/ms188783.aspx
DBCC FLUSHPROCINDB() http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx
OPTION RECOMPILE http://msdn.microsoft.com/de-de/library/ms181714.aspx
   

Keine Kommentare :

Kommentar veröffentlichen