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

Comments

• Options

  HatchetHarry Member Posts: 24

  2019-03-29

  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.

  0
• Options

  Slawek_Guzek Member Posts: 1,690

  2019-03-30 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

  0
• Options

  RockWithNAV Member Posts: 1,139

  2019-03-30

  Did you tried checking whats going on in SQL Profiler?

  Thanks
  
  Blog - rockwithnav.wordpress.com/
  Twitter - https://twitter.com/RockwithNav
  Facebook - https://facebook.com/rockwithnav/

  0
• Options

  tomguan Member Posts: 14

  2019-04-01

  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

  0
• Options

  jordi79 Member Posts: 272

  2019-04-02

  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.

  0
• Options

  Miklos_Hollender Member Posts: 1,598

  2019-04-10

  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/

  0