Sonntag, 20. Februar 2011

Parametrisierte Abfragen - die Kehrseite

In meinem letzten Blogbeitrag habe ich vehement für die Verwendung parametrisierter Abfragen geworben. Gerade dann, wenn viele Anwender immer wieder sehr änliche SQL Statements ausführen (solche, die sich nur in der WHERE Klausel unterscheiden), profitiert eine SQL Server Instanz sehr davon, dass für diese Abfragen nicht immer wieder ein neuer Ausführungsplan generiert werden muss. Manchmal bereiten parametrisierte Abfragen aber auch Probleme.

Wo ist das Problem?
Nun, beim ersten Kompilieren des Statements (= beim ersten Erstellen eines Ausführungsplans) erstellt der SQL Server den optimalen Ausführungsplan für die bei der ersten Ausführung übergebenen Parameter. In vielen Fällen ist das völlig ok, weil dieser Ausführungsplan auch dann gut funktioniert, wenn andere Parameter übergeben werden.
Aber was, wenn beim ersten Aufruf Parameter übergeben werden, die völlig untypisch sind für die nachfolgenden Aufrufe? Sie ahnen es: SQL Server speichert diesen "exotischen" Ausführungsplan und verwendet ihn bei allen weiteren Aufrufen.
Für das nachfolgende Beispiel habe ich einen zusätzlichen nichtgruppierten Index auf die Tabelle Person.Person in der Datenbank AdventureWorks2008R2 angelegt:

create index vhe_Index_Demo on Person.Person (LastName, FirstName)

Danach habe ich dann die folgende Anweisung ausgeführt:

exec sp_executesql
  @stmt = N'select FirstName, LastName from Person.Person where LastName like @p'
 ,@params = N'@p nvarchar(40)'
 ,@p = '%'

Wenn sp_executesql zum ersten Mal ausgeführt wird, dann sorgt der Parameterwert @p='%' dafür, dass ein Ausführungsplan erzeugt wird, der alle Datensätze liefert. Der beste Operator dafür ist ein Index Scan auf vhe_Index_Demo.



Wenn bei den nachfolgenden Ausführungen spezifischere Werte angegeben werden, zum Beispiel @p='Abel', dann ist ein Table Scan bestimmt nicht der beste Operator. Ein Index Seek wäre erheblich effizienter, aber da der Plan nun bereits einmal kompiliert wurde, verwendet SQL Server stur immer wieder den schlechteren Plan.
Je komplexer die Abfrage ist, desto dramatischer fällt der Unterschied aus. So habe ich in einem Fall gesehen, dass die Laufzeitunterschiede zwischen optimaler Ausführung und Ausführung mit wiederverwendetem Ausführungsplan einen Unterschied von 2 Sekunden zu 5 Minuten(!) ausmachte.

Und nun?
In einem solchen Fall müssen wir zunächst einmal herausfinden, wie ein guter Ausführungsplan aussieht. Ersetzen Sie dafür die Parameter durch typische Werte und lassen Sie sich im SQL Server Management Studio den Ausführungsplan anzeigen.

select FirstName, LastName from Person.Person where LastName like 'Abel'

Wie erwartet, generiert SQL Server hierfür einen Ausführungsplan mit einem Seek auf den Index vhe_Index_Demo:



Plan Guides
Mit der Prozedur sp_create_plan_guide lönnen wir dem Optimierer "unter die Arme greifen". Die Prozedur enthält unter anderem das SQL Statement und die Hinweise (@hints), die wir dem Optimierer mit auf den Weg geben möchten.

exec sp_create_plan_guide
  @name = N'PlanGuide_PersSuche'
 ,@stmt = N'select FirstName, LastName from Person.Person where LastName like @p'
 ,@type = N'SQL'
 ,@module_or_batch = null
 ,@params = N'@p nvarchar(40)'
 ,@hints = N'OPTION (table hint(Person.Person, FORCESEEK, INDEX(vhe_Index_Demo)))'

Der Plan Guide wird dafür sorgen, dass immer (unabhängig von den übergebenen Parametern) der erzeugte Ausführungsplan einen SEEK auf den Index vhe_Index_Demo enthält. Das ist in unserem Fall ein guter Tipp für den Optimizer.
Beim Erstellen eines Plan Guides ist es ganz wichtig, dass das SQL Statement exakt so geschrieben wird, wie das Statement, das mit sp_execute ausgeführt wird. Es kommt dabei auf Groß- und Kleinschreibung genauso an, wie auf die Leerzeichen und Zeilenumbrüche.

Hier noch ein Praxistipp:
Wenn Sie einen Plan Guide für ein SQL Statement erzeugen, dann wird der Ausführungsplan für dieses Statement sofort aus dem Query Cache entfernt. Bei der nächsten Ausführung der Abfrage wird also unter Verwendung des Plan Guides ein neuer Ausführungsplan erstellt.

Fast hätte ich es vergessen, Ihnen zu zeigen, wie Sie einen einmal erstellten Plan Guide wieder los werden:

execute sp_control_plan_guide
  @operation = N'DROP'
 ,@name = N'PlanGuide_PersSuche'