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]
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.
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.
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Blog - rockwithnav.wordpress.com/
Twitter - https://twitter.com/RockwithNav
Facebook - https://facebook.com/rockwithnav/
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.