SQL Server での高 CPU 使用率の問題のトラブルシューティング

適用対象: SQL Server

まとめ

Microsoft SQL Server の CPU 使用率が高い原因（sqlservr.exe プロセスが過剰なプロセッサ時間を消費している状態）は、一般的に、非効率的なクエリ、インデックスの不足、古い統計、パラメーターに依存するプラン、またはワークロードの増加です。この記事では、順序付けされたトラブルシューティング手順について説明し、最初にSQL Serverが CPU 負荷の原因であることを確認してから、CPU 消費の上位のクエリを特定します。次に、統計の更新、不足しているインデックスの追加、パラメータースニッフィングと SARGability の問題の解決、重いトレースの無効化、スピンロックの競合の軽減など、対象となる修正について説明します。最後に、Windows電源プランを含むオペレーティングシステムと仮想マシンのチューニングと、より多くの CPU にスケールアップするタイミングに関するガイダンスについて説明します。これらの手順を実行して、持続的な CPU 使用率を低減し、クエリのパフォーマンスを復元し、ハードウェアのスケールアップが必要なタイミングを判断します。

SQL Serverで CPU 使用率が高くなる一般的な原因

SQL Serverの CPU 使用率が高い場合、多くの原因が考えられますが、最も一般的な原因は次のとおりです。

次の条件により、テーブルまたはインデックスのスキャンが原因で発生する論理読み取りの増加:
- 古い統計情報
- 見つからないインデックス
- パラメーター依存プラン (PSP) に関する問題
- 不適切に設計されたクエリ
ワークロードの増加

SQL Server の CPU 使用率が高い問題をトラブルシューティングするには、次の手順に従います。

手順 1: CPU 使用率が高くなっている原因が SQL Server であることを確認する

次のいずれかのツールを使用して、SQL Server プロセスが実際に高い CPU 使用率の一因となっているかどうかを確認します。

タスクマネージャー: [Process] タブで、SQL Server Windows NT-64 Bit の CPU 列の値が 100% に近いかどうかを確認します。
パフォーマンスとリソースモニター (perfmon)
- カウンター: Process/%User Time、% Privileged Time
- インスタンス: sqlservr
次の PowerShell スクリプトを使用して、60 秒間にわたってカウンターデータを収集できます。
```
$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
    }
}
```
% User Timeが一貫して 90% を超える場合 (% ユーザー時間は各プロセッサのプロセッサ時間の合計であり、最大値は 100% * (CPU の数) です)、SQL Server プロセスによって CPU 使用率が高くなります。ただし、% Privileged time が常に 90% を超えている場合、CPU 使用率が高くなっている原因はウイルス対策ソフトウェア、他のドライバー、またはコンピューター上の別の OS コンポーネントが原因です。システム管理者と協力して、この動作の根本原因を分析してください。
パフォーマンスダッシュボード: SQL Server Management Studioで、<SQLServerInstance> を右クリックし、[レポート>標準レポート>パフォーマンスダッシュボード] を選択します。

ダッシュボードには、System CPU Utilizationというタイトルのグラフが棒グラフで表示されています。濃い色は SQL Server エンジンの CPU 使用率を示し、明るい色はオペレーティングシステムの全体的な CPU 使用率を表します (グラフの凡例を参照)。円形の更新ボタンまたは F5 を選択して、更新された使用率を確認します。

手順 2: CPU 使用率を高くしているクエリを特定する

Sqlservr.exe プロセスによって CPU 使用率が大幅に高くなっている場合、最も一般的な理由は、テーブルまたはインデックスのスキャンを実行する SQL Server クエリの後に、並べ替えとハッシュの操作とループ (入れ子になったループ演算子または WHILE (T-SQL)) です。クエリが現在使用している CPU が、CPU 全体の処理能力に対してどの程度かを確認するには、次のステートメントを実行します。

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

現在、高い CPU アクティビティの原因となっているクエリを特定するために、次のステートメントを実行してください。

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

現時点でクエリが CPU 使用率を上げていない場合は、以下のステートメントを実行して、過去に CPU 負荷が高かったクエリを検索することができます。

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

手順 3: 統計を更新する

CPU を最も消費するクエリを特定したら、これらのクエリで使用されるテーブルの統計を更新します。 sp_updatestats システムストアドプロシージャを使用して、現在のデータベース内のすべてのユーザー定義テーブルと内部テーブルの統計を更新します。例えば：

exec sp_updatestats

注

sp_updatestats システムストアドプロシージャは、現在のデータベース内のすべてのユーザー定義テーブルと内部テーブルに対して UPDATE STATISTICS を実行します。定期的なメンテナンスの場合は、スケジュール済みのメンテナンスによって統計が最新の状態に保たれるようにします。 Adaptive Index Defrag のようなソリューションを活用し、1 個以上のデータベースに対するインデックスの最適化と統計更新を自動管理します。この手順では、断片化レベルに従ってインデックスを再構築または再構成するかどうかを自動的に選択し、線形しきい値を使用して統計を更新します。

sp_updatestats の詳細については、sp_updatestats を参照してください。

SQL Server が引き続き CPU を過剰に使用している場合は、次の手順に進みます。

手順 4: 不足しているインデックスを追加する

インデックスがないと、クエリの実行速度が遅くなったり、CPU の使用率が高くなったりすることがあります。このパフォーマンスへの影響を軽減するために、不足しているインデックスを特定し、作成することができます。

以下のクエリを実行して、CPU 使用率が高く、クエリプランに少なくとも 1 つの不足しているインデックスが含まれるクエリを特定します。

-- 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

特定されたクエリについての実行プランを確認し、必要な変更を加えることでクエリを調整します。次のスクリーンショットは、SQL Server がクエリに不足しているインデックスを指摘する例を示しています。クエリプランの不足しているインデックス部分を右クリックし、[不足しているインデックスの詳細] を選択して、SQL Server Management Studio の別のウィンドウでインデックスを作成します。

以下のクエリを使用して、欠落しているインデックスを確認し、改善指標値が高い推奨インデックスを適用してください。 improvement_measure値が最も高い出力の上位 5 または 10 の推奨事項から開始します。これらのインデックスは、パフォーマンスに最も大きなプラスの影響を与えます。これらのインデックスを適用するかどうかを決定し、アプリケーションに対してパフォーマンステストが行われるようにします。次に、目的のアプリケーションパフォーマンスの結果が得られるまで、インデックス不足に関する推奨事項を引き続き適用します。このトピックの詳細については、「不足インデックス候補を使用した非クラスター化インデックスのチューニング」を参照してください。

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

手順 5: パラメーターに依存する問題を調べて解決する

DBCC FREEPROCCACHE コマンドを使用してプランキャッシュを解放し、これが CPU 使用率の高い問題を解決するかどうかを確認できます。その現象が解消する場合は、パラメーターに依存する問題 (PSP、「パラメータースニッフィングの問題」とも呼ばれる) を示します。

注

パラメーターを指定せずに DBCC FREEPROCCACHE を使用すると、コンパイルされたすべてのプランがプランキャッシュから削除されます。これにより、新しいクエリの実行が再度コンパイルされ、新しいクエリごとに初回のみ実行時間が長くなります。最善の方法は、問題の原因となっている可能性があるクエリを特定するためにDBCC FREEPROCCACHE ( plan_handle | sql_handle )を使用し、その個々のクエリまたは複数のクエリに対処することです。

パラメータに依存する問題を緩和するために、以下の方法を使用します。各手順には、トレードオフと欠点があります。

RECOMPILE クエリヒントを使用します。手順 2 で特定された 1 つ以上の高 CPU クエリに RECOMPILE クエリヒントを追加できます。このヒントは、コンパイル時の CPU 使用率のわずかな上昇と、各クエリ実行時の最適なパフォーマンスのバランスをとるのに役立ちます。詳細については、「パラメーターと実行プランの再利用、パラメーターの感度、および RECOMPILE クエリヒント」を参照してください。

このヒントをクエリに適用する例を以下に示します。
```
SELECT * FROM Person.Person 
WHERE LastName = 'Wood'
OPTION (RECOMPILE)
```
クエリヒント OPTIMIZE FOR を使用して、実際のパラメータ値ではなく、データ内のほとんどの値をカバーする、より一般的なパラメータ値を優先して使用します。このオプションでは、最適なパラメーター値と関連するプランの特性を完全に理解する必要があります。このヒントをクエリに適用する例を以下に示します。
```
DECLARE @LastName Name = 'Frintu'
SELECT FirstName, LastName FROM Person.Person 
WHERE LastName = @LastName
OPTION (OPTIMIZE FOR (@LastName = 'Wood'))
```
クエリヒント OPTIMIZE FOR UNKNOWN を使用して、実際のパラメータ値を密度ベクトルの平均値で上書きします。また、入力されるパラメータの値をローカル変数に取り込み、パラメータそのものを使うのではなく、述語の中でローカル変数を使うという方法もあります。この修正では、許容できるパフォーマンスを提供するのに平均密度で十分な場合があります。
DISABLE_PARAMETER_SNIFFINGクエリヒントを使用して、パラメータースニッフィングを完全に無効にします。クエリで使用する方法の例を次に示します。
```
SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
```
KEEPFIXED PLAN クエリヒントを使用して、キャッシュ内の再コンパイルを防止します。この回避策は、「十分な」共通プランが既にキャッシュにあることを前提としています。よいプランが破棄されて新しい悪いプランがコンパイルされる可能性を減らすため、統計の自動更新を無効にすることもできます。
アプリケーションコードが修正されるまで、 DBCC FREEPROCCACHE コマンドを一時的なソリューションとして使用します。 DBCC FREEPROCCACHE (plan_handle)コマンドを使用すると、問題の原因となっているプランのみを削除できます。たとえば、AdventureWorks で Person.Person テーブルを参照するクエリプランを検索するには、このクエリを使用してクエリハンドルを見つけることができます。そして、クエリ結果の 2 列目に生成されるDBCC FREEPROCCACHE (plan_handle)を使用して、特定のクエリプランをキャッシュから解放することができます。
```
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%'
```

手順 6: SARGability の問題を調べて解決する

SQL Server エンジンがインデックスシークを使用してクエリの実行を高速化できる場合、クエリ内の述語は SARGable (Search ARGument-able) と見なされます。多くのクエリ設計では SARGability を妨げ、テーブルまたはインデックスのスキャンや CPU 使用率が高くなります。すべてのProductNumberを取得し、SUBSTRING()関数を適用してから文字列リテラル値と比較する必要がある、AdventureWorks データベースに対する次のクエリを考えてみましょう。ご覧のように、比較を行うには、まずテーブルのすべての行をフェッチしてから、関数を適用する必要があります。テーブルからすべての行をフェッチすると、テーブルまたはインデックスのスキャンが行われることになり、CPU 使用率が高くなります。

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

通常、検索述語の列に関数や計算を適用すると、クエリが非SARG可能になり、CPU 消費量が増加します。通常、ソリューションには、クエリを SARGable にするために創造的な方法でクエリを書き換えることが関係しています。この例に対して考えられる解決策は、この書き換えです。ここでは、関数がクエリ述語から削除され、別の列が検索され、同じ結果が得られます。

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

別の例として、セールスマネジャーが大口注文に対して 10％の販売手数料を与えたいと考え、どの注文が 300 ドル以上の手数料になるかを確認したいとします。論理的ではありますが、SARG可能ではない方法はこれです。

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

以下は、直感的ではないが SARG 可能なクエリの書き換えで、述語の反対側に計算を移動させたものである。

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

SARGability はWHERE句だけでなく、JOINs、HAVING、GROUP BY、ORDER BY句にも適用されます。クエリーで SARGability が妨げられる事象としてよく見られるのは、WHERE または JOIN 句で使用される CONVERT()、CAST()、ISNULL()、COALESCE() 関数が列のスキャンにつながる場合です。データ型変換の場合 (CONVERT または CAST) では、同じデータ型を比較することが解決策になる場合があります。以下は、T1.ProdIDカラムをJOINで明示的にINTデータ型に変換した例です。この変換では、結合カラムのインデックスを利用できなくします。データ型が異なり、SQL Server がそのうちの 1 つを変換して結合を実行する暗黙の変換でも、同じ問題が発生します。

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

T1テーブルのスキャンを回避するには、適切な計画と設計の後にProdID列の基礎となるデータ型を変更し、変換関数ON T1.ProdID = T2.ProductIDを使用せずに 2 つの列を結合することができます。

もう 1 つの解決策は、同じ CONVERT() 関数を使用する計算列を T1 に作成し、その列にインデックスを作成することです。これにより、クエリオプティマイザーは、クエリを変更することなく、そのインデックスを使用することができます。

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

場合によっては、SARGabilityを実現できるように、クエリを簡単に書き換えることができません。そのような場合は、インデックスを付けた計算列が役に立つかどうかを確認するか、CPUの高いシナリオにつながる可能性があることを意識したうえで、クエリを元のままにしておきます。

手順 7: 高負荷のトレースを無効にする

SQL Serverのパフォーマンスに影響を与え、CPU 使用率が高くなる SQL トレースまたは XEvent トレースを確認してください。たとえば、次のイベントを使用すると、SQL Server の高負荷なアクティビティをトレースすると CPU 使用率が高くなる可能性があります。

クエリプラン XML イベント (query_plan_profile、query_post_compilation_showplan、query_post_execution_plan_profile、query_post_execution_showplan、query_pre_execution_showplan)
ステートメントレベルのイベント (sql_statement_completed、sql_statement_starting、sp_statement_starting、sp_statement_completed)
ログインイベントとログアウトイベント (login、process_login_finish、login_event、logout)
ロックイベント (lock_acquired、lock_cancel、lock_released)
待機イベント (wait_info、wait_info_external)
SQL 監査イベント (監査対象のグループと、そのグループの SQL Server アクティビティに応じて)

次のクエリを実行して、アクティブな XEvent またはサーバートレースを識別します。

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

手順 8: スピンロックの競合によって発生する CPU 使用率が高くなる問題を解決する

スピンロックの競合によって発生する一般的な CPU 使用率の高さを解決するには、次のセクションを参照してください。

SOS_CACHESTORE スピンロックの競合

SQL Server インスタンスでSOS_CACHESTOREスピンロックの競合が大きい場合や、計画されていないクエリワークロードでクエリプランが削除されることが多い場合は、次の記事を参照し、コマンドを使用してトレースフラグを有効にします。

修正: アドホック SQL Server プランキャッシュでの SOS_CACHESTORE スピンロック競合が原因で発生する SQL Server の高い CPU 使用率の問題。

T174 を使用することで、高い CPU 状態が解決される場合は、SQL Server 構成マネージャーを使用してスタートアップパラメーターとして有効にします。

大容量メモリマシンでの SOS_BLOCKALLOCPARTIALLIST スピンロック競合によるランダムな高 CPU 使用率

大規模なメモリマシンでは、 SOS_BLOCKALLOCPARTIALLIST スピンロックの競合により、CPU 使用率がランダムに急増する可能性があります。単一のサーバー全体にわたる部分ブロック割り当てリストを複数のリストにパーティション分割して、その競合を減らすには、次の手順を使用します。

主張を確認します。 SOS_BLOCKALLOCPARTIALLISTスピンロック上の高いcollisions値とspins値についてsys.dm_os_spinlock_statsを確認します。競合が CPU スパイクと関連している場合は、次の手順を実行します。
CPU スパイクを一時的に軽減します。 DBCC DROPCLEANBUFFERSを実行すると、部分ブロック割り当てリストが解放され、恒久的な修正の計画中に一時的な軽減策が提供されます。
SQL Server ビルドに修正プログラムが含まれていることを確認してください。 パーティション分割の動作は、トレースフラグ 8142 と 8145 を通じて提供され、SQL Server 2019 累積更新プログラム 21 で、バグ 2410400 に対処するために最初に導入されました。古いビルドを使用している場合は、トレースフラグを有効にする前に、お使いのバージョン向けの最新の累積更新プログラムを適用してください。
トレースフラグ 8142 を有効にします。 トレースフラグ 8142 をグローバルスタートアップパラメーターとして有効にします。このトレースフラグは、スピンロックで保護されたリストを CPU でパーティション分割します。最大 64 個のパーティションが、通常は競合を排除するのに十分です。
競合が解決しない場合は、トレースフラグ 8145 を有効にします。 64 個の CPU パーティションで十分でないシステムでは、さらにトレースフラグ 8145 をグローバルスタートアップパラメーターとして有効にしてください。トレースフラグ 8145 は、CPU ごとではなく、ソフト NUMA ノードごとにトレースフラグ 8142 によって有効になっているパーティション分割を変更します。トレースフラグ 8142 も有効になっていない限り、効果はありません。
SQL Server サービスを再起動してスタートアップトレースフラグを有効にしてから、sys.dm_os_spinlock_statsを再確認して、競合が減少し、CPU 使用率が安定していることを確認してください。

ハイエンドコンピューターでのXVB_listでのスピンロックの競合による CPU 使用率が高い

SQL Server インスタンスで、高性能マシン (新しい世代のプロセッサ (CPU) が多数搭載されたハイエンドシステム) の XVB_LIST スピンロックでの競合によって CPU 使用率が高い状態が発生する場合は、トレースフラグ TF8102 を TF8101 と共に有効にします。

注

高い CPU 使用率は、他の多くのスピンロックの種類でのスピンロック競合に起因する可能性があります。スピンロックの詳細については、「SQL Server でのスピンロック競合の診断と解決」を参照してください。

手順 9: OS レベルで電源プランの設定を確認する

Windows が既定のバランス電源プランに設定されている場合、SQL Server のワークロードでパフォーマンスが低下し、システムの CPU 使用率が高くなる可能性があります。 バランス型電源プランの設定では、CPU クロック速度が低下してエネルギーが節約される場合があります。たとえば、3.00 GHz で評価されたプロセッサは、1.2 GHzまでクロックを下げる可能性があります。その結果、通常 CPU を約 30% 使用するワークロードは、クロック速度が低下するため、100% 使用率に達する可能性があります。コンピューティング集中型のSQL Server ワークロードに対して一貫した最適なパフォーマンスを維持するには、高パフォーマンスの電源プランを使用するようにシステムを構成します。この設定により、CPU が完全に評価された速度で動作し、パフォーマンスのボトルネックを回避できます。詳細については、「バランス型電源プランを使用する場合の Windows Server でのパフォーマンスの低下」を参照してください。

手順 10: 仮想マシンを構成する

仮想マシンを使用している場合は、CPU の割り当てが過剰になっておらず、適切に設定されていることを確認します。詳細については、「 ESX/ESXi 仮想マシンのパフォーマンスに関する問題 (2001003) のトラブルシューティング」を参照してください。

手順 11: より多くの CPU を使用するようにシステムをスケールアップする

個々のクエリインスタンスで使用される CPU 容量が少なく、すべてのクエリの全体的なワークロードによって CPU 消費量が高くなる場合は、CPU を追加してコンピューターをスケールアップすることを検討してください。次のクエリを使用して、実行ごとの平均および最大 CPU 消費量が特定のしきい値を超え、システム上で何度も実行されるクエリの数を検索します。環境に合わせて 2 つの変数の値を必ず変更してください。

-- 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

フィードバック

このページはお役に立ちましたか?

最終更新: 2026-08-13