Problem Description: We migrated from NAV 5.0 Native DB to NAV 5.0 SQL 2008 DB a couple of weeks ago. From last few days we are experiencing performance issues with Navision. The process speed is slow (posting few lines takes lot of time) sometimes and users are often locked while doing the postings. We did some SQL maintenance (Index Rebuild and table optimization) but no real luck. Posting shipments and invoices is taking long time which in turn is blocking more users .During Peak Hours Situation is worse.
Any Suggestions how to handle this?
MVP - Dynamics NAV
We are using jet reports for reporting and some reports are run during working hrs as well.. Just wonder if Jet reports could cause table locks?
Many, too many for this forum. Contact you nearest NAV competent MS partner or freelancer to look into your problem.
Auto Update Statistics, Auto Update Statistics Asynchronously. These are more important than index tuning
for SQL. Index tuning helps but is not the final answer.
Next check sift indexes and disable maintain sift index for those not being used.
I disagree with that. Index tuning (having and using proper indexes) is probably the most crtitical performance issue. Statistics updates, while important, only help if they are associated with usable indexes. Updating statistics on indexes that are not used provide no value.
Proper hardware and configuration is also important.
We have 200 million g/l entries, 45 million item ledger entries, 89 million values entries
and over 250,000 items and post over 500,000 sales per day.
Our locking is minimal. Statistics has resolved most of our posting problems.
Yes indexing helps but SQL takes advantage of statistics more than indexing.
We rebuild indexes only once per week on these large tables and on a normal day
the indexes are around 60% fragmented and we don't lose any performance.
So I disagree with you even more!! We had a Microsoft SQL engineer here for 1 week
and worked with him on evaluating all of our indexes, sifts and code and it was found
that the database had the statistics turned off. Once we turned them on it was like day and
night. Good luck to you if you think indexes are the answer. LOL!!
RIS Plus, LLC
MVP - Business Apps
If you DO NOT have autostatistics turned on and auto update statistics turned on,
these indexed views will not have the statistics updated. Thus cause large performance issues.
Using the sql sp_createstats or sp_updatestats does not update stats on indexed views.
Only turning on autocreate and autoupdatestatistics will do this.
Thus you must turn them on if you want to take advantage and not lose performance on the sifts.
I never said that statistics are not important. But they are in context with good indexes. Statistics are built on indexed columns. If I have an index on column A with updated statistics, but my query uses column B, those stats won't hep. Also indexes won't help if their statistices are out of date.
You can use UPDATE STATISTICS.
Properly maintained statistics are absolutely crucial. In NAV databases though, because most queries are "SELECT *" queries, in the end you will end up with statistics on every single irrelevant column, with the auto options only updating samples, and this can cause enormous overhead. I've been in a number of situations where turning the auto options off, getting rid of all auto stats, and setting up proper statistics maintenance made a very big positive impact.
That's not to say that auto stats options are always bad, they are probably sufficient for most NAV implementations out there. It's just that for a lot of the ones that have big performance problems we've had very good results turning off the auto options and setting up 'manual' stats maintenance. The essential part here is that there are sufficient stats and that they are properly maintained. There are more ways than one to accomplish that.
The reality is that it's ALL important. Properly maintained statistics are important, so is the right number of indexes on the right columns, and the right frequency of rebuilding and reorganizing those indexes, with the right fill factors. It ALL has a significant role, and the challenge is to find the right balance.
I did not know about sp_updatestats not updating indexed views, I'll have to look into that, thanks :thumbsup:
RIS Plus, LLC
MVP - Business Apps
Am just curious here and doesn't know anything about tuning Databases. The topic and arguments are pretty useful for the likes of me, I want to know more and will be much appreciated if you would share your daily/weekly tuning on the database?
Thank you Guys!
The topic and arguments are interesting, we've also been experiencing this problem.
Our NAV database is almost 400GB and we don't have an expert to look at it.
Hope that this topic will be revived and some expert shed more light.
We've been using NAV 2018, previously our Company decided to delete the old company that has huge data. Our DB currently on 130GB but still encountered locking of tables, mostly of posting invoice and transfer order (ship and received)
Like I said, it's not necessarily the size of your database. However, it's interesting that you're on 2018 and are still experiencing this. Back in 2009 and 2013, I did see this quite a bit but not on latter versions. It's understandable that you may run into locking issues when posting because NAV has to lock tables to ensure it doesn't get corrupted by uncommitted data. Nevertheless, it can still happen if you have some customization that's putting extra stress on the posting process. Was this a fresh setup or a migration from an earlier version?