Table locking and Slow performance

Make sure the database is configured in SQL with Auto Create Statistics,
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.

Just to give you a little back ground on the database I'm working on daily.
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!!

When microsoft changed sifts to indexed views, sql statistics became critical.

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.

bghubr wrote:

...Using the sql sp_createstats or sp_updatestats does not update stats on indexed views...

You can use UPDATE STATISTICS.

@bghubr: You make it out to be that the statistics is the one and only thing that will do any good for performance. This is undoubtedly caused by your system not having any updated statistics to begin with, and a dramatic increase in performance when they were updated. You will know better when new performance issues pop up and you are forced to investigate how to properly tune indexes. I'm happy that you have found one thing that works really well for you, but that does NOT mean that everything else does not work at all, regardless of what certain support people are telling you. There is more than one way of doing things, and reasons for choosing one over the other depending on the situation.

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:

Hi 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.
Thanks.

Tony_NCD wrote: »

Wow, what version of NAV are you even on? The size of the DB doesn't tell you everything for it. If, for example, your tables are storing images. A 100,000 records, which is a small amount, can easily throw your database into the hundreds of GB but that's meaningless because the number of records are still fairly small. Depending on your version of NAV, you might be able to see long running queries in your Windows event viewer logs. If you know which tables are slow, then see what fields are being used for filtering and see if you can create indexes to help speed it up. Last of all, locking and speed performance may be related but not the same thing. If you have locking issues, all the performance tuning won't do you any good.

Hi,

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)

den wrote: »

Hi,

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?