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.