Mit Hilfe der Systemprozedur sp_executeSQL werden Transact-SQL-Anweisungen oder -Batches ausgeführt, die bzw. der mehrfach wiederverwendet werden kann oder dynamisch erstellt wurde. Insbesondere wird sp_executeSQL eingesetzt, um kompilierte Pläne für die erneute Verwendung in den Plancache zu laden. Das kann aber auch zu Problemen führen, die der nachfolgende Artikel behandelt.

Für das nachfolgende Beispiel wird das Datenmodell aus dem Artikel "Optimierung von Datenbankmodellen-Richtige Wahl von Datentypen und Indexen (Teil 3)" verwendet. Die Relation dbo.tbl_Companies hat den nachfolgenden Aufbau:

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)       NULL
);
GO

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

Für das Beispiel ist weiterhin entscheidend, wie die Daten im Attribut [CostCenter] verteilt sind (DENSITY). Dazu wird zunächst mittels DBCC SHOW_STATISTICS mit der Option WITH HISTOGRAM die Häufigkeit des Vorkommens für jeden unterschiedlichen Wert in im Dataset ermittelt. Bei der Analyse fällt auf, dass die Verteilung des Schlüsselattributs [CostCenter] recht unterschiedlich ist.

DBCC SHOW_STATISTICS ('dbo.tbl_Companies', ix_tbl_Companies_CostCenter) WITH HISTOGRAM;

Problemstellung

Das obige Ergebnis ist zur besseren Darstellung auf ein paar Zeilen resultiert, um die Unterschiede aufzuzeigen. Basierend auf diesen Ergebnissen kann nun das eigentliche Problem untersucht werden. Dazu dient der folgende Code und die dazugehörigen Ausführungspläne.

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

DECLARE @stmt nvarchar(4000) = 'SELECT * FROM dbo.tbl_Companies WHERE CostCenter = @CostCenter;';
DECLARE @parms nvarchar(255) = '@CostCenter char(7)';

EXEC sp_executeSQL @stmt, @parms, @CostCenter = 'C001000';
EXEC sp_executeSQL @stmt, @parms, @CostCenter = 'C006000';

Es fällt auf, dass beide Abfragen unabhängig voneinander den gleichen Abfrageplan verwenden. Die Ausführung der ersten Abfrage mit dem Wert "C001000" hat - auf Grund der niedrigen Selektivität (Kardinalität) - einen Abfrageplan erstellt, der einen "Clustered Index Scan" als optimale Abfragestrategie ermittelt hat. Aus dem obigen Ergebnis lässt sich ableiten, dass beide Abfragen unabhängig voneinander einen gemeinsamen Abfrageplan verwenden. Die nachfolgende Abfrage zeigt, dass diese Vermutung richtig ist.

SELECT  s_t.Text,
        c_p.usecounts
FROM    sys.dm_exec_cached_plans c_p CROSS APPLY sys.dm_exec_sql_text (c_p.plan_handle) s_t
WHERE   s_t.text LIKE '%SELECT * FROM dbo.tbl_Companies%' AND
        s_t.text NOT LIKE '%sys.dm_exec_query_stats%'

Das Ergebnis zeigt zwei wichtige Informationen. Zum einen wird erkennbar, dass der Abfrageplan zweimal aufgerufen wurde (somit stimmt die Aussage, dass ein gemeinsamer Abfrageplan vorliegt). Zum anderen ist interessant, dass der Abfrageplan parametrisiert ist. Dies wiederum lässt den Schluss zu, dass hier "Parameter Sniffing" angewendet wurde. Hierbei handelt es sich um eine Technik, bei der von Microsoft SQL Server der Parameter "abgefangen" wird und seine Kardinalität überprüft wird. Auf Basis der Kardinalität wird dann ein optimaler Abfrageplan erstellt und gespeichert. Die Rahmenbedingungen, auf deren Basis die Entscheidung gefällt wird, ergibt sich aus der nachfolgenden Abfrage, die auf Basis der oben ausgeführten Abfrage etwas modifiziert wurde.

SELECT  s_t.Text,
        c_p.usecounts,
        q_s.total_rows,
        q_s.last_logical_reads,
        q_s.last_physical_reads,
        q_s.last_rows
FROM    sys.dm_exec_cached_plans c_p CROSS APPLY sys.dm_exec_sql_text (c_p.plan_handle) s_t
        INNER JOIN sys.dm_exec_query_stats q_s ON (c_p.plan_handle = q_s.plan_handle)
WHERE   s_t.text LIKE '%SELECT * FROM dbo.tbl_Companies%' AND
        s_t.text NOT LIKE '%sys.dm_exec_query_stats%'

Aus dieser Analyse wird ersichtlich, dass der Abfrageplan auf Basis von 587 Datenzeilen erstellt wurde aber bei der letzten Ausführung nur ein Datensatz zurückgeliefert wurde. Der gewählte Abfrageplan ist basierend auf dem ersten Ergebnis richtig gewesen, jedoch ist dieser Abfrageplan bei einer sehr hohen Selektivität nicht geeignet.

Lösungsansatz

Wie man Microsoft SQL Server dazu zwingen kann, keine Abfragepläne zu erstellen habe ich im oben genannten Artikel bereits erklärt und demonstriert. Wenden wir diese Möglichkeiten nun auf das  obige Beispiel an.

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

DECLARE @stmt nvarchar(4000) = 'SELECT * FROM dbo.tbl_Companies WHERE CostCenter = @CostCenter OPTION (RECOMPILE);';
DECLARE @parms nvarchar(255) = '@CostCenter char(7)';

EXEC sp_executeSQL @stmt, @parms, @CostCenter = 'C001000'; EXEC sp_executeSQL @stmt, @parms, @CostCenter = 'C006000';

Fazit

Die Systemprozedur sp_executeSQL ist eine sehr wichtige Option, wenn dynamisches SQL verwendet werden muss. Auf Grund der Möglichkeit, der Systemprozedur sp_executeSQL Parameter zu übergeben, verhält sich Code, der mittels sp_executeSQL ausgeführt wird, wie eine Prozedur! Bei der Ausführung von sp_executeSQL sollte also auf jeden Fall das "Parameter Sniffing" berücksichtigt werden. Um Parameter Sniffing zu verhindern, kann u. a. OPTION (RECOMPILE) verwendet werden, um bei jeder Ausführung eine Recompilierung zu erzwingen.

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