Freigeben über

Verringerte Abfrageleistung nach dem Upgrade von SQL Server 2012 oder früher auf 2014 oder höher

  • Artikel

Nachdem Sie SQL Server von 2012 oder einer früheren Version auf 2014 oder eine höhere Version aktualisiert haben, tritt möglicherweise das folgende Problem auf: Die meisten ursprünglichen Abfragen werden gut ausgeführt, aber einige Ihrer Abfragen werden langsamer als in der vorherigen Version ausgeführt. Obwohl es viele mögliche Ursachen und faktoren gibt, sind die Änderungen im Kardinalitätsschätzungsmodell (Kardinalitätsschätzung ) nach dem Upgrade eine relativ häufige Ursache. Ab SQL Server 2014 wurden wesentliche Änderungen an den CE-Modellen eingeführt.

Dieser Artikel enthält Schritte zur Problembehandlung und Lösungen für Probleme mit der Abfrageleistung, die bei Verwendung der Standard-CE auftreten, aber nicht bei Verwendung der Legacy-CE auftreten.

Hinweis

Wenn alle Abfragen nach dem Upgrade langsamer ausgeführt werden, gelten die in diesem Artikel beschriebenen Schritte zur Problembehandlung wahrscheinlich nicht für Ihre Situation.

Problembehandlung: Ermitteln Sie, ob CE-Änderungen das Problem sind, und ermitteln Sie den Grund

Schritt 1: Ermitteln, ob die Standard-CE verwendet wird

  1. Wählen Sie eine Abfrage aus, die nach dem Upgrade langsamer ausgeführt wird.
  2. Führen Sie die Abfrage aus, und erfassen Sie den Ausführungsplan.
  3. Überprüfen Sie im ausführungsplan Eigenschaftenfenster CardinalityEstimationModelVersion. Suchen Sie die CE-Modellversion aus dem ausführungsplan Eigenschaftenfenster.
  4. Der Wert 70 gibt die Legacy-CE an, und ein Wert von 120 oder höher gibt die Verwendung der Standard-CE an.

Wenn die Legacy-CE verwendet wird, sind die CE-Änderungen nicht die Ursache für das Leistungsproblem. Wenn die Standard-CE verwendet wird, fahren Sie mit dem nächsten Schritt fort.

Schritt 2: Ermitteln, ob der Abfrageoptimierer mithilfe der Legacy-CE einen besseren Plan generieren kann

Führen Sie die Abfrage mit der Legacy-CE aus. Wenn die Leistung besser ist als die Verwendung der Standard-CE, fahren Sie mit dem nächsten Schritt fort. Wenn sich die Leistung nicht verbessert, sind die CE-Änderungen nicht die Ursache.

Schritt 3: Ermitteln, warum die Abfrage mit der Legacy-CE besser funktioniert

Testen Sie die verschiedenen CE-bezogenen Abfragehinweise für Ihre Abfrage. Verwenden Sie für SQL Server 2014 die entsprechenden Ablaufverfolgungsflags 4137, 9472 und 4139, um die Abfrage zu testen. Bestimmen Sie anhand dieser Tests, welche Hinweise oder Ablaufverfolgungsflags sich positiv auf die Leistung auswirken.

Lösung

Um das Problem zu beheben, probieren Sie eine der folgenden Methoden aus:

  • Optimieren Sie die Abfrage.

    Verständlicherweise ist es nicht immer möglich, Abfragen neu zu schreiben, aber vor allem, wenn nur wenige Abfragen umgeschrieben werden können, sollte dieser Ansatz die erste Wahl sein. Optimal geschriebene Abfragen sind unabhängig von den CE-Versionen besser.

  • Verwenden Sie abfragehinweise, die in Schritt 3 identifiziert wurden.

    Dieser gezielte Ansatz ermöglicht es anderen Workloads, von den standardmäßigen CE-Annahmen und -Verbesserungen zu profitieren. Darüber hinaus ist es eine stabilere Option als das Erstellen einer Planhinweisliste. Und es erfordert keine Abfragespeicher (QDS), im Gegensatz zum Erzwingen eines Plans (die robusteste Option).

  • Erzwingen Sie einen guten Plan.

    Dies ist eine günstige Option, die für bestimmte Abfragen verwendet werden kann. Das Erzwingen eines Plans kann mithilfe einer Planhinweisliste oder QDS erfolgen. QDS ist im Allgemeinen einfacher zu verwenden.

  • Verwenden Sie eine datenbankbezogene Konfiguration, um die Legacy-CE zu erzwingen.

    Dies ist ein weniger bevorzugter Ansatz, da es sich um eine datenbankweite Einstellung handelt, die für alle Abfragen für diese Datenbank gilt. Dennoch ist dies manchmal notwendig, wenn ein gezielter Ansatz nicht möglich ist. Dies ist sicherlich die einfachste Option zu implementieren.

  • Verwenden Sie das Ablaufverfolgungsflag 9841, um legacy CE global zu erzwingen. Verwenden Sie hierzu DBCC TRACEON , oder legen Sie das Ablaufverfolgungsflag als Startparameter fest.

    Dies ist der am wenigsten ausgerichtete Ansatz und sollte nur als vorübergehende Entschärfung verwendet werden, wenn Sie keine der anderen Optionen anwenden können.

Optionen zum Aktivieren von Legacy CE

Abfrageebene: Abfragehinweis oder QUERYTRACEON-Option verwenden

  • Verwenden Sie für SQL Server 2016 SP1 und höher den Hinweis FORCE_LEGACY_CARDINALITY_ESTIMATION für Ihre Abfrage, z. B.:

    SELECT * FROM Table1
WHERE Col1 = 10
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

  • Aktivieren Sie das Ablaufverfolgungsflag 9481, um einen Legacy-CE-Plan zu erzwingen. Hier ist ein Beispiel:

    SELECT * FROM Table1
WHERE Col1 = 10
OPTION (QUERYTRACEON 9481)

Datenbankebene: Festlegen des bereichsbezogenen Konfigurations- oder Kompatibilitätsgrads

  • Ändern Sie für SQL Server 2016 und höhere Versionen die datenbankweit gültige Konfiguration:

      --Force a specific database to use legacy CE
  ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

  -- Validate what databases use legacy CE
  SELECT name, value
      FROM sys.database_scoped_configurations 
  WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';

  • Ändern Sie den Kompatibilitätsgrad für die Datenbank. Dies ist die einzige Option auf Datenbankebene, die für SQL Server 2014 verfügbar ist. Beachten Sie, dass sich diese Änderung auf mehr als nur die CE auswirkt. Um die Auswirkungen von Änderungen des Kompatibilitätsgrads zu ermitteln, wechseln Sie zu ALTER DATABASE-Kompatibilitätsgrad (Transact-SQL), und untersuchen Sie die darin enthaltenen Tabellen "Unterschiede".

    ALTER DATABASE <YourDatabase>
SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level

Hinweis

Diese Änderung wirkt sich auf alle Abfragen aus, die im Kontext der Datenbank ausgeführt werden, für die die Konfiguration geändert wird, es sei denn, es wird ein überschreibendes Ablaufverfolgungsflag oder ein Abfragehinweis verwendet. Abfragen, die aufgrund der Standard-CE eine bessere Leistung erbringen, können zurückgesetzt werden.

Serverebene: Ablaufverfolgungsflag verwenden

Verwenden Sie das Ablaufverfolgungsflag 9481, um serverweite Legacy-CE zu erzwingen:

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

Hinweis

Diese Änderung wirkt sich auf alle Abfragen aus, die im Kontext des SQL Server instance ausgeführt werden, es sei denn, es wird ein überschreibendes Ablaufverfolgungsflag oder ein Abfragehinweis verwendet. Abfragen, die aufgrund der Standard-CE eine bessere Leistung erbringen, können zurückgesetzt werden.

Häufig gestellte Fragen

Für bereits vorhandene Datenbanken, die mit niedrigeren Kompatibilitätsgraden ausgeführt werden, wird der empfohlene Workflow zum Aktualisieren des Abfrageprozessors auf einen höheren Kompatibilitätsgrad unter Ändern des Datenbankkompatibilitätsmodus und Verwenden der Abfragespeicher und Abfragespeicher Verwendungsszenarien beschrieben. Die in diesem Artikel eingeführte Methodik gilt für Verschiebungen auf 130 oder höher für SQL Server und Azure SQL Datenbank.

F2: Ich habe keine Zeit, um CE-Änderungen zu testen. Was kann ich in diesem Fall tun?

Für bereits vorhandene Anwendungen und Workloads wird nicht empfohlen, auf die Standard-CE umzusteigen, bis genügend Regressionstests durchgeführt wurden. Wenn Sie weiterhin Zweifel haben, empfehlen wir Ihnen, weiterhin ein Upgrade SQL Server durchzuführen und auf den neuesten verfügbaren Kompatibilitätsgrad zu wechseln. Aktivieren Sie als Vorsichtsmaßnahme auch das Ablaufverfolgungsflag 9481 für SQL Server 2014, oder konfigurieren Sie die LEGACY_CARDINALITY_ESTIMATION datenbankbezogene KonfigurationON für SQL Server 2016 und höhere Versionen, bis Sie die Möglichkeit zum Testen haben.

F3: Gibt es Nachteile bei der dauerhaften Verwendung der Legacy-CE?

Zukünftige Verbesserungen und Korrekturen im Zusammenhang mit Kardinalitätsschätzungen sind auf neuere Versionen ausgerichtet. Version 70 ist ein akzeptabler Zwischenzustand. Nach sorgfältigen Tests empfehlen wir jedoch, schließlich auf eine neuere CE-Version umzusteigen, um von den neuesten CE-Fixes zu profitieren. Es besteht eine hohe Wahrscheinlichkeit, dass sich der Abfrageplan ändert, wenn sie von der Legacy-CE wechseln. Testen Sie daher, bevor Sie Änderungen an Produktionssystemen vornehmen. Die Änderungen können die Abfrageleistung in vielen Fällen verbessern, aber in einigen Fällen kann die Abfrageleistung beeinträchtigt werden.

Wichtig

Die Standard-CE ist der Standard Codepfad, der langfristig zukünftige Investitionen und eine umfassendere Testabdeckung erhalten wird. Planen Sie daher nicht vor, die Legacy-CE auf unbestimmte Zeit zu verwenden.

F4: Ich habe Tausende von Datenbanken und möchte nicht LEGACY_CARDINALITY_ESTIMATION für jede manuell aktivieren. Gibt es eine alternative Methode?

Aktivieren Sie für SQL Server 2014 das Ablaufverfolgungsflag 9481, um die Legacy-CE für alle Datenbanken unabhängig vom Kompatibilitätsgrad zu verwenden. Führen Sie für SQL Server 2016 und höher die folgende Abfrage aus, um Datenbanken zu durchlaufen. Die Einstellung wird auch dann aktiviert, wenn die Datenbank wiederhergestellt oder auf einem anderen Server angefügt wird.

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

Für Azure SQL-Datenbank können Sie ein Supportticket erstellen, damit dieses Ablaufverfolgungsflag auf Abonnementebene, aber nicht auf Serverebene aktiviert ist.

F5: Wird die Ausführung mit der Legacy-CE verhindern, dass ich Zugriff auf neue Features erhalte?

Auch wenn LEGACY_CARDINALITY_ESTIMATION aktiviert ist, erhalten Sie weiterhin Zugriff auf die neueste Funktionalität, die in der Version von SQL Server und dem zugehörigen Datenbank-Kompatibilitätsgrad enthalten ist. Beispielsweise kann eine Datenbank mit aktiviertem LEGACY_CARDINALITY_ESTIMATION, die am SQL Server 2017 mit Datenbank-Kompatibilitätsgrad 140 ausgeführt wird, weiterhin von der Featurefamilie der adaptiven Abfrageverarbeitung profitieren.

F6: Wann wird der Support für die Legacy-Ce nicht mehr unterstützt?

Es ist nicht geplant, die Unterstützung der Legacy-CE an diesem Punkt zu beenden. Zukünftige Verbesserungen und Korrekturen im Zusammenhang mit Kardinalitätsschätzungen sind jedoch auf neuere Versionen der CE ausgerichtet.

F7: Ich habe nur wenige Abfragen, die mit der Standard-CE zurückgesetzt werden, aber die meisten Abfragen sind die gleiche oder sogar verbessert. Wie behebe ich diese Situation?

Eine differenziertere Alternative zum serverspezifischen Ablaufverfolgungsflag 9481 oder der LEGACY_CARDINALITY_ESTIMATION datenbankweit gültigen Konfiguration ist die Verwendung des abfragebezogenen USE HINT-Konstrukts. Weitere Informationen finden Sie unter USE HINT-Abfragehinweisargument in SQL Server 2016 und USE HINT.

Hinweis

Es gibt auch eine QUERYTRACEON Option mit dem Ablaufverfolgungsflag 9481, aber Sie sollten stattdessen die Verwendung von USE HINT in Betracht ziehen, da es semantisch sauberer ist und keine speziellen Berechtigungen erfordert.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION ermöglicht es Ihnen, das CE-Modell des Abfrageoptimierers unabhängig vom Kompatibilitätsgrad der Datenbank auf Version 70 festzulegen. Siehe Abfrageebene: Abfragehinweis verwenden oder QUERYTRACEON-Option verwenden.

Wenn es nur eine Abfrage gibt, die mit der Standard-CE problematisch ist, können Sie alternativ einen in Abfragespeicher gespeicherten Legacy-CE-Plan erzwingen oder in Verbindung mit einer Planhinweisliste verwendenFORCE_LEGACY_CARDINALITY_ESTIMATION.

CE ist ein komplexes Problem, und die Algorithmen basieren auf den weniger perfekten Daten, die für Schätzungen verfügbar sind, z. B. Statistiken für Tabellen und Indizes. Es gibt keine Informationen für einige out-of-model-Konstrukte wie Tabellenwertfunktionen (Table-Valued Functions, TVFs) und Modelle, die auf vielen Annahmen basieren (z. B. Korrelation oder Unabhängigkeit der Prädikate und Spalten, einheitliche Datenverteilung, Einschluss usw.).

Angesichts der unbegrenzten Kombinationen aus Kundenschema, Daten und Workloads ist es fast unmöglich, Modelle zu wählen, die für alle Fälle geeignet sind. Während einige Änderungen in der Standard-CE Fehler enthalten können (wie jede andere Software dies kann) und behoben werden können, werden andere Probleme durch eine Modelländerung verursacht.

Änderungen in CE-Versionen, insbesondere von 70 auf 120, umfassen viele verschiedene Optionen für verwendete Modelle. Gehen Sie beispielsweise bei der Schätzung von Filtern von einer gewissen Korrelation zwischen den Prädikaten aus, da in der Praxis häufig eine solche Korrelation vorhanden ist und das CE-Modell 70 in solchen Fällen die Ergebnisse unterschätzt. Während diese Änderungen für viele Workloads getestet und viele Abfragen verbessert wurden, war die Legacy-CE bei einigen anderen Abfragen eine bessere Übereinstimmung, und daher kann es zu Leistungsregressionen mit der Standard-CE kommen.

Leider wird es nicht als Fehler angesehen. Verwenden Sie in solchen Situationen eine Problemumgehung, z. B. die Optimierung der Abfrage, genau wie bei der Legacy-CE, wenn die Abfrageleistung nicht akzeptabel ist, oder erzwingen Sie ein vorheriges CE-Modell oder einen bestimmten Ausführungsplan.

F9: Gibt es eine Ressource, um Details zu den Kardinalitätsänderungen in der Standard-CE und den Auswirkungen auf die Abfrageleistung zu erfahren?

Weitere Informationen finden Sie unter Optimieren Ihrer Abfragepläne mit dem SQL Server 2014 Kardinalitätsschätzung, und lesen Sie den Abschnitt "Was hat sich in SQL Server 2014 geändert?".