對 Azure SQL Database 上的高 CPU 進行診斷和疑難排解

  • 發行項

適用於：Azure SQL Database

Azure SQL Database 提供內建工具來識別高 CPU 使用率的原因，並將工作負載效能最佳化。 您可以使用這些工具，在事件發生時進行高 CPU 使用率的疑難排解，或在事件完成後被動進行。 您也可以啟用自動調整，主動減少一段時間的資料庫 CPU 使用率。 本文說明如何使用 Azure SQL Database 中的內建工具診斷和疑難排解高 CPU 使用率，並說明何時新增 CPU 資源。

了解虛擬核心計數

在診斷高 CPU 事件時，了解可供資料庫使用的虛擬核心 (vCore) 數目會很有幫助。 虛擬核心相當於邏輯 CPU。 虛擬核心數目有助於您了解資料庫可用的 CPU 資源。

識別 Azure 入口網站中的虛擬核心計數

如果您使用以虛擬核心為基礎的服務層級搭配佈建的計算層，便可以在 Azure 入口網站中快速識別資料庫的虛擬核心計數。 在此案例中，資料庫的 [概觀] 頁面上所列的定價層會包含虛擬核心計數。 例如，資料庫的定價層可能是「一般用途：標準系列 (Gen5)、16 個虛擬核心」。

針對無伺服器計算層中的資料庫，虛擬核心計數一律相當於資料庫的最大虛擬核心計數設定。 虛擬核心計數會顯示在 [概觀] 頁面上針對資料庫所列的定價層中。 例如，資料庫的定價層可能是「一般用途：無伺服器、標準系列 (Gen5)、16 個虛擬核心」。

如果您使用以 DTU 為基礎的購買模型下的資料庫，您將需要使用 SQL Transact-SQL 查詢資料庫的虛擬核心計數。

使用 Transact-SQL 識別虛擬核心計數

您可以使用 SQL Transact-SQL 找出任何資料庫目前的虛擬核心計數。 您可以使用 SQL Server Management Studio (SSMS)、Azure Data Studio 或 Azure 入口網站的查詢編輯器，對 Azure SQL Database 執行 Transact-SQL。

連線至您的資料庫，並執行下列查詢：

SELECT 
    COUNT(*) as vCores
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE';
GO

找出高 CPU 使用率的原因

您可以使用 Azure 入口網站、 SSMS 中的查詢存放區互動式工具，及 SSMS 與 Azure Data Studio 中的 Transact-SQL 查詢來測量和分析 CPU 使用率。

Azure 入口網站和查詢存放區會顯示已完成查詢的執行統計資料，例如 CPU 計量。 如果您目前遭遇的高 CPU 使用率事件，可能是由一或多個正在執行的長時間查詢所造成，請使用 Transact-SQL 找出目前正在執行的查詢。

造成新的和異常高的 CPU 使用率的常見原因如下：

  • 使用大量 CPU 的工作負載中的新查詢。
  • 定期執行查詢的頻率增加。
  • 查詢計畫回歸，包括參數敏感性計畫的 (PSP) 問題所造成的回歸，導致一或多個查詢耗用更多 CPU。
  • 查詢計畫的編譯或重新編譯大幅增加。
  • 資料庫中的查詢使用了過度平行的處理原則。

若要了解造成高 CPU 事件的原因，請找出資料庫何時發生高 CPU 使用率，以及當時當時最頻繁使用 CPU 的查詢。

檢查：

注意

Azure SQL Database 需要計算資源來執行核心服務功能，例如高可用性和災害修復、資料庫備份與還原、監視、查詢存放區、自動調整等等。在具有低虛擬核心計數的資料庫或密集彈性集區中的資料庫上，使用這些計算資源可能會特別明顯。 深入了解 Azure SQL Database 中的資源管理。

使用 Azure 入口網站來追蹤各種 CPU 計量，包括您的資料庫在一段時間內所使用的可用 CPU 百分比。 Azure 入口網站會將 CPU 計量與資料庫查詢存放區中的資訊結合，以便您識別指定時間內在資料庫中耗用 CPU 的查詢。

請遵循下列步驟來找出 CPU 百分比計量。

  1. 瀏覽至 Azure 入口網站中的資料庫。
  2. 在左側功能表的 [智慧型效能] 下，選取 [查詢效能深入解析]。

查詢效能深入解析的預設檢視會顯示 24 小時的資料。 CPU 使用率會顯示為資料庫使用的總可用 CPU 百分比。

在該期間內執行的前五項查詢會顯示在 CPU 使用率圖表上方的直條中。 選取圖表上的一段時間，或使用 [自訂] 功能表來探索特定的時段。 您也可以增加顯示的查詢數目。

Screenshot shows Query Performance Insight in the Azure portal.

選取每個顯示高 CPU 使用率的查詢識別碼，開啟查詢的詳細資料。 詳細資料包含查詢文字，以及查詢的效能歷程記錄。 檢查 CPU 是否因最近的查詢而增加。

記下查詢識別碼，藉以使用下一節中的查詢存放區進一步調查查詢計畫。

在 Azure 入口網站中針對所識別出的熱門查詢，檢閱與其有關的查詢計畫

遵循這些步驟，在 SSMS 的互動式查詢存放區工具中使用查詢識別碼，藉以檢查一段時間內的查詢執行計畫。

  1. 開啟 SSMS。
  2. 在 [物件總管] 中連線至 Azure SQL Database。
  3. 在 [物件總管] 中，展開資料庫節點。
  4. 展開 [查詢存放區] 資料夾。
  5. 開啟 [追蹤查詢] 窗格。
  6. 在畫面左上角的 [追蹤查詢] 方塊中輸入查詢識別碼，然後按 Enter 鍵。
  7. 如有必要，請選取 [設定] 調整時間間隔，使其符合高 CPU 使用率的發生時間。

此頁面會顯示查詢的執行計畫，及其在最近 24 小時內的相關計量。

使用 Transact-SQL 找出正在執行的查詢 SQL

Transact-SQL 可讓您識別目前執行中的查詢，及其目前為止所使用的 CPU 時間。 您也可以使用 SQL Transact-SQL 查詢資料庫中最近的 CPU 使用率、依 CPU 排列的熱門查詢，以及最常編譯的查詢。

您可以使用 SQL Server Management Studio (SSMS)、Azure Data Studio 或 Azure 入口網站的查詢編輯器 查詢 CPU 計量。 使用 SSMS 或 Azure Data Studio 時，請開啟新的查詢視窗，並將其連接至您的資料庫 (而非 master 資料庫)。

執行下列查詢，已找出目前正在執行的查詢，以及 CPU 使用率和執行計畫。 CPU 時間會以毫秒為單位傳回。

SELECT
    req.session_id,
    req.status,
    req.start_time,
    req.cpu_time AS 'cpu_time_ms',
    req.logical_reads,
    req.dop,
    s.login_name,
    s.host_name,
    s.program_name,
    object_name(st.objectid,st.dbid) 'ObjectName',
    REPLACE (REPLACE (SUBSTRING (st.text,(req.statement_start_offset/2) + 1,
        ((CASE req.statement_end_offset    WHEN -1    THEN DATALENGTH(st.text) 
        ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1),
        CHAR(10), ' '), CHAR(13), ' ') AS statement_text,
    qp.query_plan,
    qsx.query_plan as query_plan_with_in_flight_statistics
FROM sys.dm_exec_requests as req  
JOIN sys.dm_exec_sessions as s on req.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as st
OUTER APPLY sys.dm_exec_query_plan(req.plan_handle) as qp
OUTER APPLY sys.dm_exec_query_statistics_xml(req.session_id) as qsx
ORDER BY req.cpu_time desc;
GO

此查詢會傳回執行計畫的兩個複本。 資料行 query_plan 包含 sys.dm_exec_query_plan 的執行計畫。 此版本的查詢計畫只會包含資料列計數的估計值，而且不包含任何執行統計資料。

如果是由資料行 query_plan_with_in_flight_statistics 傳回執行計畫，此計畫會提供較詳細的資訊。 query_plan_with_in_flight_statistics 資料行會傳回 sys.dm_exec_query_statistics_xml 的資料，其中包括「傳輸中」的執行統計資料，例如目前正在執行的查詢所傳回的實際資料列數。

查看過去一小時的 CPU 使用率計量

下列的 sys.dm_db_resource_stats 查詢會傳回大約過去一小時以 15 秒為間隔的平均 CPU 使用率。

SELECT
    end_time,
    avg_cpu_percent,
    avg_instance_cpu_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC; 
GO

請勿將重點僅放在 avg_cpu_percent 資料行上。 avg_instance_cpu_percent 資料行包含使用者和內部工作負載所使用的 CPU。 如果 avg_instance_cpu_percent 接近100%，CPU 資源便會飽和。 在此情況下，如果應用程式輸送量不足或查詢延遲很高，您應該針對高 CPU 進行疑難排解。

深入了解 Azure SQL Database 中的資源管理。

請參閱 sys.dm_db_resource_stats 中的範例，以了解更多查詢。

依 CPU 使用率查詢最近的 15 個查詢

查詢存放區會追蹤查詢的執行統計資料，包括 CPU 使用率。 下列查詢會傳回過去 2 小時內執行的前 15 個查詢 (依 CPU 使用率排序)。 CPU 時間會以毫秒為單位傳回。

WITH AggregatedCPU AS 
    (SELECT
        q.query_hash, 
        SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms, 
        SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_ms, 
        MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms, 
        MAX(max_logical_io_reads) max_logical_reads, 
        COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, 
        COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, 
        SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS aborted_execution_count, 
        SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS regular_execution_count, 
        SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS exception_execution_count, 
        SUM(count_executions) AS total_executions, 
        MIN(qt.query_sql_text) AS sampled_query_text
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
    JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
    JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
    JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
    WHERE 
            rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception') AND 
        rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
     GROUP BY q.query_hash), 
OrderedCPU AS 
    (SELECT *, 
    ROW_NUMBER() OVER (ORDER BY total_cpu_ms DESC, query_hash ASC) AS RN
    FROM AggregatedCPU)
SELECT *
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_ms DESC;
GO

此查詢會依據查詢的雜湊值來分組。 如果您在 number_of_distinct_query_ids 資料行中找到較高的值，請調查是否經常執行的查詢未正確參數化。 非參數化查詢可能會在每次執行時進行編譯，這會耗用大量 CPU 並影響查詢存放區的效能。

若要深入了解個別查詢，請記下查詢雜湊，然後使用它來識別指定查詢雜湊的 CPU 使用率和查詢計畫。

透過查詢雜湊來查詢最常編譯的查詢

編譯查詢計畫是需要大量 CPU 的流程。 Azure SQL Database 會快取記憶體中的計畫以供重複使用。 如果查詢未參數化，或 RECOMPILE 提示強制重新編譯，便可能會經常編譯某些查詢。

查詢存放區會追蹤查詢的編譯次數。 執行下列查詢，藉以依編譯計數識別查詢存放區中的前 20 個查詢，以及每分鐘的平均編譯次數：

SELECT TOP (20)
    query_hash,
    MIN(initial_compile_start_time) as initial_compile_start_time,
    MAX(last_compile_start_time) as last_compile_start_time,
    CASE WHEN DATEDIFF(mi,MIN(initial_compile_start_time), MAX(last_compile_start_time)) > 0
        THEN 1.* SUM(count_compiles) / DATEDIFF(mi,MIN(initial_compile_start_time), 
            MAX(last_compile_start_time)) 
        ELSE 0 
        END as avg_compiles_minute,
    SUM(count_compiles) as count_compiles
FROM sys.query_store_query AS q
GROUP BY query_hash
ORDER BY count_compiles DESC;
GO

若要深入了解個別查詢，請記下查詢雜湊，然後使用它來識別指定查詢雜湊的 CPU 使用率和查詢計畫。

識別指定查詢雜湊的 CPU 使用率和查詢計畫

執行下列查詢，藉以找出指定 query_hash 的個別查詢識別碼、查詢文字和查詢執行計畫。 CPU 時間會以毫秒為單位傳回。

將 @query_hash 變數的值取代為對於您的工作負載有效的 query_hash。

declare @query_hash binary(8);

SET @query_hash = 0x6557BE7936AA2E91;

with query_ids as (
    SELECT
        q.query_hash,
        q.query_id,
        p.query_plan_hash,
        SUM(qrs.count_executions) * AVG(qrs.avg_cpu_time)/1000. as total_cpu_time_ms,
        SUM(qrs.count_executions) AS sum_executions,
        AVG(qrs.avg_cpu_time)/1000. AS avg_cpu_time_ms
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p on q.query_id=p.query_id
    JOIN sys.query_store_runtime_stats qrs on p.plan_id = qrs.plan_id
    WHERE q.query_hash = @query_hash
    GROUP BY q.query_id, q.query_hash, p.query_plan_hash)
SELECT qid.*,
    qt.query_sql_text,
    p.count_compiles,
    TRY_CAST(p.query_plan as XML) as query_plan
FROM query_ids as qid
JOIN sys.query_store_query AS q ON qid.query_id=q.query_id
JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON qid.query_id=p.query_id and qid.query_plan_hash=p.query_plan_hash
ORDER BY total_cpu_time_ms DESC;
GO

對於查詢存放區的整個歷程記錄之中的 query_hash，此查詢會針對每個執行計畫的變化傳回一個資料列。 結果會依總 CPU 時間來排序。

使用互動式查詢存放區工具來追蹤 CPU 使用率的歷程記錄

如果您想使用圖表工具，請依照下列步驟來使用 SSMS 中的互動式查詢存放區工具。

  1. 開啟 SSMS，並在 [物件總管] 中連線至您的資料庫。
  2. 在 [物件總管] 中，展開資料庫節點
  3. 展開 [查詢存放區] 資料夾。
  4. 開啟 [整體資源耗用量] 窗格。

資料庫的上個月總 CPU 時間 (以毫秒為單位) 會顯示在窗格的左下方部分。 在預設檢視中，CPU 時間是依日彙總。

Screenshot shows the Overall Resource Consumption view of Query Store in SSMS.

選取窗格右上方的 [設定]，以選取不同的時段。 您也可以變更彙總的單位。 例如，您可以選擇查看特定日期範圍的資料，並依小時彙總資料。

使用互動式查詢存放區工具，依 CPU 時間識別熱門查詢

選取圖表中的橫條以進一步切入，並查看在特定時段內執行的查詢。 [熱門資源取用查詢] 窗格將會開啟。 您也可以直接在物件總管中，從資料庫下的查詢存放區節點開啟 [熱門資源取用查詢]。

Screenshot shows the Top Resource Consuming Queries pane for Query Store in S S M S.

在預設檢視中，[熱門資源取用查詢] 窗格會依 [持續時間 (毫秒)] 顯示查詢。 持續時間有時可能會低於 CPU 時間：使用平行處理原則的查詢所使用的 CPU 時間可能會比其整體持續時間更多。 如果等候時間很長，則持續時間也可能高於 CPU 時間。 若要查看依 CPU 時間的查詢，請選取窗格左上角的 [計量] 下拉式清單，然後選取 [CPU 時間 (毫秒)]。

左上象限中的每個橫條都代表一個查詢。 選取橫條以查看該查詢的詳細資料。 畫面的右上方會顯示該查詢的查詢存放區中有多少個執行計畫，並根據執行的時間和您選取的計量數量來對應。 選取每個計畫識別碼，以控制要在畫面的下半部顯示的查詢執行計畫。

注意

如需解讀查詢存放區檢視和在熱門資源取用者檢視中顯示之圖形的指南，請參閱查詢存放區的最佳做法

降低 CPU 使用率

疑難排解的一部分應該包括深入了解上一節中所識別的查詢。 您可以調整索引、修改應用程式模式、調整查詢，以及調整資料庫的 CPU 相關設定，藉以降低 CPU 使用率。

請考慮本節中的下列策略。

使用自動索引微調來降低 CPU 使用率

有效的索引微調可減少許多查詢的 CPU 使用率。 最佳化索引會減少查詢的邏輯和實體讀取，這通常可減少查詢所需執行的工作。

Azure SQL Database 針對主要複本上的工作負載提供自動索引管理。 自動索引管理使用機器學習服務來監視您的工作負載，並針對您的資料庫最佳化資料列存放區磁碟型非叢集索引。

請在 Azure 入口網站中參閱效能建議，包括索引建議。 您可以手動套用這些建議，也可以啟用 CREATE INDEX 自動微調選項，以便在資料庫中建立新索引並驗證效能。

使用自動計畫修正來降低 CPU 使用率 (強制執行計畫)

高 CPU 事件的另一個常見原因是執行計畫選擇迴歸。 Azure SQL Database 提供強制執行計畫自動微調選項，藉以在主要複本找出工作負載中查詢執行計畫的迴歸。 啟用此自動微調功能之後，Azure SQL Database 將會測試強制查詢執行計畫是否會讓具有執行計畫迴歸的查詢產生可靠的效能提升。

如果您的資料庫是在 2020 年 3 月之後建立的，系統會自動啟用強制執行計畫自動微調選項。 如果您的資料庫是在這段時間之前建立的，建議您啟用強制執行計畫自動微調選項。

手動微調索引

使用識別高 CPU 使用率的原因中所述的方法，識別出最高 CPU 耗用查詢的查詢計畫。 這些執行計畫將協助您識別並新增非叢集索引，以加速查詢。

您的資料庫中每個以磁碟為基礎的非叢集索引都需要儲存空間，而且必須由 SQL 引擎維護。 請盡可能修改現有的索引，而非加入新的索引，並確保新的索引能成功減少 CPU 使用率。 如需非叢集索引的概觀，請參閱非叢集索引設計指導方針。

對於某些工作負載來說，資料行存放區索引可能是減少頻繁讀取查詢 CPU 的最佳選擇。 如需可能適用於資料行存放區索引之案例的相關概略建議，請參閱資料行存放區索引 - 設計指導。

調整您的應用程式、查詢和資料庫設定

在檢查熱門查詢時，您可能會發現應用程式反模式，例如「多對話」行為、可受益於分區化的工作負載，以及次佳資料庫存取設計。 針對大量讀取的工作負載，請考慮唯讀複本來卸載唯讀查詢工作負載和應用程式層快取為長期策略，藉以擴增頻繁讀取的資料。

您也可以選擇手動微調工作負載中所識別出的最高 CPU 使用量的查詢。 手動微調選項包括重寫 Transact-SQL 陳述式、在查詢存放區中強制執行計畫，以及套用查詢提示。

如果您發現有時查詢使用的執行計畫在效能上並非最佳的情況，請檢閱參數敏感性計畫 (PSP) 問題的查詢中的解決方案

如果您找出具有大量計畫的非參數化查詢，請考慮將這些查詢參數化，並務必完整宣告參數資料類型，包括長度和精確度。 這可以藉由修改查詢、建立計畫指南來強制參數化特定查詢，或在資料庫層級啟用強制參數化來完成。

如果您找出有較高編譯速率的查詢，請找出造成頻繁編譯的原因。 頻繁編譯的最常見原因是 RECOMPILE 提示。 盡可能識別新增 RECOMPILE 提示的時間，以及該提示所欲解決的問題。 調查是否可以實作替代效能調整解決方案，為經常執行的查詢提供一致的效能，而不需要 RECOMPILE 提示。

調整平行處理原則的最大程度來降低 CPU 使用率

平行處理原則的最大程度 (MAXDOP) 設定會控制資料庫引擎中的查詢內平行處理原則。 較高的 MAXDOP 值通常會導致每個查詢有更多平行執行緒，並加快查詢執行的速度。

在某些情況下，同時執行的大量平行查詢可能會使工作負載變慢，並造成高 CPU 使用率。 如果資料庫有大量的虛擬核心，其中 MAXDOP 設為較高的數字或零，便很有可能有過度平行處理的情況。 MAXDOP 設為零時，資料庫引擎會將平行執行緒所使用的排程器數目設定為邏輯核心的總數目或 64，以較小者為準。

您可以使用 Transact-SQL 識別資料庫的平行處理原則的最大程度設定。 使用 SSMS 或 Azure Data Studio 連線到您的資料庫，並執行下列查詢：

SELECT 
    name, 
    value, 
    value_for_secondary, 
    is_value_default 
FROM sys.database_scoped_configurations
WHERE name=N'MAXDOP';
GO

請考慮在資料庫層級試驗 MAXDOP 設定中的小幅變更，或使用查詢提示來個別修改有問題的查詢，以使用非預設的 MAXDOP。 如需詳細資訊，請參閱設定平行處理原則的最大程度中的範例。

新增 CPU 資源的時機

您可能會發現工作負載的查詢和索引已正確調整，或者效能微調所需的變更是您因為內部流程或其他原因而無法在短期內進行的變更。 新增更多 CPU 資源對這些資料庫可能會很有幫助。 您可以在最短停機時間的情況下調整資料庫資源。

您可以使用虛擬核心購買模型設定資料庫的虛擬核心計數或硬體設定，將更多 CPU 資源新增至您的 Azure SQL Database。

在以 DTU 為基礎的購買模型下，您可以提高服務層級，並增加 (DTU) 的資料庫交易單位數目。 DTU 代表 CPU、記憶體、讀取和寫入的混合測量單位。 虛擬核心購買模型的其中一個優點是，您可以用更細微的方式控制使用中的硬體和虛擬核心數目。 您可以將 Azure SQL Database 從以 DTU 為基礎的模型移轉至以虛擬核心為基礎的模型，以便在購買模型之間轉換。

相關內容

若要深入了解監視和效能微調 Azure SQL Database，請參閱下列文章：