NAV 2017 CU10: Event 705 Long Running SQL statement

vaprog

Hi
I have a demo setup on my client of NAV 2017 CU10. NST, SQL, Client, Dev. Environment all local; demo database with CRONUS company.

My system regularly logs the following event

Server instance: DynamicsNAV100
Category: Sql
ClientSessionId: 00000000-0000-0000-0000-000000000000
ClientActivityId: 00000000-0000-0000-0000-000000000000
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 03/16/2018 15:00:07
Message <ii>Threshold Trace Tag. Execution Time = 1259 milliseconds
    Long Running SQL statement Task ID:9
    </ii>
ProcessId: 6296
Tag: 000007S
ThreadId: 18
CounterInformation:

The event is logged every 30 seconds + Execution Time, as stated in the event message.

I cleared out a stale entry in the Active Session table, that shipped with the demo database. The Session Event Table, that is frequently named in connection with that event contains about 30 to 50 records.

What might be causing this event and how do I stop NAV from spamming my event log?

RemkoD

Personally I think the default set threshold is a bit too low. You can set the threshold higher in the NST configuration file:

<add key="SqlLongRunningThreshold" value="1000" />

Usually the warning contains the query that is running long. Do you have anything like that in the warning? That would be helpful to further analyze what is causes the warning.

You could use the SQL Profiler on the database to check for long running queries too.

Slawek_Guzek

One of the reasons I can think of is locking. It does not seeem to make much sense in demo environment like yours to blame locking but maye you have Task Scheduler enabled, or some web services connections, and the environment is slow - maybe this all together adds up to slow Active Session queries down to the point where NST records long running queries.

vaprog

Thanks for your replies.
I did not know about the configuration option. That should help me to silence the NST in case the cause cannot be found.

I mentioned, that my entries occur regularly (i.e. 30 seconds after after the last he next one begins).
I did not mention

• The event is logged even if the NST is (supposed to be) idle
• The event is logged even if no one is connected
• no one other thy I connects to the service (according to Session Events table)
• There are two jobs in the Job Queue, each running once every 24 hours, running for some milliseconds. One of them probably is on the edge of triggering the event, but can do so only every 24 h not every 30 s

RemkoD

@Slawek_Guzek, True. The fact that a queries duration is above the 1000ms it doesn't necessarily mean the query it self takes that long to process. There could be other reasons the duration for that query is over 1000ms, like waiting for a resource.

@vaprog Because your warning doesn't contain IDs from the client or server sessions I guess it is caused by queries launched from the NST session self.

Can you make a trace with SQL Profiler and check if any odd query pops up every 30 sec? I did a quick trace on a local NST myself, only micro queries are executed from the idle NST. Nothing worth to mention and certainly nothing close to the 1000ms duration time.

Does your system have limited resources or is it overwhelmed with other tasks? In that case long running queries would occur easier.

Easiest solution is to higher the threshold to 2000ms for example. That way you will still be informed about long running queries without flooding the event viewer application log.

Slawek_Guzek

vaprog wrote: »

I mentioned, that my entries occur regularly (i.e. 30 seconds after the last he next one begins).

If that's the case the culprit is very likely to be the SQL Server Express and its Auto Close database feature. One of the differences between SQL Server Express and other versions is that it quickly releases the resources. This article may shed some light