Edit

Share via

Analyzing Long Running Operation (SQL Query) Telemetry

  • Article

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)"}

Monitoring and Analyzing Telemetry
Enable Sending Telemetry to Application Insights
Monitoring and Analyzing Long Running SQL Queries On-Premises
The Business Central Administration Center