NAV 2017 - Session Event Table > Long Running SQL

nick_robbo

Hi,

We have recently upgraded to NAV 2017, and are having a few issues with the Task Scheduler.
We are seeing a lot of errors/warnings like this in the Event Logs

Message <ii>Threshold Trace Tag. Execution Time = 7398 milliseconds Long Running SQL statement Task ID:1 DELETE TOP (1000) [dbo].[Session Event] WITH(TABLOCKX) WHERE NOT EXISTS (SELECT 1 FROM [dbo].[Active Session] AS acs WHERE acs.[User SID] = [dbo].[Session Event].[User SID] AND acs.[Server Instance ID] = [dbo].[Session Event].[Server Instance ID] AND acs.[Session ID] = [dbo].[Session Event].[Session ID] AND acs.[Session Unique ID] = [dbo].[Session Event].[Session Unique ID] AND acs.[Login Datetime] = [dbo].[Session Event].[Event Datetime]) AND DATEDIFF(Month, [Event Datetime], GETUTCDATE()) >= @ROWCOUNT&lt;/ii>

As I understand it, it is trying to delete entries from the table "Session Event". I presume that this is for the Task Scheduler. We are seeing some performance issues, and often Job Queue entries which are "Scheduled" are not run. I am just wondering if anyone else has encountered this before? Or has any fixes?

nick_robbo

Turns out the answer was to remove a large amount of entries from the Session Event table (Around 3-4 million). This was making the Inserts/Deletes for Task Scheduler very slow.

kalyankandimalla

Hi Nick,

I found below link, can you please check and see if this helps you.

https://robertostefanettinavblog.com/2016/11/04/nav-2017-task-scheduler/

kennie

Hi Nick,

Which CU are you running? I believe that we have fixed this in a CU early this year

Regards,
Kennie Nybo Pontoppidan

nick_robbo

kennie wrote: »

Hi Nick,

Which CU are you running? I believe that we have fixed this in a CU early this year

Regards,
Kennie Nybo Pontoppidan

We are running CU05, we do have a lot of Job Queue Entries, and some run a custom Workflow Engine. These run every few seconds so I can undertand why the Task Scheduler would need to interact with the Active Session/Session Event table. It doesn't really look like a big problem, but it would be nice to understand why it is happening.

nick_robbo

Turns out the answer was to remove a large amount of entries from the Session Event table (Around 3-4 million). This was making the Inserts/Deletes for Task Scheduler very slow.

soetie

we have the same issue here. I cant find where the problems are. But very frequent logging in the eventlog about long running queries

systemIO

Same here but another statement:

SELECT ISNULL(COUNT("Sales_Header"."Document Type"),@1) AS "Count_Orders" FROM "CosmoMigTest1".dbo."KRAL AG$Sales Header" AS "Sales_Header" WITH(READUNCOMMITTED)  OUTER APPLY (SELECT TOP (1) @2 AS "Shipped$Sales Line$EXISTS",ISNULL("Shipped$Sales Line"."Document Type",@3) AS "Shipped$Sales Line$Document Type",ISNULL("Shipped$Sales Line"."Document No_",@4) AS "Shipped$Sales Line$Document No_",ISNULL("Shipped$Sales Line"."Line No_",@1) AS "Shipped$Sales Line$Line No_" FROM "CosmoMigTest1".dbo."KRAL AG$Sales Line" AS "Shipped$Sales Line" WITH(READUNCOMMITTED)  WHERE ("Shipped$Sales Line"."Document Type"="Sales_Header"."Document Type" AND "Shipped$Sales Line"."Document No_"="Sales_Header"."No_" AND ISNULL("Shipped$Sales Line"."Qty_ Shipped (Base)",@5)<>@6) ORDER BY "Shipped$Sales Line$Document Type" ASC,"Shipped$Sales Line$Document No_" ASC,"Shipped$Sales Line$Line No_" ASC) AS "SUB$Sales_Header$Shipped"  OUTER APPLY (SELECT TOP (1) ISNULL(MIN("Completely Shipped$Sales Line"."Completely Shipped"),@7) AS "Completely Shipped$Sales Line$MIN$Completely Shipped" FROM "CosmoMigTest1".dbo."KRAL AG$Sales Line" AS "Completely Shipped$Sales Line" WITH(READUNCOMMITTED)  WHERE ("Completely Shipped$Sales Line"."Document Type"="Sales_Header"."Document Type" AND "Completely Shipped$Sales Line"."Document No_"="Sales_Header"."No_" AND ISNULL("Completely Shipped$Sales Line"."Type",@3)<>@8)) AS "SUB$Sales_Header$Completely Shipped"  OUTER APPLY (SELECT TOP (1) @9 AS "Late Order Shipping$Sales Line$EXISTS",ISNULL("Late Order Shipping$Sales Line"."Document Type",@3) AS "Late Order Shipping$Sales Line$Document Type",ISNULL("Late Order Shipping$Sales Line"."Document No_",@4) AS "Late Order Shipping$Sales Line$Document No_",ISNULL("Late Order Shipping$Sales Line"."Line No_",@1) AS "Late Order Shipping$Sales Line$Line No_" FROM "CosmoMigTest1".dbo."KRAL AG$Sales Line" AS "Late Order Shipping$Sales Line" WITH(READUNCOMMITTED)  WHERE ("Late Order Shipping$Sales Line"."Document Type"="Sales_Header"."Document Type" AND "Late Order Shipping$Sales Line"."Sell-to Customer No_"="Sales_Header"."Sell-to Customer No_" AND "Late Order Shipping$Sales Line"."Document No_"="Sales_Header"."No_" AND ISNULL("Late Order Shipping$Sales Line"."Shipment Date",@10)>=@11 AND ISNULL("Late Order Shipping$Sales Line"."Shipment Date",@10)<=@12 AND ISNULL("Late Order Shipping$Sales Line"."Outstanding Quantity",@5)<>@13) ORDER BY "Late Order Shipping$Sales Line$Document Type" ASC,"Late Order Shipping$Sales Line$Document No_" ASC,"Late Order Shipping$Sales Line$Line No_" ASC) AS "SUB$Sales_Header$Late Order Shipping"  WHERE ("Sales_Header"."Document Type"=@0) AND (ISNULL("Sales_Header"."Status",@3)=@14 AND ISNULL("SUB$Sales_Header$Completely Shipped"."Completely Shipped$Sales Line$MIN$Completely Shipped",@7)=@15 AND ISNULL("SUB$Sales_Header$Late Order Shipping"."Late Order Shipping$Sales Line$EXISTS",@16)=@17) OPTION(OPTIMIZE FOR UNKNOWN, FAST 50, FORCE ORDER, LOOP JOIN)

RemkoD

@systemIO, this topic is about the same error too: link.

systemIO

@RemkoD Thank you for the link