使用有关 Azure SQL 数据库和 Azure SQL 托管实例性能问题的智能见解性能诊断日志Use the Intelligent Insights performance diagnostics log of Azure SQL Database and Azure SQL Managed Instance performance issues

适用于: Azure SQL 数据库 Azure SQL 托管实例

本页介绍如何使用智能见解生成的有关 Azure SQL 数据库和 Azure SQL 托管实例性能问题的性能诊断日志,以及其格式和它为满足自定义开发需求所包含的数据。This page provides information on how to use the performance diagnostics log generated by Intelligent Insights of Azure SQL Database and Azure SQL Managed Instance performance issues, its format, and the data it contains for your custom development needs. 可将此诊断日志发送到 Azure 事件中心Azure 存储或第三方解决方案,用于自定义 DevOps 警报和报告功能。You can send this diagnostics log to 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. 它还包括一个引用条目所涉及的特定数据库的资源 ID (ResourceId)。It also includes a resource ID (ResourceId) that refers to the particular 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

问题 ID 和受影响的数据库Issue ID and database affected

问题标识属性 (issueId_d) 提供一种跟踪性能问题直至解决的独特方法。The issue identification property (issueId_d) provides a way of uniquely tracking performance issues until resolved. 同一问题的日志报告状态中的多个事件记录将共享同一问题 ID。Multiple event records in the log reporting status of the same issue will share the same issue ID.

除问题 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
  • 内存分配器Memory clerk
  • 锁定Locking
  • 受影响的查询哈希Affected query hashes
  • 阻止的查询哈希Blocking query hashes
  • 增加的 MAXDOPIncreased MAXDOP
  • 查询哈希Query hashes
  • CXP 等待时间CXP wait times
  • 等待时间Wait times
  • Pagelatch 争用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
  • 致使整体数据库 pagelatch 争用等待时间增加的查询 [%]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
  • 完善的计划 IDGood plan IDs
  • 错误的计划 IDBad 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. 影响属性包含实体和指标。The impact property consists of entities and metrics. 实体引用特定查询(类型:Query)。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."
    

    后续步骤Next steps