| title | description | ms.custom | ms.date | ms.reviewer | ms.service | ms.topic | author |
|---|---|---|---|---|---|---|---|
Monitoring Long Running SQL Queries to the Event Log |
This topic provides an overview on how to monitor long running SQL queries in the event log starting with NAV 2017. |
bap-template |
04/01/2021 |
jswymer |
dynamics-365-op |
conceptual |
jswymer |
[!INCLUDEnav2017] was the first version that allows long running SQL queries to be logged to the Windows Event Log. The queries are logged when the application communicates with the database and the call to the database takes too long. Starting in [!INCLUDEprod_short] 2019 release wave 2, long running queries can also be emitted as telemetry to Microsoft Azure Application Insights. Using Application Insights requires that you first enable it on your tenant.
The time logged when a SQL query runs is the time spent on the called database as seen from the server. There are multiple reasons that can cause a delay. For example, a delay happens when the database waits for a lock to release. Or it runs an operation that's missing indexes.
The threshold of when a SQL query is considered to be long running is controlled by the [!INCLUDEserver] configuration setting SqlLongRunningThreshold. The default value is 1000 milliseconds (ms). By default, the threshold is set to 1000 milliseconds. In this case, if a SQL query runs longer 1000 ms, a message is recorded in the event log and emitted as telemetry. The message indicates that the action took longer than expected or longer than the given threshold. For more information about setting the SqlLongRunningThreshold by using [!INCLUDEadmintool], see Configuring Business Central Server.
You can also change the setting by Set-NAVServerConfiguration cmdlet in [!INCLUDEadminshell]. The cmdlet includes the -ApplyTo Memoryparameter that enables you to change the setting without doing a server restart. For example, to change the threshold dynamically to 2000 ms, run the [!INCLUDEprod_short] Administration Shell as Administrator and then type the following PowerShell cmdlet:
Set-NAVServerConfiguration -ServerInstance <ServerInstanceName> -KeyName SqlLongRunningThreshold -KeyValue 2000 -ApplyTo Memory
To use the Windows Event Log, see Troubleshooting: Using the Event Viewer to Monitor Long Running SQL Queries.
To set up and use Application Insights, see Enabling Application Insights for Tenant Telemetry.
Troubleshooting: Using the Event Log to Monitor Long Running SQL Queries
Troubleshooting: Analyzing Long Running SQL Queries Involving FlowFields by Disabling SmartSQL
Monitoring and Analyzing Telemetry
Set-NAVServerConfiguration
Tools for Monitoring Performance Counters and Events
Monitoring Business Central Server Using Performance Counters
Monitoring Business Central Server Events