Table locking and Slow performance

vijay4398

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?

kine

I recommend to contact someone who understand ho to solve performance problems of NAV on SQL. The souce of your problms could be more things, from HW through wrong index t wrong app design.

vijay4398

Hi Experts ,

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?

Any Idea?

rhpnt

vijay4398 wrote:

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?

Many, too many for this forum. Contact you nearest NAV competent MS partner or freelancer to look into your problem.

bghubr

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.

bbrown

bghubr wrote:

...Index tuning helps but is not the final answer...

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.

bghubr

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!!

DenSter

bghubr wrote:

Yes indexing helps but SQL takes advantage of statistics more than indexing.

Actually it's the other way around

bghubr

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.

bbrown

The database I work with often is not quite as large as yours but still larger then a typical NAV system. (140 million GL, 72 million VE - and growing).

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.

bbrown

bghubr wrote:

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

You can use UPDATE STATISTICS.

bbrown

The database I mentioned above is 300+ GB. "Auto everything" is turned off. Indexes and statistics are updated nighty with T-SQL scripts. Adjust Cost over the past two nights has posted 70,000+ adjustments at an average of 1700+ per minute. Not seeing performance issues.

DenSter

@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:

manthonylugo

Hi Experts,

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!

den

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

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.

den

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)

irasoelbaks

If the problem can be reproduced easy you can perform a trace with the SQL Server Profiler to identify if there a slow running queries. Please notice that performance problems can vary wide from software, hardware, limits, settings, indexes, C/AL Code, Nav platform, user behaviour etc.

Tony_NCD

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?