Mittwoch, 21. November 2012

Optimierung von Datenbankmodellen – die Verwendung von Planguides

Der Einsatz bei einem Kunden, dessen Datenbanksystem in einem Teilbereich sehr schlecht lief bildet die Grundlage dieses Artikels. Folgender Sachverhalt liegt dem folgenden Artikel zugrunde. Der Kunde hat eine “Out-Of-The-Box”-Applikation, die unter anderem ein Adressbuch beinhaltet. Dieses Adressbuch wird von den Mitarbeitern hoch frequentiert und von Zeit zu Zeit kommt es vor, dass die Performance massiv einbricht. Abfragen, die vorher in wenigen Sekunden ihre Daten an den Client lieferten, dauerten plötzlich bis zu 3 Minuten. Was ist da passiert?

Für die Analyse des Problems sowie die anschließende Lösung ist von Bedeutung, dass wir KEINE Relationen ändern durften. Weiterhin durften wir, um den Support für die Anwendung nicht zu verlieren, keine Indexe anlegen, ändern oder Prozeduren verändern. Zu Demonstrationszwecken nehme ich das Datenmodell sowie die Datenmenge aus dem Artikel [Optimierung von Datenbankmodellen–Richtige Wahl von Datentypen und Indexen (Teil 3)]. Die Struktur sowie die Datenverteilung der Relation [dbo].[tbl_Companies] stellt sich wie folgt dar:

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

ALTER TABLE dbo.tbl_Companies ADD CONSTRAINT pk_tbl_Companies_Id PRIMARY KEY CLUSTERED (Id);
CREATE INDEX ix_tbl_Companies_CostCenter ON dbo.tbl_Companies (CostCenter);

Die Relation [dbo].[tbl_Companies] besitzt zwei Indexe; den Clustered Index [pk_tbl_Companies_Id] sowie einen Index [ix_tbl_Companies_CostCenter], der ausschließlich das Attribut [CostCenter] berücksichtigt. Auf die Daten wird mittels einer Stored Procedure [dbo].[proc_app_Get_CompanyData] zugegriffen. Die Stored Procedure hat den folgenden Aufbau:

CREATE PROC dbo.proc_SearchCompany
    @CostCenter char(7)
AS
    SET NOCOUNT ON;
    SELECT
* FROM dbo.tbl_Companies WHERE CostCenter = @CostCenter;
    SET NOCOUNT OFF
GO

Die Prozedur erwartet den Parameter @CostCenter und zeigt anschließend alle Informationen aus der Relation [dbo].[tbl_Companies] an. Um die Problematik zu verstehen, die sich aus dieser Konstruktion ergibt, wird sowohl IO als auch der Abfrageplan für die Abfrage für zwei unterschiedliche Parameterwerte analysiert.

Datenbereich mit niedriger Selektivität

Zunächst wird die Abfrage mit der Kostenstelle “C001000” analysiert. Diese Kostenstelle ist in der Relation [dbo].[tbl_Companies] insgesamt 586 mal vertreten.

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;

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

SET STATISTICS IO OFF;

Zunächst wird der Bufferpool sowie der Plancache geleert. Anschließend werden die IO-Statistiken aktiviert. Anschließend wird die Abfrage ausgeführtund die IO-Statistiken wieder deaktiviert. Der Abfrageplan zum obigen Code stellt sich wie folgt dar:

Abfrageplan - niedrige Selektivität

(586 row(s) affected)
tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 35, physische Lesevorgänge 1

Microsoft SQL Server hat auf Grund der niedrigen Selektivität eine Abfragestrategie gewählt, die einen Clustered Index Scan favorisiert. Diese Entscheidungen werden von SQL Server auf Grund von Statistiken über die zu erwartende Datenmenge sowie die dafür notwendigen IO-Operationen gewählt.

Datenbereich mit hoher Selektivität

Nun wird die obige Abfrage erneut für die Kostenstelle ‘C049000’ analysiert. Diese Kostenstellt besitzt eine sehr hohe Selektivität, da sie in der Relation [dbo].[tbl_Companies] nur ein mal vorkommt.

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;

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

SET STATISTICS IO OFF

Auch hier wird zunächst wieder der Bufferpool sowie der Plancache geleert, um die gleichen Voraussetzungen zu schaffen. Das IO-Ergebnis sowie der Abfrageplan stellen sich wie folgt dar:

Abfrageplan - hohe Selektivität

(1 row(s) affected)
tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 4, physische Lesevorgänge 3

Die Abfragestrategie des Abfrageoptimierer sieht nun ganz anders aus als noch in der ersten Abfrage. Da hier nur ein Datensatz zurückgeliefert wird, macht ein Indexscan auf dem Clustered Index keinen Sinn. Vielmehr konnte der Abfrageoptimierer nun den Index verwenden, der auf dem Attribut [CostCenter] liegt. Da jedoch die eigentlichen Daten im Clustered Index liegen, muss zusätzlich – basierend auf dem Attribut [Id] – ein Bookmark Lookup im Clustered Index gemacht werden.

Verwendung der Stored Procedure

Soweit die Theorie – nun wird die Abfrage erneut ausgeführt. Dieses mal jedoch über die Stored Procedure [dbó].[proc_SearchCompany]. Für die Analyse des Abfrageplans wird simuliert, dass zwei Benutzer die Abfrage sequentiell verwenden. Benutzer A führt die Prozedur für die Kostenstelle “C001000” aus und Benutzer B für die Kostenstelle “C049000”.

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;

-- Benutzer A
EXEC    dbo.proc_SearchCompany @CostCenter = 'C001000';

-- Benutzer B
EXEC    dbo.proc_SearchCompany @CostCenter = 'C049000';

SET STATISTICS IO OFF;

Interessant ist das Ergebnis des Abfrageplans sowie des IO wie die nachfolgende Abbildung zeigt.

Abfrageplan - Stored Procedure

tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 35, physische Lesevorgänge 1 ….

tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 35, physische Lesevorgänge 0 …

Das Ergebnis ist verblüffend aber logisch. Hierbei handelt es sich um “Parameter Sniffing” und ist ein erwartetes Ergebnis. Um es einfach auszudrücken, bedeutet “Parameter Sniffing” nichts anderes, als das Microsoft SQL Server beim Ausführen einer Stored Procedure den Übergabeparameter verwendet, um die Kardinalität des Wertes zu bestimmen (weitere Informationen dazu in der Linkliste am Ende des Artikels). Die Information über die Kardinalität eines Parameters fließt in die Bestimmung der Abfragestrategie mit ein. Die Abfragestrategie wird als Plan im Plancache abgelegt.

Betrachtet man nun den Aufruf der Prozeduren in ihrer historischen Reihenfolge, wird schnell klar, was hier passiert; bei der ersten Verwendung der Prozedur wurde ein Wert für den Parameter verwendet, der eine geringe Selektivität hat. Aus diesem Grund ist die beste Abfragestrategie ein “Clustered Index Scan”. Diese Informationen werden im Plancache abgelegt.

Anschließend führt Benutzer B die Prozedur erneut aus. Da im Plancache für die Ausführung der Prozedur ein Abfrageplan zur Verfügung gestellt wird, wird dieser Plan für die Ausführung erneut verwendet. Dieses Mal jedoch ist der Wert des Parameters ein hoch selektiver Wert (es gibt nur einen Datensatz). Dennoch wird wieder ein “Clustered Index Scan” verwendet – nur dieses Mal wäre es nicht der optimale Plan (siehe Analyse weiter oben).

Lösung

Welche Möglichkeiten bestehen nun, um dieses Dilemma zu beseitigen. Das Ziel muss sein, dass Microsoft SQL Server jedes Mal einen neuen – individuellen Abfrageplan verwendet. Die Lösung besteht darin, mit dem Abfragehinweis OPTION (RECOMPILE) jedes Mal einen neuen Abfrageplan zu erzwingen. Jedoch konnte das in diesem Fall nicht gemacht werden. Bei der Anwendung handelt es sich um ein Standardprodukt und Änderungen an Datenbankobjekten konnten/durften nicht angewendet werden.

Für solche Fälle stellt Microsoft SQL Server seit der Version 2005 die Möglichkeit zur Verfügung, sogenannte Planhinweise in einer Datenbank zu speichern. Mit Planhinweislisten kann die Leistung von Abfragen optimieren werden, wenn Abfragen nicht direkt geändert werden können/sollen. Die Verwendung von Planhinweislisten bietet sich z. B. an, wenn eine kleine Teilmenge von Abfragen in der Datenbankanwendung eines Drittanbieters nicht erwartungsgemäß funktioniert. Planhinweislisten beeinflussen die Abfrageoptimierung, indem Abfragehinweise oder ein fester Abfrageplan an die Abfragen angefügt werden. In der Planhinweisliste geben Sie die Transact-SQL-Anweisung an, die optimiert werden soll, sowie entweder eine OPTION-Klausel mit den zu verwendenden Abfragehinweisen oder einen spezifischen Abfrageplan, der für die Optimierung der Abfrage verwendet werden soll.

Um einen Abfrageplan zu erstellen, wird die Prozedur sp_create_plan_guide verwendet. Für eine Optimierung der oben aufgeführten Abfrage wird OPTION (RECOMPILE) verwendet. Damit wird bei jeder Ausführung ein neuer Abfrageplan generiert. Um den Abfrageplan anzulegen wird das folgende Script in der Datenbank ausgeführt:

EXEC    sp_create_plan_guide
        @name = N'PG_SearchCompany_By_CostCenter',
        @stmt = N'SELECT * FROM dbo.tbl_Companies WHERE CostCenter = @CostCenter;',
        @type = N'OBJECT',
        @module_or_batch = N'dbo.proc_SearchCompany',
        @params = NULL,
        @hints =  N'OPTION (RECOMPILE)'

Mit dem obigen Statement wird in der Datenbank ein Abfrageplan für das Objekt [dbo].[proc_SearchCompany] abgelegt. Als Abfrageoption für den in der Prozedur vorkommenden Text “SELECT …” OPTION (RECOMPILE) verwendet. Sobald ein solcher Plan in der Datenbank eingerichtet wurde, ist er sofort verfügbar. Also wird die Prozedur erneut für Benutzer A und Benutzer B ausgeführt. Die Abfragepläne sehen anschließend wie folgt aus:

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;

-- Benutzer A
EXEC dbo.proc_SearchCompany @CostCenter = 'C001000';

-- Benutzer B
EXEC dbo.proc_SearchCompany @CostCenter = 'C049000';

SET STATISTICS IO OFF;

Abfrageplan - Mit Planguide

Perfekt – genau dieses Ergebnis sollte erreicht werden. Um festzustellen, ob ein Planguide verwendet wurde, öffnet man für den Ausführungsplan lediglich die Eigenschaften (rechte Maustaste auf “SELECT Cost:x%) und schaut in den Eigenschaften, ob für PlanguideName ein Eintrag vorhanden ist.

Abfrageplan - Eigenschaft PlanGuideName

Fazit

Häufig kommt es vor, dass Datenbankstrukturen von Drittanbietern – aus welchen Gründen auch immer – nicht geändert werden dürfen. Sofern eine schlechte Performance von Abfragen optimiert werden soll, bieten die Planguides eine sehr gute Möglichkeit, ohne Änderungen an Datenbankobjekten ideale Optimierungen zu erzielen.

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
sp_create_plan_guide http://msdn.microsoft.com/de-de/library/ms179880.aspx
Planhinweise http://msdn.microsoft.com/de-de/library/ms190417(v=sql.105).aspx

Keine Kommentare :

Kommentar veröffentlichen