Optimieren von Ausführungsplänen für parametrisierte T-SQL-Abfragen mit Fensterfunktionen

Question

Nov 29, 2012, 10:59 PM

sql-server query-optimization tsql sql-server-2008-r2

Optimieren von Ausführungsplänen für parametrisierte T-SQL-Abfragen mit Fensterfunktionen

BEARBEITEN: Ich habe den Beispielcode aktualisiert und vollständige Tabellen- und Ansichtsimplementierungen als Referenz bereitgestellt, aber die wesentliche Frage bleibt unverändert.

Ich habe eine ziemlich komplexe Ansicht in einer Datenbank, die ich abzufragen versuche. Wenn ich versuche, eine Reihe von Zeilen aus der Ansicht abzurufen, indem ich die WHERE-Klausel fest mit bestimmten Fremdschlüsselwerten codiere, wird die Ansicht mit einem optimalen Ausführungsplan sehr schnell ausgeführt (Indizes werden ordnungsgemäß verwendet usw.).

SELECT * 
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20

Wenn ich jedoch versuche, der Abfrage Parameter hinzuzufügen, bricht mein Ausführungsplan plötzlich zusammen. Wenn ich die folgende Abfrage ausführe, erhalte ich Index-Scans, anstatt überall zu suchen, und die Abfrageleistung ist sehr schlecht.

DECLARE @ForeignKeyCol int = 20

SELECT * 
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = @ForeignKeyCol

Ich verwende SQL Server 2008 R2. Was gibt es hier? Worum geht es bei der Verwendung von Parametern, die zu einem suboptimalen Plan führen? Jede Hilfe wäre sehr dankbar.

Als Referenz sind hier die Objektdefinitionen, für die ich den Fehler erhalte.

CREATE TABLE [dbo].[BaseTable]
(
    [PrimaryKeyCol] [uniqueidentifier] PRIMARY KEY,
    [ForeignKeyCol] [int] NULL,
    [DataCol] [binary](1000) NOT NULL
)

CREATE NONCLUSTERED INDEX [IX_BaseTable_ForeignKeyCol] ON [dbo].[BaseTable]
(
    [ForeignKeyCol] ASC
)

CREATE VIEW [dbo].[ViewOnBaseTable]
AS
SELECT
    PrimaryKeyCol,
    ForeignKeyCol,
    DENSE_RANK() OVER (PARTITION BY ForeignKeyCol ORDER BY PrimaryKeyCol) AS ForeignKeyRank,
    DataCol
FROM
    dbo.BaseTable

Ich bin sicher, dass die Fensterfunktion das Problem ist, aber ich filtere meine Abfrage nach einem einzelnen Wert, nach dem die Fensterfunktion partitioniert. Daher würde ich erwarten, dass der Optimierer zuerst filtert und dann die Fensterfunktion ausführt. Dies geschieht im hartcodierten Beispiel, jedoch nicht im parametrisierten Beispiel. Unten sehen Sie die beiden Abfragepläne. Der obere Plan ist gut und der untere Plan ist schlecht.