NAV 2017 - Session Event Table > Long Running SQL

nick_robbo
nick_robbo Member Posts: 49
in NAV Three Tier
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?

Best Answer

  • nick_robbo
    nick_robbo Member Posts: 49
    Answer ✓
    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.

Answers

  • kalyankandimalla
    kalyankandimalla Member Posts: 56
    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
    kennie Member Posts: 34
    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
    nick_robbo Member Posts: 49
    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
    nick_robbo Member Posts: 49
    Answer ✓
    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
    soetie Member Posts: 62
    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
    systemIO Member Posts: 5
    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
    RemkoD Member Posts: 100
    @systemIO, this topic is about the same error too: link.
  • systemIO
    systemIO Member Posts: 5
    @RemkoD Thank you for the link :)

Categories