Problembehandlung bei Problemen mit hoher CPU-Auslastung in SQL Server

Gilt für: SQL Server

Zusammenfassung

Hohe CPU-Auslastung in Microsoft SQL Server (der sqlservr.exe-Prozess, der übermäßige Prozessorzeit in Anspruch nimmt) ergibt sich häufig aus ineffizienten Abfragen, fehlenden Indizes, veralteten Statistiken, parameterabhängigen Plänen oder einer Zunahme der Arbeitsauslastung. Dieser Artikel führt Sie durch ein geordnetes Problembehandlungsverfahren, um zunächst zu bestätigen, dass SQL Server die Quelle der CPU-Auslastung ist, und dann die Abfragen mit dem höchsten CPU-Verbrauch zu identifizieren. Anschließend werden gezielte Korrekturen wie das Aktualisieren von Statistiken, das Hinzufügen fehlender Indizes, das Beheben von Problemen mit Parameter Sniffing und der SARGability, das Deaktivieren umfangreichen Tracings und das Verringern von Spinlock-Kontention erläutert. Schließlich umfasst es die Optimierung des Betriebssystems und der virtuellen Maschine, einschließlich des Windows-Energieplans und Anleitungen dazu, wann auf mehr CPUs hochzuskalieren ist. Führen Sie diese Schritte aus, um eine dauerhafte CPU-Auslastung zu reduzieren, die Abfrageleistung wiederherzustellen und zu entscheiden, wann eine Hardwareskalierung erforderlich ist.

Häufige Ursachen für eine hohe CPU-Auslastung in SQL Server

Obwohl viele mögliche Ursachen für eine hohe CPU-Auslastung in SQL Server vorhanden sind, sind die folgenden Ursachen am häufigsten:

• Hohe logische Lesevorgänge, die durch Tabellen- oder Indexscans aufgrund der folgenden Bedingungen verursacht werden:
  • Veraltete Statistiken
  • Fehlende Indizes
  • Probleme bei parametersensitiven Plänen (PSP)
  • Schlecht entworfene Abfragen
• Zunehmende Arbeitsauslastung

Führen Sie die folgenden Schritte aus, um Probleme mit hoher CPU-Auslastung in SQL Server zu beheben.

Schritt 1: Überprüfen Sie, ob SQL Server eine hohe CPU-Auslastung verursacht

Verwenden Sie eines der folgenden Tools, um zu überprüfen, ob der SQL Server-Prozess tatsächlich zu einer hohen CPU-Auslastung beiträgt:

• Task-Manager: Überprüfen Sie auf der Registerkarte Prozesse, ob der Spaltenwert CPU für SQL Server Windows NT-64 Bit nahezu 100 Prozent beträgt.

• Leistungs- und Ressourcenmonitor (perfmon)

  • Zähler: Process/%User Time, % Privileged Time
  • Instanz: sqlservr

• Sie können das folgende PowerShell-Skript verwenden, um die Leistungsindikatordaten über einen Zeitraum von 60 Sekunden zu erfassen:

  $serverName = $env:COMPUTERNAME
  $Counters = @(
      ("\\$serverName" + "\Process(sqlservr*)\% User Time"), ("\\$serverName" + "\Process(sqlservr*)\% Privileged Time")
  )
  Get-Counter -Counter $Counters -MaxSamples 30 | ForEach {
      $_.CounterSamples | ForEach {
          [pscustomobject]@{
              TimeStamp = $_.TimeStamp
              Path = $_.Path
              Value = ([Math]::Round($_.CookedValue, 3))
          }
          Start-Sleep -s 2
      }
  }
  

  Wenn % User Time konsistent größer als 90 % Prozessorzeit ist (% Benutzerzeit ist die Summe der Prozessorzeit auf jedem Prozessor, ihr Maximalwert beträgt 100 % * (Anzahl der CPUs)), verursacht der SQL Server-Prozess eine hohe CPU-Auslastung. Wenn % Privileged time jedoch durchgängig mehr als 90 Prozent beträgt, tragen Ihre Antiviren-Software, andere Treiber oder eine andere Betriebssystemkomponente auf dem Computer zu einer hohen CPU-Auslastung bei. Sie sollten mit Ihrem Systemadministrator zusammenarbeiten, um die Grundursache dieses Verhaltens zu analysieren.

• Leistungsdashboard: Klicken Sie in SQL Server Management Studio mit der rechten Maustaste auf <SQLServerInstance> und wählen Sie Berichte>Standardberichte>Leistungsdashboard aus.

  Das Dashboard veranschaulicht ein Diagramm mit dem Titel "System CPU-Auslastung " mit einem Balkendiagramm. Die dunklere Farbe gibt die CPU-Auslastung der SQL Server-Engine an, während die hellere Farbe die CPU-Auslastung des gesamten Betriebssystems darstellt (siehe Legende im Diagramm als Referenz). Wählen Sie die kreisförmige Schaltfläche zum Aktualisieren oder F5 aus, um die aktualisierte Auslastung anzuzeigen.

Schritt 2: Identifizieren Sie die Abfragen, die zur CPU-Auslastung beitragen

Wenn der Sqlservr.exe Prozess bei weitem eine hohe CPU-Auslastung verursacht, ist der häufigste Grund SQL-Server-Abfragen, die Tabellen- oder Indexscans ausführen, gefolgt von Sortier- und Hash-Operationen sowie von Schleifen (geschachtelter Schleifenoperator oder WHILE (T-SQL)). Um eine Vorstellung davon zu bekommen, wie viel CPU die Abfragen derzeit im Verhältnis zur gesamten CPU-Kapazität verbrauchen, führen Sie die folgende Anweisung aus:

DECLARE @init_sum_cpu_time int,
        @utilizedCpuCount int 
--get CPU count used by SQL Server
SELECT @utilizedCpuCount = COUNT( * )
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' 
--calculate the CPU usage by queries OVER a 5 sec interval 
SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests
WAITFOR DELAY '00:00:05'
SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity] 
FROM sys.dm_exec_requests

Führen Sie die folgende Anweisung aus, um die Abfragen zu identifizieren, die derzeit für hohe CPU-Aktivitäten verantwortlich sind:

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Wenn die CPU im Moment nicht durch Abfragen belastet wird, können Sie die folgende Anweisung ausführen, um nach historischen, CPU-gebundenen Abfragen zu suchen:

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Schritt 3: Aktualisieren Sie die Statistiken

Nachdem Sie die Abfragen identifiziert haben, die die meiste CPU verbrauchen, aktualisieren Sie Statistiken für die Tabellen, die von diesen Abfragen verwendet werden. Verwenden Sie die sp_updatestats gespeicherte Systemprozedur, um die Statistiken für alle benutzerdefinierten und internen Tabellen in der aktuellen Datenbank zu aktualisieren. Zum Beispiel:

exec sp_updatestats

Weitere Informationen zu sp_updatestats finden Sie unter sp_updatestats.

Wenn SQL Server weiterhin eine übermäßig hohe CPU-Auslastung aufweist, fahren Sie mit dem nächsten Schritt fort.

Schritt 4: Fügen Sie fehlende Indizes hinzu

Fehlende Indizes können zu langsamer ausgeführten Abfragen und einer hohen CPU-Auslastung führen. Sie können fehlende Indizes identifizieren und sie erstellen, um diese Auswirkungen auf die Leistung zu verbessern.

1. Führen Sie die folgende Abfrage aus, um Abfragen zu identifizieren, die eine hohe CPU-Auslastung verursachen und in deren Abfrageplan mindestens ein Index fehlt:

   -- Captures the Total CPU time spent by a query along with the query plan and total executions
   SELECT
       qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
       q.[text],
       p.query_plan,
       qs_cpu.execution_count,
       q.dbid,
       q.objectid,
       q.encrypted AS text_encrypted
   FROM
       (SELECT TOP 500 qs.plan_handle,
        qs.total_worker_time,
        qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu
   CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
   CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
   WHERE p.query_plan.exist('declare namespace 
           qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
           //qplan:MissingIndexes')=1
   

2. Überprüfen Sie die Ausführungspläne für die identifizierten Abfragen, und optimieren Sie die Abfrage, indem Sie die erforderlichen Änderungen vornehmen. Der folgende Screenshot zeigt ein Beispiel, in dem SQL Server auf einen fehlenden Index für Ihre Abfrage hinweist. Klicken Sie mit der rechten Maustaste auf den Teil des Abfrageplans für den fehlenden Index, und wählen Sie dann Details zum fehlenden Index aus, um den Index in einem anderen Fenster in SQL Server Management Studio zu erstellen.

   

3. Verwenden Sie die folgende Abfrage, um nach fehlenden Indizes zu suchen und alle empfohlenen Indizes anzuwenden, die hohe Werte für das Verbesserungsmaß aufweisen. Beginnen Sie mit den 5 bzw. 10 besten Empfehlungen aus der Ausgabe, für die die improvement_measure-Werte am höchsten sind. Diese Indizes haben die größten positiven Auswirkungen auf die Leistung. Entscheiden Sie, ob Sie diese Indizes anwenden möchten, und stellen Sie sicher, dass Leistungstests für die Anwendung durchgeführt werden. Setzen Sie dann ggf. weitere Empfehlungen zu fehlenden Indizes um, bis die Anwendung die gewünschten Leistungsergebnisse erzielt. Weitere Informationen zu diesem Thema finden Sie unter Optimieren nicht gruppierter Indizes mit Vorschlägen zu fehlenden Indizes.

   SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime,
       mig.index_group_handle,
       mid.index_handle,
       CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
       'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,
           '') + CASE WHEN mid.equality_columns IS NOT NULL
   AND mid.inequality_columns IS NOT NULL THEN ','
   ELSE ''
   END + ISNULL(mid.inequality_columns,
           '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')',
           '') AS create_index_statement,
       migs.*,
       mid.database_id,
       mid.[object_id]
   FROM sys.dm_db_missing_index_groups mig
   INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
   WHERE CONVERT (DECIMAL (28, 1),
                  migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
   ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
   

Schritt 5: Untersuchen und beheben Sie parameterabhängige Probleme

Sie können den Befehl DBCC FREEPROCCACHE verwenden, um den Plancache zu leeren, und überprüfen, ob dadurch das Problem der hohen CPU-Auslastung behoben wird. Wenn das Problem dadurch behoben ist, ist dies ein Hinweis auf ein parametersensitives Problem (PSP, auch bekannt als „Parameter-Sniffing-Problem“).

Zur Entschärfung der parametersensitiven Probleme können Sie die folgenden Methoden verwenden. Jede Methode ist mit Zielkonflikten/Kompromissen und Nachteilen verbunden.

• Verwenden Sie den RECOMPILE-Abfragehinweis. Sie können einer oder mehreren der Abfragen mit hoher CPU, die in Schritt 2 identifiziert wurden, einen RECOMPILE Abfragehinweis hinzufügen. Dieser Hinweis trägt dazu bei, den leichten Anstieg der CPU-Auslastung der Kompilierung durch eine stärker optimierte Leistung für jede Abfrageausführung auszugleichen. Weitere Informationen finden Sie unter Parameter- und Ausführungsplanwiederverwendung, Parametersensitivität und RECOMPILE-Abfragehinweis.

  Hier ist ein Beispiel dafür, wie Sie diesen Hinweis auf Ihre Abfrage anwenden können.

  SELECT * FROM Person.Person 
  WHERE LastName = 'Wood'
  OPTION (RECOMPILE)
  

• Verwenden Sie den OPTIMIZE FOR-Abfragehinweis, um den tatsächlichen Parameterwert mit einem häufiger vorkommenden Parameterwert zu überschreiben, der die meisten Werte in den Daten abdeckt. Diese Option erfordert ein vollständiges Verständnis der optimalen Parameterwerte und zugehörigen Planmerkmale. Hier ist ein Beispiel für die Verwendung dieses Hinweises in Ihrer Abfrage.

  DECLARE @LastName Name = 'Frintu'
  SELECT FirstName, LastName FROM Person.Person 
  WHERE LastName = @LastName
  OPTION (OPTIMIZE FOR (@LastName = 'Wood'))
  

• Verwenden Sie den OPTIMIZE FOR UNKNOWN-Abfragehinweis, um den tatsächlichen Parameterwert durch den Mittelwert des Dichtevektors zu ersetzen. Sie können dies auch tun, indem Sie die eingehenden Parameterwerte in lokalen Variablen erfassen und dann die lokalen Variablen innerhalb der Prädikate verwenden, anstatt die Parameter selbst zu verwenden. Für diesen Fix kann die durchschnittliche Dichte ausreichen, um eine akzeptable Leistung zu bieten.

• Verwenden Sie den DISABLE_PARAMETER_SNIFFING-Abfragehinweis, um Parameter Sniffing vollständig zu deaktivieren. Hier ist ein Beispiel für die Verwendung in einer Abfrage:

  SELECT * FROM Person.Address  
  WHERE City = 'SEATTLE' AND PostalCode = 98104
  OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
  

• Verwenden Sie den KEEPFIXED PLAN-Abfragehinweis, um erneute Kompilierungen im Cache zu verhindern. Bei dieser Problemumgehung wird davon ausgegangen, dass der allgemeine, „hinreichend gute“ Plan der Plan ist, der sich bereits im Cache befindet. Sie können auch automatische Statistikaktualisierungen deaktivieren, um die Wahrscheinlichkeit zu verringern, dass der gute Plan entfernt und ein neuer schlechter Plan kompiliert wird.

• Verwenden Sie den Befehl DBCC FREEPROCCACHE als temporäre Lösung, bis der Anwendungscode korrigiert ist. Sie können DBCC FREEPROCCACHE (plan_handle) verwenden, um nur denjenigen Plan zu entfernen, der das Problem verursacht. Um beispielsweise Abfragepläne zu finden, die auf die Person.Person-Tabelle in AdventureWorks verweisen, können Sie diese Abfrage verwenden, um den Abfragehandle zu finden. Anschließend können Sie den spezifischen Abfrageplan aus dem Cache freigeben, indem Sie den in der zweiten Spalte der Abfrageergebnisse erstellten DBCC FREEPROCCACHE (plan_handle) verwenden.

  SELECT text, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans
  CROSS APPLY sys.dm_exec_query_plan(plan_handle)
  CROSS APPLY sys.dm_exec_sql_text(plan_handle)
  WHERE text LIKE '%person.person%'
  

Schritt 6: Untersuchen und beheben Sie Probleme mit der SARGability

Ein Prädikat in einer Abfrage gilt als SARGable (Search ARGument-fähig), wenn SQL Server-Engine mithilfe einer Indexsuche die Ausführung der Abfrage beschleunigen kann. Viele Abfrageentwürfe verhindern SARGability und führen zu Tabellen- oder Indexscans sowie hoher CPU-Auslastung. Betrachten Sie die folgende Abfrage für die AdventureWorks-Datenbank, in der jede ProductNumber abgerufen werden muss und jeweils die Funktion SUBSTRING() darauf angewendet werden muss, bevor sie mit einem Zeichenfolgenliteralwert verglichen wird. Wie Sie sehen können, müssen Sie zuerst alle Zeilen der Tabelle abrufen und dann die Funktion anwenden, bevor Sie einen Vergleich durchführen können. Das Abrufen aller Zeilen aus der Tabelle bedeutet einen Tabellen- oder Indexscan, was zu einer höheren CPU-Auslastung führt.

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE SUBSTRING(ProductNumber, 0, 4) =  'HN-'

Das Anwenden einer beliebigen Funktion oder Berechnung auf die Spalte(n) im Suchprädikat macht die Abfrage im Allgemeinen nicht SARGable und führt zu einer höheren CPU-Auslastung. Lösungen umfassen normalerweise das Umschreiben der Abfragen auf kreative Weise, um sie sargierbar zu machen. Eine mögliche Lösung für dieses Beispiel ist diese Umschreibung, bei der die Funktion aus dem Abfrageprädikat entfernt wird, eine andere Spalte durchsucht wird und dieselben Ergebnisse erzielt werden:

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE Name LIKE  'Hex%'

Hier ist ein weiteres Beispiel, bei dem ein Vertriebsleiter 10 % Verkaufsprovision für große Bestellungen vergeben möchte und sehen möchte, für welche Aufträge die Provision mehr als 300 $ betragen wird. Hier ist die logische, aber nicht sargable Möglichkeit, dies zu tun.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300

Dies ist eine mögliche weniger intuitive, aber SARGierbare Neuschreibung der Abfrage, bei der die Berechnung auf die andere Seite des Prädikats verschoben wird.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice > 300/0.10

SARGierbarkeit gilt nicht nur für WHERE-Klauseln, sondern auch für JOINs-, HAVING-, GROUP BY- und ORDER BY-Klauseln. Häufig auftretende Fälle, wo SARGierbarkeit in Abfragen verhindert wird, betreffen die Funktionen CONVERT(), CAST(), ISNULL(), COALESCE(), wenn sie in WHERE- oder JOIN-Klauseln verwendet werden, was zum Durchsuchen von Spalten führt. In den Fällen, in denen es um die Konvertierung von Datentypen geht (CONVERT oder CAST), kann die Lösung darin bestehen, sicherzustellen, dass Sie dieselben Datentypen vergleichen. Hier ist ein Beispiel, wo die T1.ProdID Spalte explizit in den INT Datentyp in einem JOIN konvertiert wird. Die Konvertierung verhindert die Verwendung eines Indexes in der Verknüpfungsspalte. Das gleiche Problem tritt bei der impliziten Konvertierung auf, bei der die Datentypen unterschiedlich sind und SQL Server einen von ihnen konvertiert, um die Verknüpfung auszuführen.

SELECT T1.ProdID, T1.ProdDesc
FROM T1 JOIN T2 
ON CONVERT(int, T1.ProdID) = T2.ProductID
WHERE t2.ProductID BETWEEN 200 AND 300

Um eine Durchsuchung der Tabelle T1 zu vermeiden, können Sie den zugrundeliegenden Datentyp der Spalte ProdID nach entsprechender Planung und Gestaltung ändern und dann die beiden Spalten ohne Verwendung der Konvertierungsfunktion ON T1.ProdID = T2.ProductID verknüpfen.

Eine andere Lösung besteht darin, in T1 eine berechnete Spalte zu erstellen, in der die gleiche CONVERT()-Funktion verwendet wird, und dann einen Index dafür zu erstellen. Dadurch kann der Abfrageoptimierer diesen Index verwenden, ohne dass Sie die Abfrage ändern müssen.

ALTER TABLE dbo.T1  ADD IntProdID AS CONVERT (INT, ProdID);
CREATE INDEX IndProdID_int ON dbo.T1 (IntProdID);

In einigen Fällen können Abfragen nicht einfach umgeschrieben werden, um SARGability zu ermöglichen. Überprüfen Sie in diesen Fällen, ob eine berechnete Spalte mit einem zugehörigen Index dazu beitragen kann. Sonst müssen Sie halt die Abfrage so beibehalten, wie sie war, aber in dem Bewusstsein, dass sie zu Szenarien mit höherer CPU führen kann.

Schritt 7: Deaktivieren Sie ausführliches Tracing

Suchen Sie nach SQL Trace oder XEvent-Ablaufverfolgung, die sich auf die Leistung von SQL Server auswirkt und eine hohe CPU-Auslastung verursacht. Die Verwendung der folgenden Ereignisse kann z. B. zu einer hohen CPU-Auslastung führen, wenn Sie eine hohe SQL Server-Aktivität nachverfolgen:

• Abfrageplan XML-Ereignisse (query_plan_profile, query_post_compilation_showplan, query_post_execution_plan_profile, query_post_execution_showplan, query_pre_execution_showplan)
• Anweisungsereignisse (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
• Anmelde- und Abmeldeereignisse (login, process_login_finish, login_event, logout)
• Sperrereignisse (lock_acquired, lock_cancel, lock_released)
• Warteereignisse (wait_info, wait_info_external)
• SQL-Überwachungsereignisse (abhängig von der überwachten Gruppe und SQL Server-Aktivitäten in dieser Gruppe)

Führen Sie die folgenden Abfragen aus, um aktive XEvent- oder Serverablaufverfolgungen zu identifizieren:

PRINT '--Profiler trace summary--'
SELECT traceid, property, CONVERT(VARCHAR(1024), value) AS value FROM::fn_trace_getinfo(
    default)
GO
PRINT '--Trace event details--'
SELECT trace_id,
    status,
    CASE WHEN row_number = 1 THEN path ELSE NULL end AS path,
    CASE WHEN row_number = 1 THEN max_size ELSE NULL end AS max_size,
    CASE WHEN row_number = 1 THEN start_time ELSE NULL end AS start_time,
    CASE WHEN row_number = 1 THEN stop_time ELSE NULL end AS stop_time,
    max_files,
    is_rowset,
    is_rollover,
    is_shutdown,
    is_default,
    buffer_count,
    buffer_size,
    last_event_time,
    event_count,
    trace_event_id,
    trace_event_name,
    trace_column_id,
    trace_column_name,
    expensive_event
FROM
    (SELECT t.id AS trace_id,
     row_number() over(PARTITION BY t.id order by te.trace_event_id, tc.trace_column_id) AS row_number,
     t.status,
     t.path,
     t.max_size,
     t.start_time,
     t.stop_time,
     t.max_files,
     t.is_rowset,
     t.is_rollover,
     t.is_shutdown,
     t.is_default,
     t.buffer_count,
     t.buffer_size,
     t.last_event_time,
     t.event_count,
     te.trace_event_id,
     te.name AS trace_event_name,
     tc.trace_column_id,
     tc.name AS trace_column_name,
     CASE WHEN te.trace_event_id in (23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) THEN CAST(1 as bit) ELSE CAST(0 AS BIT) END AS expensive_event FROM sys.traces t CROSS APPLY::fn_trace_geteventinfo(t.id) AS e JOIN sys.trace_events te ON te.trace_event_id = e.eventid JOIN sys.trace_columns tc ON e.columnid = trace_column_id) AS x
GO
PRINT '--XEvent Session Details--'
SELECT sess.NAME 'session_name', event_name, xe_event_name, trace_event_id,
    CASE WHEN xemap.trace_event_id IN(23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) 
    THEN Cast(1 AS BIT)
ELSE Cast(0 AS BIT)
END AS expensive_event
FROM sys.dm_xe_sessions sess
JOIN sys.dm_xe_session_events evt
ON sess.address = evt.event_session_address
INNER JOIN sys.trace_xe_event_map xemap
ON evt.event_name = xemap.xe_event_name
GO

Schritt 8: Hohe CPU-Auslastung durch Spinlock-Contention beheben

Informationen zum Beheben allgemeiner CPU-Auslastung durch Spinlock-Konkurrenz finden Sie in den folgenden Abschnitten.

SOS_CACHESTORE spinlock contention

Wenn ihre SQL Server Instanz starke SOS_CACHESTORE Spinlockkonflikte aufweist oder Sie feststellen, dass Ihre Abfragepläne häufig bei ungeplanten Abfrageworkloads entfernt werden, lesen Sie den folgenden Artikel und aktivieren Sie das Traceflag T174 mithilfe des DBCC TRACEON (174, -1) Befehls:

FIX: SOS_CACHESTORE Spinlock-Konflikt im Ad-hoc-Plancache von SQL Server verursacht eine hohe CPU-Auslastung in SQL Server.

Wenn der Zustand mit hoher CPU-Auslastung durch die Verwendung von T174 behoben werden kann, aktivieren Sie dies als Startparameter mit dem Konfigurations-Manager von SQL Server.

Zufällig hohe CPU-Auslastung aufgrund von SOS_BLOCKALLOCPARTIALLIST-Spinlock-Contention auf Systemen mit großem Arbeitsspeicher

Auf Großspeichercomputern SOS_BLOCKALLOCPARTIALLIST kann spinlock-Contention zufällige Spitzen bei der CPU-Auslastung erzeugen. Führen Sie die folgenden Schritte aus, um die Sperrkonkurrenz auf der einzelnen serverweiten Liste für Teilblockzuweisungen zu reduzieren, indem Sie sie in mehrere Listen partitionieren.

1. Bestätigen Sie die Beanstandung. Prüfen Sie sys.dm_os_spinlock_stats auf erhöhte collisions- und spins-Werte auf dem SOS_BLOCKALLOCPARTIALLIST-Spinlock. Wenn der Ressourcenkonflikt mit den CPU-Spitzen korreliert, fahren Sie mit den folgenden Schritten fort.

2. Mildern Sie die CPU-Spitzen vorübergehend ab. DBCC DROPCLEANBUFFERS auszuführen gibt die Liste der teilweisen Blockzuweisungen frei und bietet vorübergehende Abhilfe, während Sie den dauerhaften Fix planen.

3. Stellen Sie sicher, dass Ihr SQL Server Build den Fix enthält. Das Partitionierungsverhalten wird über Traceflags 8142 und 8145 bereitgestellt und wurde erstmals im SQL Server 2019 Kumulatives Update 21 eingeführt, um Fehler 2410400 zu beheben. Wenn Sie einen älteren Build verwenden, wenden Sie das neueste kumulative Update für Ihre Version an, bevor Sie die Traceflags aktivieren.

4. Aktivieren Sie Traceflag 8142. Aktivieren Sie den Traceflag 8142 als globalen Startparameter. Dieses Traceflag partitioniert die Spinlock-geschützte Liste nach CPU, bis zu 64 Partitionen, die in der Regel ausreichen, um die Kontention zu beseitigen.

5. Wenn der Konflikt weiterhin besteht, aktivieren Sie das Traceflag 8145. Auf Systemen, auf denen 64 CPU-Partitionen nicht ausreichen, aktivieren Sie zusätzlich das Ablaufverfolgungsflag 8145 als globalen Startparameter. Das Ablaufverfolgungsflag 8145 ändert die durch Ablaufverfolgungsflag 8142 aktivierte Partitionierung pro Soft-NUMA-Knoten statt pro CPU. Es hat keine Auswirkung, es sei denn, das Traceflag 8142 ist ebenfalls aktiviert.

6. Starten Sie den SQL Server-Dienst neu, damit die Start-Traceflags wirksam werden, und überprüfen Sie sys.dm_os_spinlock_stats erneut, ob die Ressourcenkonkurrenz zurückgegangen ist und die CPU-Auslastung sich stabilisiert hat.

Hohe CPU-Auslastung aufgrund der Spinlock-Contention auf XVB_list auf High-End-Computern

Wenn Ihre SQL Server-Instanz ein Szenario mit hoher CPU-Auslastung aufweist, das durch Spinlockkonflikt auf dem XVB_LIST-Spinlock auf Hochkonfigurationscomputern verursacht wird (High-End-Systeme mit einer großen Anzahl von Prozessoren der neueren Generation (CPUs)), aktivieren Sie das Traceflag TF8102 zusammen mit TF8101.

Schritt 9: Überprüfen Ihrer Energieplaneinstellungen auf Betriebssystemebene

SQL Server-Workloads können eine geringere Leistung aufweisen und eine hohe CPU-Auslastung auf dem System verursachen, wenn Windows mit dem standardmäßigen Ausgewogenen Energieplan konfiguriert ist. Die Einstellung des ausgewogenen Energieplans kann die CPU-Taktgeschwindigkeit verringern, um Energie zu sparen. Beispielsweise kann ein Prozessor mit 3,00 GHz auf 1,2 GHz gedrosselt werden. Daher erreichen Workloads, die in der Regel etwa 30% CPU verbrauchen, aufgrund der reduzierten Taktgeschwindigkeit 100% Auslastung. Um eine konsistente und optimale Leistung für rechenintensive SQL Server Workloads zu gewährleisten, konfigurieren Sie das System für die Verwendung des High Performance-Energiesparplans. Diese Einstellung stellt sicher, dass die CPU mit der vollen Nenngeschwindigkeit arbeitet, was dazu beiträgt, Leistungsengpässe zu vermeiden. Weitere Informationen finden Sie unter Langsame Leistung unter Windows Server bei Verwendung des Energiesparplans "Ausgeglichen".

Schritt 10: Konfigurieren Des virtuellen Computers

Wenn Sie eine virtuelle Maschine verwenden, stellen Sie sicher, dass Sie ihr nicht zu viele CPUs zuweisen und dass sie korrekt konfiguriert ist. Weitere Informationen finden Sie unter Beheben von Leistungsproblemen mit virtuellen ESX-/ESXi-Computern (2001003).

Schritt 11: Skalieren des Systems zur Verwendung weiterer CPUs

Wenn einzelne Abfrageinstanzen wenig CPU-Kapazität verwenden, aber die Gesamtauslastung aller Abfragen zusammen einen hohen CPU-Verbrauch verursacht, erwägen Sie die Skalierung Ihres Computers, indem Sie weitere CPUs hinzufügen. Verwenden Sie die folgende Abfrage, um die Anzahl der Abfragen zu ermitteln, die einen bestimmten Schwellenwert für den Durchschnitt und die maximale CPU-Auslastung pro Ausführung überschreiten und häufig auf dem System ausgeführt werden. Stellen Sie sicher, dass Sie die Werte der beiden Variablen so ändern, dass sie Ihrer Umgebung entsprechen:

-- Shows queries where Max and average CPU time exceeds 200 ms and executed more than 1000 times
DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000
SELECT qs.total_worker_time/1000 total_cpu_time_ms,
       qs.max_worker_time/1000 max_cpu_time_ms,
       (qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
       qs.execution_count,
       q.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec
        OR qs.max_worker_time > @cputime_threshold_microsec )
        AND execution_count > @execution_count
ORDER BY  qs.total_worker_time DESC 

Verwandte Inhalte

• Hohe CPU- oder Speicherzuteilungen können bei Abfragen auftreten, die eine optimierte geschachtelte Schleife oder Batchsortierung verwenden.
• Empfohlene Updates und Konfigurationsoptionen für SQL Server 2017 und 2016 mit leistungsstarken Workloads
• Leistung überwachen und optimieren
• Bewährte Methoden mit dem Abfragespeicher