Monitoring Long Running SQL Queries using the Event Log

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

Defining Long Running SQL Queries

The time logged in long running SQL queries is the time spent on the called database as seen from the server. There are multiple reasons that can cause this delay, such as the database waiting for a lock to be released, or the database executing an operation that perfoms badly due to missing indexes. The threshold of when a query is logged is controlled in the configuration value of the SqlLongRunningThreshold key. The default value is 1000 milliseconds (ms). For more information about SqlLongRunningThreshold, see Configuring Microsoft Dynamics NAV Server, database settings section.

Changing Configuration Values

With Microsoft Dynamics NAV 2018, some of the configuration values for the server can be changed in the memory of the server, without doing a server restart. To change the threshold dynamically to 2000 ms, run the Dynamics NAV Administration Shell as Administrator and then type the following PowerShell cmdlet:

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

See Also

Troubleshooting: Using the Event Log to Monitor Long Running SQL Queries in Dynamics NAV
Troubleshooting: Analyzing Long Running SQL Queries Involving FlowFields by Disabling SmartSQL
Configuring Microsoft SQL Server
Set-NAVServerConfiguration
Tools for Monitoring Performance Counters and Events
Monitoring Microsoft Dynamics NAV Server Using Performance Counters
Monitoring Microsoft Dynamics NAV Server Events