使用 Intelligent Insights Azure SQL Database 效能診斷記錄Use the Intelligent Insights Azure SQL Database performance diagnostics log

此頁面提供有關如何使用 Intelligent Insights 所產生 Azure SQL Database 效能診斷記錄的使用方式、其格式及其所含資料的資訊,來因應您的自訂開發需求。This page provides information on how to use the Azure SQL Database performance diagnostics log generated by Intelligent Insights, its format, and the data it contains for your custom development needs. 您可以傳送到此診斷記錄Azure 監視器記錄Azure 事件中樞Azure 儲存體,或是提供自訂的 DevOps 警示和報告的第三方解決方案功能。You can send this diagnostics log to Azure Monitor logs, Azure Event Hubs, Azure Storage, or a third-party solution for custom DevOps alerting and reporting capabilities.

記錄標頭Log header

診斷記錄使用 JSON 標準格式來輸出 Intelligent insights 結果。The diagnostics log uses JSON standard format to output Intelligent Insights findings. 用於存取 Intelligent Insights 記錄的確切類別屬性是一個固定值 "SQLInsights"。The exact category property for accessing an Intelligent Insights log is the fixed value "SQLInsights".

記錄的標頭是通用的,而且包含在建立項目時顯示的時間戳記 (TimeGenerated)。The header of the log is common and consists of the time stamp (TimeGenerated) that shows when an entry was created. 它也會包含資源識別碼 (ResourceId),該識別碼參考與項目與相關的特定 SQL Database。It also includes a resource ID (ResourceId) that refers to the particular SQL Database the entry relates to. 類別 (Category)、 層級 (Level) 和作業名稱 (OperationName) 是值不會變更的固定屬性。The category (Category), level (Level), and operation name (OperationName) are fixed properties whose values do not change. 它們表示記錄項目為參考資訊,來自 Intelligent Insights (SQLInsights)。They indicate that the log entry is informational and that it comes from Intelligent Insights (SQLInsights).

"TimeGenerated" : "2017-9-25 11:00:00", // time stamp of the log entry
"ResourceId" : "database identifier", // value points to a database resource
"Category": "SQLInsights", // fixed property
"Level" : "Informational", // fixed property
"OperationName" : "Insight", // fixed property

問題識別碼和受影響的資料庫Issue ID and database affected

問題識別屬性 (issueId_d) 提供一種以獨特方式追蹤效能問題直到解決為止的方法。The issue identification property (issueId_d) provides a way of uniquely tracking performance issues until resolved. 相同問題的記錄報告狀態中的多筆事件記錄會共用相同的問題識別碼。Multiple event records in the log reporting status of the same issue will share the same issue ID.

除了「問題識別碼」之外,診斷記錄還會回報與診斷記錄中所回報問題相關之特定事件的開始 (intervalStartTime_t) 和結束 (intervalEndTme_t) 時間戳記。Along with the issue ID, the diagnostics log reports the start (intervalStartTime_t) and end (intervalEndTme_t) time stamps of the particular event related to an issue that's reported in the diagnostics log.

彈性集區 (elasticPoolName_s) 屬性會指出發生問題之資料庫所屬的彈性集區。The elastic pool (elasticPoolName_s) property indicates which elastic pool the database with an issue belongs to. 如果資料庫不屬於彈性集區,此屬性就不會有值。If the database isn't part of an elastic pool, this property has no value. 資料庫名稱 (databaseName_s) 屬性中會顯示所偵測到有問題的資料庫。The database in which an issue was detected is disclosed in the database name (databaseName_s) property.

"intervalStartTime_t": "2017-9-25 11:00", // start of the issue reported time stamp
"intervalEndTme_t":"2017-9-25 12:00", // end of the issue reported time stamp
"elasticPoolName_s" : "", // resource elastic pool (if applicable) 
"databaseName_s" : "db_name",  // database name
"issueId_d" : 1525, // unique ID of the issue detected
"status_s" : "Active" // status of the issue – possible values: "Active", "Verifying", and "Complete"

偵測到的問題Detected issues

Intelligent Insights 效能記錄的下一個區段包含透過內建的人工智慧偵測到的效能問題。The next section of the Intelligent Insights performance log contains performance issues that were detected through built-in artificial intelligence. JSON 診斷記錄內的屬性會顯示偵測。Detections are disclosed in properties within the JSON diagnostics log. 這些偵測包括問題類別、問題的影響、受影響的查詢和計量。These detections consist of the category of an issue, the impact of the issue, the queries affected, and the metrics. 偵測屬性可能包含多個偵測到的效能問題。The detections properties might contain multiple performance issues that were detected.

偵測到的效能問題會藉由下列偵測屬性結構回報:Detected performance issues are reported with the following detections property structure:

"detections_s" : [{
"impact" : 1 to 3, // impact of the issue detected, possible values 1-3 (1 low, 2 moderate, 3 high impact)
"category" : "Detectable performance pattern", // performance issue detected, see the table
"details": <Details outputted> // details of an issue (see the table)
}] 

下表提供可偵測的效能模式,以及輸出到診斷記錄中的詳細資料。Detectable performance patterns and the details that are outputted to the diagnostics log are provided in the following table.

偵測類別Detection category

類別 (category) 屬性會說明可偵測之效能模式的類別。The category (category) property describes the category of detectable performance patterns. 如需可偵測之效能模式的所有可能類別,請參閱下表。See the following table for all possible categories of detectable performance patterns. 如需詳細資訊,請參閱使用 Intelligent Insights 針對資料庫效能問題進行疑難排解For more information, see Troubleshoot database performance issues with Intelligent Insights.

依據偵測到的效能問題之不同,診斷記錄檔中輸出的詳細資料也會有所不同。Depending on the performance issue detected, the details outputted in the diagnostics log file differ accordingly.

可偵測的效能模式Detectable performance patterns 輸出的詳細資料Details outputted
達到資源限制Reaching resource limits
  • 受影響的資源Resources affected
  • 查詢雜湊Query hashes
  • 資源耗用量百分比Resource consumption percentage
  • 工作負載增加Workload Increase
  • 執行時間增加的查詢數目Number of queries whose execution increased
  • 對工作負載增加影響最大之查詢的查詢雜湊Query hashes of queries with the largest contribution to the workload increase
  • 記憶體壓力Memory Pressure
  • 記憶體 ClerkMemory clerk
  • 鎖定Locking
  • 受影響的查詢雜湊Affected query hashes
  • 封鎖查詢雜湊Blocking query hashes
  • MAXDOP 增加Increased MAXDOP
  • 查詢雜湊Query hashes
  • CXP 等候時間CXP wait times
  • 等候時間Wait times
  • 頁面閂鎖爭用Pagelatch Contention
  • 造成爭用之查詢的查詢雜湊Query hashes of queries causing contention
  • 遺漏索引Missing Index
  • 查詢雜湊Query hashes
  • 新查詢New Query
  • 新查詢的查詢雜湊Query hash of the new queries
  • 不尋常的等候統計資料Unusual Wait Statistic
  • 不尋常的等候類型Unusual wait types
  • 查詢雜湊Query hashes
  • 查詢等候時間Query wait times
  • TempDB 爭用TempDB Contention
  • 造成爭用之查詢的查詢雜湊Query hashes of queries causing contention
  • 整體資料庫頁面閂鎖爭用等候時間的查詢歸屬 [%]Query attribution to the overall database pagelatch contention wait time [%]
  • 彈性集區 DTU 不足Elastic pool DTU Shortage
  • 彈性集區Elastic pool
  • DTU 取用量最高的資料庫Top DTU-consuming database
  • 取用量最高之取用者所使用的不良 DTU 百分比Percent of pool DTU used by the top consumer
  • 計畫迴歸Plan Regression
  • 查詢雜湊Query hashes
  • 良好計畫識別碼Good plan IDs
  • 不良計畫識別碼Bad plan IDs
  • 資料庫範圍設定值變更Database-Scoped Configuration Value Change
  • 與預設值相比的資料庫範圍組態變更Database-scoped configuration changes compared to the default values
  • 用戶端執行速度太慢Slow Client
  • 查詢雜湊Query hashes
  • 等候時間Wait times
  • 定價層降級Pricing Tier Downgrade
  • 文字通知Text notification
  • 影響Impact

    影響 (impact) 屬性會說明偵測行為會對資料庫具有的問題造成多少影響。The impact (impact) property describes how much a detected behavior contributed to the problem that a database is having. 影響範圍從 1 到 3,3 具有最高的比重、2 為中等,1 的比重最低。Impacts range from 1 to 3, with 3 as the highest contribution, 2 as moderate, and 1 as the lowest contribution. 視您的特定需求而定,可以使用影響值來作為自訂警示自動化的輸入。The impact value might be used as an input for custom alerting automation, depending on your specific needs. 受影響的屬性查詢 (QueryHashes) 會提供受特定偵測影響的查詢雜湊清單。The property queries impacted (QueryHashes) provide a list of the query hashes that were affected by a particular detection.

    受影響的查詢Impacted queries

    Intelligent Insights 記錄的下一個區段提供受所偵測到之效能問題影響的特定查詢相關資訊。The next section of the Intelligent Insights log provides information about particular queries that were affected by the detected performance issues. 此資訊會以內嵌在 impact_s 屬性中的物件陣列形式顯示。This information is disclosed as an array of objects embedded in the impact_s property. Impact 屬性是由實體和計量所組成。The impact property consists of entities and metrics. 實體是指特定的查詢 (類型:查詢)。Entities refer to a particular query (Type: Query). 唯一的查詢雜湊會在值 (Value) 屬性底下顯示。The unique query hash is disclosed under the value (Value) property. 此外,所顯示的每個查詢後面都會接著一個計量和值,用來指出所偵測到的效能問題。In addition, each of the queries disclosed is followed by a metric and a value, which indicate a detected performance issue.

    在下列記錄範例中,使用雜湊 0x9102EXZ4 的查詢會被偵測到有增加的執行持續時間 (計量:DurationIncreaseSeconds)。In the following log example, the query with the hash 0x9102EXZ4 was detected to have an increased duration of execution (Metric: DurationIncreaseSeconds). 110 秒的值指出此特定查詢會再花費 110 秒的時間執行。The value of 110 seconds indicates that this particular query took 110 seconds longer to execute. 因為可以偵測到多個查詢,因此這個特定記錄區段可以包含多個查詢項目。Because multiple queries can be detected, this particular log section might include multiple query entries.

    "impact" : [{
    "entity" : { 
    "Type" : "Query", // type of entity - query
    "Value" : "query hash value", // for example "0x9102EXZ4" query hash value },
    "Metric" : "DurationIncreaseSeconds", // measured metric and the measurement unit (in this case seconds)
    "Value" : 110 // value of the measured metric (in this case seconds)
    }]
    

    度量Metrics

    計量 (metric) 屬性底下會提供所回報之每個計量的度量單位,可能的值為:秒、數字及百分比。The unit of measurement for each metric reported is provided under the metric (metric) property with the possible values of seconds, number, and percentage. 值 (value) 屬性中會回報所測量計量的值。The value of a measured metric is reported in the value (value) property.

    DurationIncreaseSeconds 屬性提供以秒為單位的度量單位。The DurationIncreaseSeconds property provides the unit of measurement in seconds. CriticalErrorCount 度量單位是數字,代表錯誤計數。The CriticalErrorCount unit of measurement is a number that represents an error count.

    "metric" : "DurationIncreaseSeconds", // issue metric type – possible values: DurationIncreaseSeconds, CriticalErrorCount, WaitingSeconds
    "value" : 102 // value of the measured metric (in this case seconds)
    

    根本原因分析與改進建議Root cause analysis and improvement recommendations

    Intelligent Insights 效能記錄的最後部分是關於所識別之效能降低問題的自動化根本原因分析。The last part of the Intelligent Insights performance log pertains to the automated root cause analysis of the identified performance degradation issue. 此資訊會透過根本原因分析 (rootCauseAnalysis_s) 屬性以人類易讀的用語顯示。The information appears in human-friendly verbiage in the root cause analysis (rootCauseAnalysis_s) property. 改進的建議包含在可能的記錄中。Improvement recommendations are included in the log where possible.

    // example of reported root cause analysis of the detected performance issue, in a human-readable format
    
    "rootCauseAnalysis_s" : "High data IO caused performance to degrade. It seems that this database is missing some indexes that could help."
    

    您可以使用 Intelligent Insights 效能記錄檔Azure 監視器記錄或協力廠商解決方案,提供自訂的 DevOps 警示和報告功能。You can use the Intelligent Insights performance log with Azure Monitor logs or a third-party solution for custom DevOps alerting and reporting capabilities.

    後續步驟Next steps