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 beitragende Faktoren gibt, ist eine relativ häufige Ursache die Änderungen des Kardinalitätsschätzungsmodells (CE) nach dem Upgrade. Die CE-Modelle wurden ab SQL Server 2014 erheblich geändert.

Dieser Artikel enthält Problembehandlungsschritte und Lösungen für Abfrageleistungsprobleme, die bei Verwendung des Standard-CE auftreten, aber nicht bei Verwendung der Legacy-CE auftreten.

Notiz

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

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

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 aus dem Ausführungsplan Eigenschaftenfenster KardinalityEstimationModelVersion. Suchen Sie die CE-Modellversion aus dem Ausführungsplan Eigenschaftenfenster.
  4. Ein Wert von 70 gibt die Legacy-CE an, und ein Wert von 120 oder höher gibt die Verwendung des Standard-CE an.

Wenn die Legacy-CE verwendet wird, sind die CE-Änderungen nicht die Ursache des Leistungsproblems. Wenn die Standard-CE verwendet wird, fahren Sie mit dem nächsten Schritt fort.

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

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

Schritt 3: Herausfinden, warum die Abfrage mit dem legacy CE besser funktioniert

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

Lösung

Um dieses Problem zu lösen, probieren Sie eine der folgenden Methoden aus:

  • Optimieren Sie die Abfrage.

    Verständlicherweise ist es nicht immer möglich, Abfragen neu zu schreiben, aber insbesondere, wenn nur wenige Abfragen neu geschrieben werden können, sollte dieser Ansatz die erste Wahl sein. Optimal geschriebene Abfragen werden unabhängig von CE-Versionen besser ausgeführt.

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

    Mit diesem gezielten Ansatz können andere Workloads von den standardmäßigen CE-Annahmen und -Verbesserungen profitieren. Darüber hinaus ist es eine robustere Option als das Erstellen eines Planleitfadens. Und es ist nicht erforderlich, Abfragespeicher (QDS) zu erzwingen, im Gegensatz zum Erzwingen eines Plans (die robusteste Option).

  • Erzwingen Sie einen guten Plan.

    Dies ist eine günstige Option und kann verwendet werden, um bestimmte Abfragen abzuzielen. Das Erzwingen eines Plans kann mithilfe eines Planleitfadens oder einer QDS erfolgen. QDS ist im Allgemeinen einfacher zu verwenden.

  • Verwenden Sie die Datenbankbereichskonfiguration , um die Legacy-CE zu erzwingen.

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

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

    Dies ist der am wenigsten gezielte Ansatz und sollte nur als temporäre 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öhere Versionen Hinweise 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 die Ablaufverfolgungskennzeichnung 9481, um einen legacy CE-Plan zu erzwingen. Ein Beispiel:

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

Datenbankebene: Festlegen der Bereichskonfiguration oder Kompatibilitätsstufe

  • Ändern Sie für SQL Server 2016 und höhere Versionen die Datenbankbereichskonfiguration:

      --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 die Kompatibilitätsstufe für die Datenbank. Es ist die einzige Option auf Datenbankebene, die für SQL Server 2014 verfügbar ist. Beachten Sie, dass sich diese Änderung mehr als nur auf den CE auswirkt. Um die Auswirkungen von Änderungen auf Kompatibilitätsebene zu ermitteln, wechseln Sie zur ALTER DATABASE-Kompatibilitätsstufe (Transact-SQL), und überprüfen Sie die Darin enthaltenen Tabellen "Unterschiede".

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

Notiz

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 überschriebenes Ablaufverfolgungs-Flag oder ein Abfragehinweis verwendet. Abfragen, die aufgrund von Standard-CE besser funktionieren, können zurücktreten.

Serverebene: Ablaufverfolgungskennzeichnung verwenden

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

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

Notiz

Diese Änderung wirkt sich auf alle Abfragen aus, die im Kontext der SQL Server-Instanz ausgeführt werden, es sei denn, ein überschriebenes Ablaufverfolgungsflaggen oder Abfragehinweis wird verwendet. Abfragen, die aufgrund von Standard-CE besser funktionieren, können zurücktreten.

Häufig gestellte Fragen

Für bereits vorhandene Datenbanken, die auf niedrigeren Kompatibilitätsebenen ausgeführt werden, wird der empfohlene Workflow für das Upgrade des Abfrageprozessors auf eine höhere Kompatibilitätsstufe im Ändern des Datenbankkompatibilitätsmodus und unter Verwendung der Abfragespeicher- und Abfragespeicher Verwendungsszenarien detailliert beschrieben. Die im 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 den Standard-CE zu wechseln, bis ausreichende Regressionstests durchgeführt wurden. Wenn Sie weiterhin Zweifel haben, empfehlen wir, sql Server weiterhin zu aktualisieren und auf die neueste verfügbare Kompatibilitätsstufe zu wechseln. Aktivieren Sie als Vorsichtsmaßnahme auch das Ablaufverfolgungskennzeichnung 9481 für SQL Server 2014, oder konfigurieren Sie die konfiguration für LEGACY_CARDINALITY_ESTIMATION Datenbankbereichskonfiguration ON für SQL Server 2016 und höhere Versionen, bis Sie die Möglichkeit haben, zu testen.

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

Zukünftige Kardinalitätsverbesserungen und Fixes werden auf neuere Versionen zentriert. Version 70 ist ein akzeptabler Zwischenzustand. Nach sorgfältigen Tests empfehlen wir jedoch, schließlich zu einer neueren CE-Version zu wechseln, um von den neuesten CE-Fixes zu profitieren. Es gibt eine hohe Wahrscheinlichkeit von Abfrageplanänderungen, wenn sie von der älteren CE wechseln, also testen Sie, 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 Hauptcodepfad, der zukünftige Investitionen und eine tiefere Testabdeckung langfristig erhalten wird. Planen Sie daher nicht, die Legacy-CE auf unbestimmte Zeit zu verwenden.

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

Aktivieren Sie für SQL Server 2014 das Ablaufverfolgungskennzeichnung 9481, um die Legacy-CE für alle Datenbanken unabhängig von der Kompatibilitätsstufe zu verwenden. Führen Sie für SQL Server 2016 und höhere Versionen die folgende Abfrage aus, um Datenbanken zu durchlaufen. Die Einstellung wird auch 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 Ablaufverfolgungskennzeichnung auf Abonnementebene, aber nicht auf Serverebene aktiviert ist.

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

Auch wenn LEGACY_CARDINALITY_ESTIMATION aktiviert ist, erhalten Sie weiterhin Zugriff auf die neueste Funktionalität, die in der Version von SQL Server und der zugehörigen Datenbankkompatibilitätsebene enthalten ist. Beispielsweise kann eine Datenbank mit LEGACY_CARDINALITY_ESTIMATION aktivierte Ausführung auf Datenbankkompatibilitätsebene 140 auf SQL Server 2017 weiterhin von der Featurefamilie für adaptive Abfrageverarbeitung profitieren.

F6: Wann wird die Legacy-CE nicht mehr unterstützt?

Wir haben derzeit keine Pläne, die Unterstützung der Legacy-CE zu beenden. Zukünftige Kardinalitäts-bezogene Verbesserungen und Fixes werden jedoch auf neuere Versionen des CE ausgerichtet.

F7: Ich habe nur einige Abfragen, die mit der Standard-CE zurücktreten, aber die meisten Abfrageleistung ist identisch oder sogar verbessert. Wie sollte ich vorgehen?

Eine differenziertere Alternative zum serverbezogenen Ablaufverfolgungskennzeichnung 9481 oder die konfiguration mit LEGACY_CARDINALITY_ESTIMATION Datenbankbereich ist die Verwendung des abfragebezogenen USE HINT-Konstrukts. Weitere Informationen finden Sie unter USE HINT-Abfragehinweisargument in SQL Server 2016 und USE HINT.

Notiz

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

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION ermöglicht es Ihnen, das Abfrageoptimierer-CE-Modell auf Version 70 festzulegen, unabhängig von der Kompatibilitätsstufe der Datenbank. Siehe Abfrageebene: Abfragehinweise oder QUERYTRACEON-Option verwenden.

Wenn es nur eine Abfrage gibt, die mit dem Standard-CE problematisch ist, könnten Sie einen in Abfragespeicher gespeicherten Legacy-CE-Plan erzwingen oder in Verbindung mit einem Planleitfaden verwendenFORCE_LEGACY_CARDINALITY_ESTIMATION.

CE ist ein komplexes Problem, und die Algorithmen basieren auf den weniger als 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 (TVFs) und Modelle basierend auf vielen Annahmen (z. B. Korrelation oder Unabhängigkeit der Prädikate und Spalten, einheitliche Datenverteilung, Eindämmung usw.).

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

Änderungen in CE-Versionen, insbesondere von 70 bis 120, umfassen viele verschiedene Auswahlmöglichkeiten für verwendete Modelle. Nehmen Sie beispielsweise bei der Schätzung von Filtern einige Korrelationsstufen zwischen den Prädikaten an, da diese Korrelation in der Praxis häufig vorhanden ist, und das CE-Modell 70 die Ergebnisse in solchen Fällen unterschätzt würde. Während diese Änderungen für viele Workloads getestet und viele Abfragen verbessert wurden, war die Legacy-CE für einige andere Abfragen eine bessere Übereinstimmung, und damit mit dem Standard-CE können Leistungsregressionen beobachtet werden.

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

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

Ausführliche Informationen finden Sie unter Optimieren Ihrer Abfragepläne mit dem SQL Server 2014-Kardinalitäts-Estimator und lesen Sie den Abschnitt "Was wurde in SQL Server 2014 geändert?".