Kriki’s Dynamics NAV blog

Eric will probably not blog about this session for obvious reasons.

I am not writing everything he said or showed, only the things that are most important or something I didn’t know myself.

In general performance problems are created by the application (80-90%) and sometimes (10-20% by infrastructure).

The native server needs less heavy hardware than SQLserver but it has some severe limitations :
-32 bit and no 64bit.
-Memory for cache is limited to (in theory) 1GB.
-1 CPU. (my remark:this is not really a problem:the Navision server doesn’t need more).
I think the biggest problem of the Navision DB is the memory limit.

Things to be checked are CPU, disks, network, memory, server settings (most people forget this last one).
It is also best to put only 1 DB per server.
If you DB is over 5GB, it is best to divide the DB in parts of maximum 5GB. Each part is best put on a RAID1.
It is also possible to use RAID10. There are still a lot of discussions if RAID10 or RAID1 is better for the Navision DB.
My remark on that is:the Navision DB server has already his own striping between the DB-files, so why should you need another striping on the disks. Ok, you can put each 5GB-DB-file on a separate 6disk RAID10, but this is really overkill.
Don’t forget to use the commitcache and because of that, a UPS is mandatory.

The SQL DB:
-things to check are memory, CPU, disk subsystem, server settings.

Remember also this equation: memory versus disk = nano seconds versus milli seconds!
If you go over 4GB of memory, you should choose 64bit. You avoid a lot of things with the boot.ini and AWE in SQL.
SQL2000 uses the extra memory (over 4GB) ONLY FOR DATA. Plancaching, locking is still memory-limited.

Eric made also a beautiful table displaying when to use the different options (/3GB /PAE AWE). I didn’t manage to copy it. But don’t worry, his slides will be downloadable. When I know where they are, I will add the link in a comment.

If you are on 64bit and have LOTS of memory and spare processing power, you can use the option “Always Rowlock” in Navision.

The SQL server account also needs the “Lock Pages in memory” privilege to avoid that the OS decides to swap some pages of SQL to disk.

CPU: At least with the current technology this does NOT pose a problem anymore (at least for NAV DB’s on SQL).
It is better to use 2 dualcore processors instead of 1 quad core: if one processor fails, you can still work.

You need a different spindles for different functions (DB,Transaction log, OS+swap file). Don’t even THINK about sharing some drive to put something else on it, not even the backup!
SQL2000 doesn’t use a lot the tempDB, so you can put it together with the OS. With SQL2005 it is better to give it it’s own spindle.

No RAID5!

I repeat: NO RAID5!!

I repeat again: NO RAID5!!!

…

Did I already write this? NO RAID5!!!!!!

Gain performance (=more disks) is opposite to gain capacity (=bigger disks)
A lot of smaller drives is better than few big drives. It is the number of spindles that count, not the capacity.

I don’t remember if I already told you : NO RAID5!!!!!!

Settings on SQL server:
-MDOP (=Max degree of parallelism)=1. Otherwise it can happen that users lock themselves out. But in general you will gain performance if 1 processor serves 1 request for Navision. Navision sends simple commands to SQL.
-Auto-options (auto close, auto shrink, auto create statistics, auto update statistics): Put them to NO.
But you need to calculate the statistics daily. So create a job to do that.

Terminal services:
-The biggest advantage is easy maintenance, specially for updates.
-a 1GBit network to the DB-server is a must!

NAS:
-Any PC you can buy is good enough. After all it is a client without GUI. At least if the NAS does only NAV-things.

Network:
-1GBit backbone
-clients : 100 Mbit
-Dont use HUBS! I thought this was obvious but Eric said he still found them at some customers! Like Homer would say: Doh!

NAV2009:
-You can only use the 3-tier with SQL-server
-Put the middle tier on its own box. Probably it will need good processing power but little disk power.
-But it is still a big unknown.

Some remarks:
-Best use SAS-disks
-You need the correct software+hardware: e.g. 64bit SQL (best SQL2005) on WIN 64bit on 64bit hardware.
-Remember you have fat clients, so don’t give the warehouse a copy of the ENIAC.

Filed under: DirectionsEMEA2008, NAVISION-DB, SQL