Donnerstag, 24. Februar 2011

AdventureWorks Datenbanken

Die AdventureWorks Beispiel-Datenbanken werden seit geraumer Zeit auf http://www.codeplex.com/, der Open Source Projektseite von Microsoft, angeboten. Da die Struktur der Seite etwas unübersichtlich ist, wenn man "nur mal eben" die passende Version braucht, habe ich hier die erforderlichne Links zusammengestellt.

Die Einstiegsseite liefert einen Überblick über alle Datenbanken und Codebeispiele für SQL Server.

Wenn Sie eine der Beispieldatenbanken brauchen, erreichen Sie den Download über die folgenden Links:
Außerdem gibt es zu den Versionen 2005 bis 2008 auch ER-Diagramme, die beim Verstehen der Datenbankstruktur sehr hilfreich sind. Diese finden Sie hier als Visio-Diagramme.

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'

Dienstag, 1. Februar 2011

Hallo Programmierer - verwendet doch bitte parametrisierte Abfragen!

In den vergangenen Wochen habe ich gleich zwei größere SQL Server Installationen erlebt, die beide unter demselben Problem litten: Die Anwendungsentwickler generieren ihre SQL Statements für jede Abfrage neu und zwingen damit bei großen Benutzerzahlen den Datenbankserver in die Knie. Was genau steckt dahinter?
Betrachten wir einmal das folgende SQL Skript:

dbcc freeproccache         -- Cache und Buffer leeren
dbcc dropcleanbuffers
declare @i int             -- Schleifenvariable
       ,@m int             -- Obergrenze der Schleifenvariablen
       ,@cmd nvarchar(500) -- Generiertes SQL Statement
set @i = 1
select @m = MAX(BusinessEntityID) from Person.Person
while (@i < @m)
begin
   -- SQL Statement wird bei jedem Durchlauf neu generiert
   set @cmd = 'declare @Lname nvarchar(50); ' +
              ' select @Lname = LastName ' +
              'from Person.Person where ' +
              'BusinessEntityID = ' + cast(@i as nvarchar(10))
   exec (@cmd)
   set @i = @i + 1
end
go

Mein Laptop benötigt zur Ausführung dieser Schleife etwa 18 Sekunden. In jedem Schleifendurchlauf wird das SQL Statement neu generiert und die WHERE-Klausel sieht ein klein wenig anders aus. Für den SQL Server bedeutet das: Er muss für jedes Statement einen Ausführungsplan erzeugen. Und so kommt es, dass im Plan Cache so viele Varianten dieses Statements stehen, wie es Schleifendurchläufe gibt, nämlich 20.779. Über die folgende Abfrage lässt sich das ermitteln:

select entries_count
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP'

Das wirklich Dumme an der Sache ist also nicht nur, dass für das Erzeugen des (immer gleichen) Ausführungsplans eine Menge CPU-Leistung verbraten wird. Außerdem läuft auch noch der Plancache voll, in dem SQL Server einmal erzeugte Ausführungspläne aufbewahrt in der Hoffnung, dass sie bald wieder verwendet werden können. Das können Sie sehr gut in dem Bericht "Memory Consumption" sehen, den Sie erhalten, wenn Sie im Management Studio mit der rechten Maustaste auf die Instanz (Oberster Knoten im Object Explorer) klicken und dann "Reports - Standard Reports - Memory Consumption" auswählen.

Cache mit immer wieder neu generierter Abfrage
Das Bild zeigt den Zustand des Plancache nach Ausführung des obigen Skripts: Er ist angefüllt mit über 20.000 nutzlosen Ausführungsplänen ("Stolen Pages"). Halten Sie sich bitte vor Augen, dass dafür alle anderen zwischengespeicherten Daten und Pläne aus dem Cache entfernt wurden - das ist unter Performance-Aspekten sehr bitter.

Und wie kann man das besser machen?

Schauen Sie sich das folgende modifizierte Skript an:

dbcc freeproccache         -- Cache und Buffer leeren
dbcc dropcleanbuffers
declare @i int             -- Schleifenvariable
       ,@m int             -- Obergrenze der Schleifenvariablen
       ,@cmd nvarchar(500) -- SQL Statement
       ,@Lname nvarchar(50)
set @i = 1
select @m = MAX(BusinessEntityID) from Person.Person
-- Hier ist der Unterschied: Das SQL Statement bleibt
-- durch die Verwendung eines Parameters konstant.
set @cmd = 'select @Lname = LastName ' +
           'from Person.Person where ' +
           'BusinessEntityID = @Param1'
while (@i < @m)
begin
   exec sp_executesql @cmd
                     ,N'@Param1 int, @Lname nvarchar(50) OUTPUT'
                     ,@Param1 = @i
                     ,@Lname = @Lname OUTPUT;
   set @i = @i + 1
end
 
Das SQL Statement wird hier nur einmal vor Beginn der Schleife erzeugt und dann immer wieder unverändert ausgeführt. Der Parameter @Param1 erhält bei jedem Schleifendurchlauf den aktuellen Wert der Zählervariablen zugewiesen. Dieses SQL Skript braucht auf meinem Rechner keine 2 Sekunden, ist also ungefähr 10-mal schneller als die erste Variante.
Wie sieht die Veränderung im Cache aus? Wieder hilft uns die bereits oben verwendete Abfrage weiter:

select entries_count
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP'

Sie zeigt, dass jetzt gerade einmal 2 Ausführungspläne im Cache liegen. Und hier liegt der wichtigste Vorteil, denn der Cache wurde nun nicht mit unbrauchbaren Ausführungsplänen überflutet. Die Grafik zeigt das sehr schön:

Cache nach Parametrisierung der Abfrage
 Je mehr Clients parallel gleichartige Abfragen stellen, desto größer fällt der Performance-Vorteil aus. Das gilt übrigens nicht nur für den SQL Server, sondern gleichermaßen auch für andere Datenbanksysteme (z. B. Oracle).

Bleibt nur noch eine Frage zu klären:
Wie erzeugt man parametrisierte SQL Abfragen mit Hochsprachen?

Am Beispiel eines ADO.NET Fragments sehen Sie, wie einfach das geht:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select LastName from Person.Person where BusinessEntityID = @Id";
cmd.Parameters.Add("@Id", SqlDbType.Int);

Wie Sie sehen, ist der Aufwand gar nicht groß. Ich finde sogar eher, dass der Code dadurch übersichtlicher wird. Und die Performance-Vorteile können enorm sein.