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 query 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 Business Central Server configuration setting SqlLongRunningThreshold controls when a successful SQL query is logged as long-running in the Windows event log. In Business Central version 29, the default value is 750 milliseconds (ms). A query that takes at least the threshold is logged with a warning.
The separate setting SqlLongRunningThresholdForApplicationInsights controls the threshold for Application Insights telemetry. Its default value is also 750 ms. Changing one setting doesn't change the other.
Learn more about setting SqlLongRunningThreshold by using the Set-NAVServerConfiguration cmdlet in Configuring Business Central Server.
The cmdlet includes the -ApplyTo Memory parameter that enables you to change the setting without doing a server restart. For example, to change the event-log 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
Learn more about using the Windows event log in Troubleshooting: Using the Event Viewer to Monitor Long Running SQL Queries.
Learn more about setting up and using Application Insights in Enabling Application Insights for Tenant Telemetry.
Related information
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