Lazy Navision?

Theo_Goliath

Hi all,

I'm just entering the world of Navision, and hence this forum, having been a Windows system administrator since 1996 and now broadening my horizon.

I'm currently working on migrating a Navision native db install to SQL. For this i have to backup the db in the native client and restore it with the sql client. I'm also running checkfields.fob that i found in the Mibuso downloads. All great so far... but i notice Navision seems to be 'lazy', as in it's not performing very fast in these steps.

I'm running this on a freshly installed VMWare VM with Server 2008 R2, 4 separate RAID 10-sets of 10k and 15k rpm SAS disks and SQL Server 2008. The VMWare host has plenty of CPU power (currently running on a quad core system, planning on migrating to an 8-core server), RAM and is not heavily loaded otherwise.

What i see is backup taking long (about 2 hours for a 36GB native db), restore taking even longer (6+ hours), running the checkfields job takes a long time too.
What i also see is one processor core being utilized (fin.exe and finsql.exe are single-threaded processes) half or full, the other 3 doing nothing much. Disk I/O seems stuck between 3 and 6 MB/s for all the actions mentioned above. This seems ridiculously low as those disk sets can easily write up to 500 MB/s and read up to 800 MB/s on sequential data/large file copies (i've seen them do it and also ran some benchmark tests when i installed the server). Partitions have been aligned, formatted with 64k blocks, i'm running on high performance power profile, SQL server settings have been tweaked as per recommendations in a document i found here. It all doesn't seem to make any difference. fin.exe and finsql.exe just don't seem to use all the power i offer them. I tried raising the process priority too. Got some more speed in SQL restore when i set SQL server network packet size higher, but still the overall performance is way slower than it should be.

What can i do?

mdPartnerNL

The native NAV server is using 1 cpu.

Theo_Goliath

mdPartnerNL wrote:

The native NAV server is using 1 cpu.

I know. But can i force it somehow to at least use that one core (i think that is what you mean) to its max?
Can i tweak the system so that that one process on that single core at least makes uses of the full bandwidth of the disks and other i/o?

The screen shot below was taken without using the server.exe process, the (single) client directly opens the native database (which consists of 3 files, each file gets its own instance of slave.exe) but the performance is still way below what it should be.

Any advice would be welcome.

mdPartnerNL

You can splitup your db into more files and use caching (search for this in the older posts) or upgrade to 2013

Theo_Goliath

mdPartnerNL wrote:

You can splitup your db into more files and use caching (search for this in the older posts) or upgrade to 2013

Upgrade to 2013 would not be so simple as we have had huge amounts of customization done.

If we continue running on native database i will try splitting the db up into more files.
But will Navision on SQL actually perform better? We're investing a lot of time and resources for this conversion, i want to make sure that somehow it pays off..

jglathe

Hi,

Usually it performs better than native with many records, but it depends a little on the version and how the keys are optimized. Your hardware seems to be a little on the overkill side though... Anyway, i would recommend to use the 2009R2 version. Suboptimal key configuration can be quite a pain too. The restore run is not symptomatic for the overall performance, it is quite slow. What you can do is set MAXDOP to number of cores -1, and the parallel threshold low ( not zero). This speeds up key generation a little. Also, enabling page compression for all tables keeps more tables in memory, increasing throughput. But that's all bot much, optimizing keys beings the most out of it. We' running on a SQL Server 2005 with 8 cores, 32GB and SAN for 100 users, and it's resonably responsive, also for calculations over >5M entries. CPU load is very low, though. It increases a little with more users, but not much.

with best regards

Jens

kriki

jglathe wrote:

Also, enabling page compression for all tables keeps more tables in memory, increasing throughput.

But only if you have Enterprise Edition.

BTW: it is also better to have SQL2008R2 instead of SQL2008. And having the 64-bit-version of it!


As for the NAV client doing all those checks and not using all CPU:
-when the C/AL code is compiled, it is NOT converted to pure machine code but to a intermediate language that can be more easily interpreted by the fin.exe (or finsql.exe) executable. This makes it quite slow and most of the time it is CPU-bound (and uses only 1 core). If you have also some DB-access and network traffic, it is possible 1 client is not even using its core at 100%.
This is something you cannot improve (apart of running the client directly on the server and having at least 900MB db-cache and the COMMIT-cache enabled.

The result is that only 1 NAV-client will NEVER use the max out of SQL server (or you need to give your SQL server only 1GH or less of clockspeed...). Even with a lot of clients, SQL Server will use quite little CPU-power because the SELECTS NAV sends to SQL are on only 1 table (NAV2013 is diffferent) with optionally a WHERE on some fields and an ORDER BY.
You might speed up the checking progress by dividing the workload. You need to reprogram the checking process so multiple clients can be started and each do a part of the job. In general this is not so easy.

Native backup/restore is best avoided because of its slowness. It does have some advantages: you can also backup just 1 or some companies or the intercompany-data. This is not possible with SQL Server.
When doing the conversion, you can only speed it up if you have multiple companies. Backup a company and start restoring that company. In the mean time you can backup the next company. But BEFORE doing this, you need to backup/restore the objects and the intercompany-data in a newly created database where you didn't do anything yet!