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

tomguan

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_"=@0 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

HatchetHarry

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_Guzek

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.

RockWithNAV

Did you tried checking whats going on in SQL Profiler?

tomguan

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

jordi79

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_Hollender

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/