Performance Experience with SATA for NAV on SQL DB (was: Navision DB)

Thanx Jorg …Thanx a ton for this as we are on the edge of it due to performance issues…these suggestions would definitely help…from the hardware prospective I think I am doing pretty well …coz Mitesh & myself have done extensive test and reached to a conclusion that hardware is not an issue…the issue lies in the processes which is taking a hell lot of time …like batch process which accesses G/L Entry and other Entry tables which are like 22 million records in each of them…Its seems like a never ending story.

Hi Jörg, David
Jörg we are now implementing the Dimension tweak u mentioned and currently working on the same.
Keeping my fingers crossed.

But ther is more to my problems…I need to run the costing Batch at any cost currently
we have 17000 SKU’s to be processed.
We arent able to run the costing Batch Process since August and our financials are in a toss.
Is there any other way that I can run the cost batch on a different server and then merge it with the existing one.
I know this is absurd but I need to run it by hook or by crook. We had taken a downtime to run the cost batch for 8 hours and managed to process only 800 SKU’s. This is a nightmare for us.

I also doubt my architecture.
Currently we have Navision Client installed on the SQLServer 2005 database on x64 Server.
We have no 3 tier application. We have no Navision Server installed and everything is in one box.
All users accesses the server directly. Is this setup a hinderance.

I mean to say do we need to change the architecture.
Like one of my guys told me that isolate the SQLServer and
we should have multiple Navision Servers clustered with one Virtual IP connecting to the SQLServer
and have all users connect to this IP we would expereince huge difference in terms of performance.
This is something like Load Balancing or you could say implementing an Application Delivery Control Services (ADCS).

Will this work ? Please advice.

Thanx & Regards,

I’m afraid, this is not easily possible. I’ve seen solutions, which have modified the costing batches to be split in “chunks”, so that they could process a selection of Items per day … unfortunately I don’t know the details about this …

Firstly, you should generally not run 32bit applications on a 64bit machine - this could slow down the whole system remarkably!

Secondly, a Database Server should always be dedicated! Especially do NEVER run services on it like IIS, Domain Controller, Print/Spool Server, etc. or abuse it as File-Server!

The NAV Client on the Server should only be used for temporary purposes, e.g. to look up things or add a new user, etc… Do not use it permanently, e.g. the Job Scheduler etc…

Have in mind, that NAV Clients or NAS need to have sufficient system-resources to process the data! The “Hardware Guide” simply says “Windows requirements = NAV requirements”, but this just refers to the installation, ignoring the requirements for intensive usage. Thus, also power clients or NAS should run on powerful machines: Dual Core CPU, 4 GB RAM, Gigabit Network, fast disks, etc…

Thanx again Jorg…I hope Microsoft is watching this…

You mean I should isolate the SQLServer and install NAVISION Server seperately on another box and have users having NAVISION Client to this NAVISION Server which in turn would communicate with the SQLServer. That means a good change in the Architecture…hmmmm

The Dimensions are done…Going to disable the MaintainSIFTIndex now …
I hope things speed up by doing this…given that my reads would be little affected but my writes would be definitely faster.

Anything else that I need to do or keep a watch on …

Thanx & Regards,

Hi Prashant,

basically PC&C (the old Navision company) have been recommending this since at least 1990 that I know of, so its nothing new. [;)]

Hi David,

But it seems NAVISION 4.0 with SP1 cannot be run in 3 tier way. Then how do I implement 3 tier architechture using the same. I want to have a seperate SQL Database Server, an NAV Client Server to which the users wud connect to access the SQL Database Server. Its not possible to migrate to NAV 5 as there wud major code change by MS partner who are implementing this.

The Cost Batch needs to be run as I mentioned as soon as possible. There is nt any major changes in the cost batch code as they havent touched the base code. Now we are on 8 core processor and 32 GB Ram it beats me how does the world runs the cost batch and we cant despite having a downtime of 13 hours. Can u beat it? We were able to run only 800 SKU’s out of 17000 SKU’s for 40 stores for a period of 3 months. This is ridiculous despite being on single user mode.

Even on the Batch posting queue we face probs…lets say a PI (Purchase Invoice) with say 1 line takes 2.5 ms to post, another PI (Purchase Invoice) with 1 line takes more than a minute to post. This is a nightmare for us.

When I tried disabling the SIFT keys the performance didnt show any improvement. I implemented what Jorg had told me and expereinced some improvement. What could be done to run our cost batch on priority basis. Pls advice.



I do seem to have the numbers mixed up. The way it was explained to me is that the most performant and fault tolerant RAID array is where you stripe mirrored sets. I was told that it is faster AND it is more fault tolerant. If you lose one disk in a striped array, you lose the whole array, so losing one disk in a nested array where you mirror striped arrays will cause you to lose half of the disks and all redundancy. With striping mirrored pairs you can lose between 1 and half of your disks and still have partial redundancy.

Striping mirrored sets is more fault tolerant than mirroring striped arrays for sure. I was told that it was also faster.

sorry guys when I was replying I only saw page 1, didn’t see there was another page, and I repeated some of the stuff already said.

Not really. The Navision Database Server is its own DBMS, it is not a server that communicates to SQL Server for NAV clients, there is currently no 3-tier architecture in NAV. All processing happens inside the NAV client sessions. SQL Server is currently basically just a data storage device that the clients connect to.

What ‘isolating SQL Server and Navision’ means is to pull the NAV clients away from the SQL Server machine, it has nothing to do with being a 3-tier application. You should have a NAV client installed on the SQL Server for admin type activities, and only during off hours, since 32bit apps are bad for performance on 64bit machine. However, regular users should never connect directly to SQL Server to run any application. You can have the NAV client installed on all computers that need NAV, but with as many users as you seem to have this should probably go onto a farm of Citrix machines.

By the way, I would probably increase the number of spindles in your storage. I also don’t know for sure if splitting up the data files is beneficial. This used to be the case with the Navision Database Server (which is the native DBMS) but I don’t know if doing that on SQL Server will actually increase performance.

You need to wait for Version 6.0 to do that.