Use the Intelligent Insights Azure SQL Database performance diagnostics log

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

The diagnostics log uses JSON standard format to output Intelligent Insights findings. The exact category property for accessing an Intelligent Insights log is the fixed value "SQLInsights".

The header of the log is common and consists of the time stamp (TimeGenerated) that shows when an entry was created. It also includes a resource ID (ResourceId) that refers to the particular SQL Database the entry relates to. The category (Category), level (Level), and operation name (OperationName) are fixed properties whose values do not change. 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

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.

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.

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

The next section of the Intelligent Insights performance log contains performance issues that were detected through built-in artificial intelligence. 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

The category (category) property describes the category of detectable performance patterns. See the following table for all possible categories of detectable performance patterns. 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
  • Increased MAXDOP
  • Query hashes
  • 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 Contention
  • Query hashes of queries causing contention
  • Query attribution to the overall database pagelatch contention wait time [%]
  • Elastic pool DTU Shortage
  • Elastic pool
  • Top DTU-consuming database
  • 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

    The impact (impact) property describes how much a detected behavior contributed to the problem that a database is having. 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. The property queries impacted (QueryHashes) provide a list of the query hashes that were affected by a particular detection.

    Impacted queries

    The next section of the Intelligent Insights log provides information about particular queries that were affected by the detected performance issues. This information is disclosed as an array of objects embedded in the impact_s property. The impact property consists of entities and metrics. Entities refer to a particular query (Type: Query). 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.

    In the following log example, the query with the hash 0x9102EXZ4 was detected to have an increased duration of execution (Metric: DurationIncreaseSeconds). 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

    The unit of measurement for each metric reported is provided under the metric (metric) property with the possible values of seconds, number, and percentage. The value of a measured metric is reported in the value (value) property.

    The DurationIncreaseSeconds property provides the unit of measurement in seconds. 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

    The last part of the Intelligent Insights performance log pertains to the automated root cause analysis of the identified performance degradation issue. 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."
    

    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