Long Running SQL Statement NAV 2017

ddimitrov94

Hi everyone,
There is this custom module(50+ tables, 100+ pages) we've developed for our Customer which is causing some Warnings in the Event Viewer.
I was wondering is there a way to catch the exact moment of some of the "Long running SQL statements" taking place?
For example there is this one:

Category: Sql
ClientSessionId: 738df2e7-d27d-4fbc-b250-015d77bfcd35
ClientActivityId: 7d1165cf-3928-44cc-b1f4-41acd37af304
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 02/04/2019 16:02:44
Message <ii>Threshold Trace Tag. Execution Time = 1041 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP (50) ISNULL("Rental Contract Line"."timestamp",@4) AS "timestamp",ISNULL("Rental ......

And underneath is about 70 rows of JOINs, WHEREs and etc.There are a lot of FlowFields in this module.

There is another one:

Category: Sql
ClientSessionId: cfb3d25d-6c98-47b6-ac08-28f6f3a2ae6c
ClientActivityId: 3b16a39e-6f89-4a34-bdf6-776e60e3ae66
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 02/04/2019 15:22:50
Message <ii>Threshold Trace Tag. Execution Time = 3611 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP (50) ISNULL("Rental Contract"."timestamp",@1) AS "timestamp",ISNULL("......


Aaaand another one:

Category: Sql
ClientSessionId: cfb3d25d-6c98-47b6-ac08-28f6f3a2ae6c
ClientActivityId: 3b16a39e-6f89-4a34-bdf6-776e60e3ae66
ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000
ServerActivityId: 00000000-0000-0000-0000-000000000000
EventTime: 02/04/2019 14:35:36
Message <ii>Threshold Trace Tag. Execution Time = 2845 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP (1) ISNULL("Rental Item"."timestamp",@0) AS "timestamp",ISNULL ......

All of the above start with different table. They occur at different moments, for example today there are 20-30, that makes me think that there is something we can improve. So is there a way to catch at which moment this statement is running - When the user open page, when a button is being pressed and etc. Or i just have to go through the whole process and after each click to check the Event viewer (already did this one with no success).

Regards,
Dimitar

Juhl

Be aware of the NST cache.
First read from a table reads from SQL, next read from memory. So it’s a fun task finding the culprint.
But flowfields and keys are good place to start.

I use SQL Profiler when looking.

Juhl

Be aware of the NST cache.
First read from a table reads from SQL, next read from memory. So it’s a fun task finding the culprint.
But flowfields and keys are good place to start.

I use SQL Profiler when looking.