Use the Intelligent Insights performance diagnostics log of Azure SQL Database and Azure SQL Managed Instance performance issues
Applies to: 
Azure SQL Database
Azure SQL Managed Instance
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. 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
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 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 | |
| Workload Increase | |
| Memory Pressure | |
| Locking | |
| Increased MAXDOP | |
| Pagelatch Contention | |
| Missing Index | |
| New Query | |
| Unusual Wait Statistic | |
tempdb Contention |
|
| Elastic pool DTU Shortage | |
| Plan Regression | |
| Database-Scoped Configuration Value Change | |
| Slow Client | |
| Pricing Tier Downgrade |
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."
Next steps
- Learn about Intelligent Insights concepts.
- Learn how to Troubleshoot performance issues with Intelligent Insights.
- Learn how to collect and consume log data from your Azure resources.