Navision 3.7 vs Dynamics NAV 4.0SP3 - Performance

nEWvision

Hi

My client is facing Table Locking problem with 200 users and 80 GB database. The database is running on SQL 2005 and Navision 3.7

I would like to know if 4.0 gives us some performance benefits and how?

Regards
Newvision

Marije_Brummel

You can better upgrade to 5.0. In this version a lot of indexes are changed for better SQL Performance.

Off-course this is done to work for all customers and you might need other indexes.

If a complete upgrade to 5.0 is out of the question due to the amount of work a runtimeupgrade to 4.0SP3 is the best way to go. In that case you will need to create your own indexplan.

Try to find the SQL Perform downloads on this website. This webcast explains how to do this.

A good tip for locking is to change the clustered index of the Sales, Purchase and/or manufacturing tables. In standard NAV this starts with an option value. Try to create a new index with a more selective order and make this clustered. Best is to make the new on index the 2nd line from above.

Another best practice is to reduce the number of fields in an index. NAV has a lot of long indexes because of the fact that reports require it for sorting. Since SQL can sort without an index you can make indexes smaller and more selective withour destroying the functionality.

At last but not least; Reduce SIFT to a bare minimum. Every sift level has the impact on performance of at least 2 indexes and cause a lot of locking. Out of the box every SIFT level is active.

For example: If you have a SIFT level on Item No. in the Item Ledger Entry table with some sumindexfields, every update on that Item No. has to update that record in the SIFT table. This way Purchase and Sales people can be locked by each other.

Good luck.

nEWvision

Hi Mark,

Thanks for the reply. This is really helpful. But I was curious to know if Microsoft has done some changes in the version 4.0SP3 to improve the performance or remove the table lockout problem.
If I simply upgrade to 4.0SP3 with out doing the Database Finetunning / Creating the Index plan, will it improve the performance / resolve the table lockout?

Best Regards
Newvision

Waldo

I know that Microsoft has put some work in index tuning on 5.0 (not 4.0!!).
On code level, they have implemented the new FIND-options a huge number of times in 5.0.

So I think it's better to go to 5.0 (if your country has a localized version)...

Marije_Brummel

Or you can do it the Waldo© way. Copy pieces of 5.0 into 4.0 where you think your problem is.

Waldo

Yeah ... well ... it was worth trying, because we had very positive results.

nEWvision

Can you please suggest if there is some material from microsoft giving performance comparison between 3.70 and 4.0SP3?

Marije_Brummel

If there is any, it is not reflecting the real world.

MS claims that SQL2005 is faster that SQL2000 but we experience the other way around.

When MS does benchmarking they use some tools to regenerate the same entries over and over again on perfect hardware.

Reading your orriginal post again I am confused. You claim to be running 3.7 with SQL2005 but SQL2005 is only supported from 4.something.

What runtime are you on?

David_Singleton

nEWvision wrote:

Hi Mark,

Thanks for the reply. This is really helpful. But I was curious to know if Microsoft has done some changes in the version 4.0SP3 to improve the performance or remove the table lockout problem.
If I simply upgrade to 4.0SP3 with out doing the Database Finetunning / Creating the Index plan, will it improve the performance / resolve the table lockout?

Best Regards
Newvision

Basically NO. To get performance benefits out of the 4.00SP3 executables, you will need to do some database tuning. But if you do that tuning, then there are a lot of benefits.

Quite definitely you can do a lot more with the 4.00sp3 executables than with 3.70B.

But also note that you can also gain a lot of performance improvements by tuning your existing database. And just putting in some good maintenance plans (like those in the SQL Perform tool kit) will give good results.

colin_leversuch-roberts

we had problems going from 3.7 to 4.0 sp2 - the FAST option in queries cause lots of issues so sp3 solves that - there are also some user creation issues with sp2 solved with sp3.
You also need to make sure you have sp2 + at least rollup 3179 on sql server.

Waldo

A small thing: put your security model to "standard" to have the same as your 3.70 database.

DenSter

Upgrading your exe's only will not give you the performance increase that you're looking for. You will also need to implement a number of things:
1)
Proper database maintenance on your SQL Server (for which the aforementioned SQL Perform perform-tools are an excellent option).

2)
With your database usage (200 users is quite a few users) you are most likely also looking at some sort of performance tweaking, in the form of index tuning, eliminating SIFT levels.

3)
Maybe even a review of your most affected business processes and a code level review. Since this is the most time consuming step, with the least predictable outcome, this should not be done until last, unless of course you absolutely know that something is wrong in some processes. Going from 3.70 to 4.0 later SP or 5.0, there is quite a lot of code that can be reviewed for better performance.

Also, let's not forget that your hardware may need to be looked at, just to make sure you have the right setup. For instance if you have all of your database files, including log files, on the C drive, on a RAID 5 storage, then that is a very likely candidate to cause problems.