Monitoring and Analyzing Long Running SQL Queries

Microsoft Dynamics NAV 2017 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 Business Central 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.

Defining the long running SQL queries threshold

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 Business Central Server 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 Business Central Server Administration tool, see Configuring Business Central Server.

You can also change the setting by Set-NAVServerConfiguration cmdlet in Business Central Administration Shell. 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 Business Central Administration Shell as Administrator and then type the following PowerShell cmdlet:

Set-NAVServerConfiguration -ServerInstance <ServerInstanceName> -KeyName SqlLongRunningThreshold -KeyValue 2000 -ApplyTo Memory

Monitor and analyze data

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.

See Also

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