SQL Server で CPU を大量に使用する問題のトラブルシューティング

[アーティクル]
02/08/2024

適用対象: SQL Server

この記事では、Microsoft SQL Server を実行しているコンピューターの CPU 使用率が高いために発生する問題を診断して修正する手順について説明します。 SQL Server で発生する CPU 使用率の高さの原因には様々なものが考えられますが、以下のものが代表的な原因です。

以下の条件により、テーブルスキャンまたはインデックススキャンによって発生する高論理リード。
- 過去の統計データ
- インデックスの欠落
- パラメーターセンシティブプラン（PSP）の問題
- 不適切に設計されたクエリ
ワークロードの増加

SQL Server で CPU を大量に使用する問題のトラブルシューティングは、以下の手順で行うことができます。

手順 1: SQL Server が高い CPU 使用率を引き起こしていることを確認する。

以下のツールのいずれかを使用して、SQL Server プロセスが実際に高い CPU 使用率に寄与しているかどうかを確認します。

タスクマネージャー: [プロセス] タブで、SQL Server Windows NT-64 ビットの 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
    }
}

が一貫して 90% を超える場合 % User Time (% ユーザー時間は各プロセッサのプロセッサ時間の合計であり、最大値は 100% * (CPU なし))、SQL Server プロセスによって CPU 使用率が高くなります。しかし、% Privileged timeが常に 90% 以上である場合、ウイルス対策ソフトウェア、他のドライバー、またはコンピュータ上の他の OS コンポーネントが、高い CPU 使用率の原因となっている可能性があります。システム管理者と協力して、この動作の根本原因を分析する必要があります。

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

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

RECOMPILE クエリヒントを使用します。手順 2 でRECOMPILE識別される 1 つ以上の高 CPU クエリにクエリヒントを追加できます。このヒントは、コンパイル時の 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-'

検索述語の列に関数または計算を適用すると、一般にクエリは非検索可能になり、CPU の消費量が多くなります。解決策としては、SARGable にするために、クエリを工夫して書き換えることが一般的です。この例に対する解決策としては、このように関数をクエリ述語から削除し、別の列を検索して、同じ結果を得るという書き換えが考えられる。

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

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

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）
SQ 監査イベント (監査対象のグループとそのグループ内の SQL Server アクティビティに応じて)

以下のクエリーを実行し、アクティブな XEvent または Serverトレースを特定します。

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場合や、計画外のクエリワークロードでクエリプランが削除されることが多い場合は、次の記事を参照し、コマンドを使用してトレースフラグT174をDBCC TRACEON (174, -1)有効にします。

修正：アドホック SQL Server プランキャッシュの SOS_CACHESTORE スピンロック競合により、SQL Server の CPU 使用率が高くなります。

T174を使用してCPUが高い状態が解消された場合、SQL Server Configuration Managerを使用してスタートアップパラメーターとして有効にしてください。

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

SQL Server インスタンスでスピンロックの競合によって CPU 使用率がランダムにSOS_BLOCKALLOCPARTIALLIST高くなる場合は、2019 年SQL Server累積的な更新プログラム 21 を適用することをお勧めします。この問題を解決する方法の詳細については、一時的な軽減策を提供するバグリファレンス 2410400 と DBCC DROPCLEANBUFFERS を参照してください。

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

SQL Server インスタンスで、高構成マシン (新しい世代のプロセッサ (CPU) の数が多いハイエンドシステム) でのXVB_LISTスピンロックの競合によって CPU が高いシナリオが発生する場合は、TF8101 と共にトレースフラグ TF8102 を有効にします。

注:

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

手順 9：仮想マシンの設定

仮想マシンを使用している場合、CPU を過剰にプロビジョニングしていないか、正しく設定されているかを確認してください。詳細については、「 ESX/ESXi 仮想マシンのパフォーマンスに関する問題（2001003）のトラブルシューティング」を参照してください。

手順 10：より多くの 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

SQL Server で CPU を大量に使用する問題のトラブルシューティング

手順 1: SQL Server が高い CPU 使用率を引き起こしていることを確認する。

手順 2：CPU 使用率に影響するクエリを特定する

手順 3：統計を更新する

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

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

手順 6：SARGability の問題を調査して解決する

手順 7：重いトレースを無効にする

手順 8: スピンロックの競合によって発生する CPU 使用率の高い問題を修正する

SOS_CACHESTOREスピンロックの競合

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

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

手順 9：仮想マシンの設定

手順 10：より多くの CPU を使用するためのシステムのスケールアップ

関連項目

フィードバック

フィードバック

その他のリソース

SQL Server で CPU を大量に使用する問題のトラブルシューティング

手順 1: SQL Server が高い CPU 使用率を引き起こしていることを確認する。

手順 2：CPU 使用率に影響するクエリを特定する

手順 3：統計を更新する

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

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

手順 6：SARGability の問題を調査して解決する

手順 7：重いトレースを無効にする

手順 8: スピンロックの競合によって発生する CPU 使用率の高い問題を修正する

SOS_CACHESTOREスピンロックの競合

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

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

手順 9：仮想マシンの設定

手順 10：より多くの CPU を使用するためのシステムのスケールアップ

関連項目

フィードバック

フィードバック

その他のリソース

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

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