Analyzing Long Running Operation (SQL Query) Telemetry
Note
Azure Active Directory is now Microsoft Entra ID. Learn more
Any SQL query that takes longer than 750 milliseconds to execute will be sent to your Azure Application Insights resource. This enables you to focus on tuning SQL queries that take too long to execute (maybe one or more tables miss an index or maybe some filters are missing). It also reduces the ingestion of data into the Azure Application Insights resource to save cost of having telemetry.
If you want to capture all SQL queries for a short period of time for a given session, you can enable Additional logging from the Help & Support page. This lets you analyze queries that take a short time to run but happen very frequently. For Business Central online, additional logging is available on production environments running version 17.4 or later. For Business Central on-premises, additional logging is available on environments running version 18.6 or later.
Note
With Business Central on-premises, you can change the threshold that defines long running queries. For more information, see Defining Long Running SQL Queries Threshold.
There are multiple reasons that affect the time it takes SQL queries to run. For example, the database could be waiting for a lock to be released. Or, the database is executing an operation that does badly because of missing indexes. Sometimes you can look at the SQL statement that was generated by the code to see what caused the delay. This information is found in the CustomDimension data, specifically the AL Stack Trace column.
Dimensions in Application Insights
This table explains the columns included in long running query events emitted to Application Insights. Bold text indicates that the value of the columns is a constant. Some columns are standard for Application Insights. These columns are indicated by Application Insights.
| Column | Description or value |
|---|---|
| timestamp | Specifies the date and time that the long running query event occurred, such as 2019-08-20T07:23:07.9996696Z |
| message | Version 16.1 and later: Operation exceeded time threshold (SQL query) Before version 16.1: Action took longer than expected |
| severityLevel | 2 (This level indicates a warning. Long running queries are always recorded as warnings) |
| itemType | trace |
| customDimensions | (see table that follows) |
| operation_Name | Long Running Operation (SQL Query) Note: The use of the operation_Name column was deprecated in version 16.1. In future versions, data won't be stored in this column. So in version 16.1 and later, use the custom dimension column eventID column custom in Kusto queries instead of operation_Name. |
| operation_Id | Specifies the GUID assigned to the client operation. An operation is created whenever the user does something in the client, such as selecting an action. |
| operation_ParentId | Currently this column is the same as the operation_Id. This behavior might change in a future release. |
| session_Id | Specifies the GUID of the client session. When a client makes a connection to the Business Central Server instance, a session is created and assigned an ID. |
| client_Type | Application Insights |
| client_IP | Application Insights |
| client_City | Application Insights |
| client_StateOrProvince | Application Insights |
| client_CountryOrRegion | Application Insights |
| cloud_RoleName | Specifies the display name of Business Central tenant. For on-premises, this value is the same as the cloud_RoleInstance. |
| cloud_RoleInstance | Specifies the name of Business Central tenant. |
| appId | Application Insights |
| appName | Application Insights |
| iKey | Application Insights |
| sdkVersion | Application Insights |
| user_Id | The user telemetry ID for the user. From the user card, you can use user_Id to identify the user who triggered this telemetry event. Learn more in Assign a telemetry ID to users. |
CustomDimensions
This table describes the different dimensions of a Long Running Operation (SQL Query) operation.
| Column (key) | Description or value |
|---|---|
| extensionVersion | Specifies the version of the extension. |
| telemetrySchemaVersion | Specifies the version of the Business Central telemetry schema. |
| componentVersion | Specifies the version number of the component that emits telemetry (see the component dimension.) |
| environmentType | Specifies the environment type of the Business Central solution, such as Production or Sandbox. |
| environmentName | Specifies the environment name of the Business Central solution, such as Production or Sandbox. |
| extensionName | Specifies the name of the extension. |
| alObjectType | The type of the AL object that executed the SQL statement |
| alObjectName | The name of the AL object that executed the SQL statement |
| alStackTrace | The stack trace in AL. |
| companyName | The display name of the Business Central company that was used at time of execution. |
| extensionId | Specifies the AppID of the extension. |
| eventId | RT0005 This dimension was introduced in Business Central 2020 release wave 1, version 16.1. |
| aadTenantId | Specifies the Microsoft Entra tenant ID when using Microsoft Entra authentication. For on-premises, if you aren't using Microsoft Entra authentication, this value is common. |
| clientType | Specifies the type of client that executed the SQL Statement, such as Background or Web. For a list of the client types, see ClientType Option Type. |
| alObjectId | The type of the AL object that executed the SQL statement. |
| component | Specifies the Business Central Server instance name and the platform version. |
| executionTime | Specifies the time that it took to execute the SQL statement**. The value has the format hh:mm:ss.sssssss. |
| longRunningThreshold | Specifies the amount of time that an SQL query can run before a warning event is recorded. The value has the format hh:mm:ss.sssssss. This threshold is controlled by the Business Central Server configuration setting called SqlLongRunningThreshold. |
| sqlStatement | Specifies the SQL statement that was executed for the long running query. The value is limited to 8192 characters. If the value exceeds 8192 characters, it will be truncated in manner that still provides the most pertinent information. |
| deprecatedKeys | A comma-separated list of all the keys that have been deprecated. The keys in this list are still supported but will eventually be removed in the next major release. We recommend that update any queries that use these keys to use the new key name. |
** From telemetrySchemaVersion 0.6 and onwards, this value also includes the CompanyOpen operation.
Example
The following code snippet shows an example of the CustomDimensions.
{"extensionVersion":"16.0.10962.0","telemetrySchemaVersion":"0.3","componentVersion":"15.0.40494.0","environmentType":"Production","environmentName":"Production","extensionName":"Base Application","alObjectType":"Report","alObjectName":"Suggest Worksheet Lines","alStackTrace":"AppObjectType: Report\r\n AppObjectId: 840\r\n AL CallStack: \"Suggest Worksheet Lines\"(Report 840).DeleteEntries line 10 - Base Application by Microsoft\r\n\"Suggest Worksheet Lines\"(Report 840).\"Cash Flow Forecast - OnPostDataItem\"(Trigger) line 5 - Base Application by Microsoft\r\n\"Cash Flow Management\"(CodeUnit 841).UpdateCashFlowForecast line 32 - Base Application by Microsoft\r\n\"Cash Flow Forecast Update\"(CodeUnit 842).OnRun(Trigger) line 18 - Base Application by Microsoft\r\n\"Job Queue Start Codeunit\"(CodeUnit 449).OnRun(Trigger) line 11 - Base Application by Microsoft\r\n\"Job Queue Dispatcher\"(CodeUnit 448).HandleRequest line 30 - Base Application by Microsoft\r\n\"Job Queue Dispatcher\"(CodeUnit 448).OnRun(Trigger) line 19 - Base Application by Microsoft","companyName":"CRONUS USA, Inc.","extensionId":"437dbf0e-84ff-417a-965d-ed2bb9650972","aadTenantId":"aaaabbbb-0000-cccc-1111-dddd2222eeee","clientType":"Background","alObjectId":"840","component":"Dynamics 365 Business Central Server","executionTime":"00:00:05.7470000","sqlStatement":"DELETE FROM \"SQLDATABASE\".dbo.\"CURRENTCOMPANY$Cash Flow Forecast Entry$437dbf0e-84ff-417a-965d-ed2bb9650972\" WHERE (\"Cash Flow Forecast No_\"=@0)"}
See also
Monitoring and Analyzing Telemetry
Enable Sending Telemetry to Application Insights
Monitoring and Analyzing Long Running SQL Queries On-Premises
The Business Central Administration Center