ISEMPTY create SQL query run over 10 sec, and keep happening. How to tuning up

tomguantomguan Posts: 14Member
Recently our customer keep get table locking issue, and from NST event viewer, we found following information:
Message <ii>Threshold Trace Tag. Execution Time = 10010 milliseconds
Long Running SQL statement Task ID:3
SELECT TOP 1 NULL FROM "xxxxx_$Job" WITH(XLOCK) WHERE ("No_"[email protected] AND "timestamp"=@1) OPTION(OPTIMIZE FOR UNKNOWN)</ii>

This query should just query job table to check if No. exist. (Not sure timestamp part), but run over 10 seconds. We are verifying with SQL team to see if they have reindex. Any other suggestion? Seems we got more and more this issue every few days.

Appreciate all help.

Thanks
tom

Comments

  • HatchetHarryHatchetHarry Posts: 24Member
    You can check the Execution Plan to see what it is doing if you execute the Query in SQL Management studio. And add indexes based on that result.
  • Slawek_GuzekSlawek_Guzek Posts: 1,684Member
    edited 2019-03-30
    This must be due to locking (the exclusive lock) requested, as the primary key on the standard Job table is the "No." field, and the table (unmodified) is clustered along the PK, therefore SQL would use a seek operation to get the result, which is very quick.

    Look into NAV code an check if there is a LOCKTABLE on the Job rec var somewhere before ISEMPTY, and get rid of it. Or use/add a separate local Job rec var, do COPY from the original one to get all the filters, and then call ISEMPTY on it.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • RockWithNAVRockWithNAV Posts: 905Member
    Did you tried checking whats going on in SQL Profiler?
  • tomguantomguan Posts: 14Member
    Thanks all for your reply. I will try to verify those points and update ticket. One challenge is SQL control by other IT team, we do not have direct access. Will coordinate with them to review what's we can do for this locking and update here to let everyone know what we found.

    Thanks again
    Tom
  • jordi79jordi79 Posts: 184Member
    follow the guide in -->
    https://docs.microsoft.com/en-us/dynamics-nav/installation-considerations-for-microsoft-sql-server.

    This will prevent NAV from escalating locks to table locks unnecessarily. If you do not follow the steps mentioned, (or use SQL db out of the box), SQL has a tendency to escalate locks from record to whole table locks. And this will lock out a whole table. The downside to this is that record locks consume more resources. But this is something that can be solved by upgrading hardware.

    And update the statistics if the indexes are fragmented. Updating the DB statistics ensures that the query execution plan is optimised. FAster query means less lock time, equals better performance.

    good luck.
  • Miklos_HollenderMiklos_Hollender Posts: 1,591Member
    I must say, since we are using solid state disks, we never had problems like that. Didn't do any such SQL performance tuning or installation settings. So maybe that would be the easiest fix. Freddy is recommending this for like 11 years, by now they are almost a standard. https://blogs.msdn.microsoft.com/freddyk/tag/SSD/
Sign In or Register to comment.