Nav 4.0 SP2 runs slow on SQL Server 2000 SP4


I have just moved my client onto Navision 4.0 SP2 (SQL option) from Navision 4.0 native database, and am finding major issues with performance. Their database is approximately 14gig in size, with the majority of the transactions being inventory related. (Supply-chain, no manufacturing) The site is a 10 user site. The server is a dual processor 2.7ghz, with 1gig ram, and raid 5 HDD. At peak load the server is only using about 800mb of memory, and processor load is rarely more than 10%.

The SQL server install is 2000 standard edition, with no custom settings. Is anyone aware of any special fixes, settings, coding or voodoo that needs to be done to get comparable performance from SQL 2000 over the native database. I have been over the posting routines with a fine tooth comb, using the Performance toolkit, and am unable to wring any better performance out of this installation. The main areas that the system is getting slowed down in is inserting records into or finding records in the G/L entry, Item Ledger Entry, Value Entry, Ledger entry, and document dimension tables.

It’s got to the point were I am converting the client back to the native database until I can find a way to get some decent performance.

Any ideas, suggestions would be welcome.

Well, if you search this forum for “SQL Performance” you will find plenty of advice how to improve your system … I don’t want to repeat all here … you need at least 2 GB RAM and probably your index structure is insufficient (did you check the SIFTs?), and the worst thing for the last: RAID5 is NEVER an option for a database server - my guess: THIS is your bottleneck!!! …

But just something about SP4 for SQL Server 2000:

You violated the rule: “Allways install the odd, never the even ones!” [6]

Please refer to the related KB article

SP4 could cause some trouble, you should install another hotfix:

Do you run SQLstatistics every night (best with 30% sample. Less if it takes too long, but better not less than 10%)?
Do you run IndexRebuild at least once a week?

Where is your transaction log? An improperly configured transaction log can have a MAJOR impact on system performance. The transaction log should reside on a dedicated RAID 1 drive set (no other files on drive).

There are loads of documents covering SQL setup and performance and if you have just moved the database from Navision native to SQL then it is probable that you are going to have to do quite a few changes on Indexes and SIFTS in Navision as well as SQL maintenance and of course the Hardware. I strongly recommend reading some of these documents first:

  1. Tuning Navision for better performance, Revision 5, Created May 21, 2004
  2. Microsoft Dynamics™ NAV 4.0, Hardware Guide, White Paper
  3. Installation & System Management: Microsoft® Business Solutions–Navision®,SQL Server Option


PS: 4gb RAM would be much better

SQL 2000 Standard Edition only supports 2 GB of RAM.


But you need some more RAM for the OS, SQL-executables and some other things. So best at least 512MB more than 2GB of RAM.

hi guys,

i just found the same problem but at the same time have a solution to fix it.

  1. open your sql server 2000 enterprise manager

  2. click the [+] Microsoft SQL Server, then [+] SQL Server Group, then right click your (local) server => Properties

  3. go through Memory tab. I used Dynamic Memory …so the sql server services eat my RAM as much 1.7GB

So now, please use the Fixed Memory. If you have 2 GB like mine, use it 75% = more or less 1,5GB

Don;t ask me why…but it works. My Navision posting is getting faster.

Hope it helps

Andri Darmawan - Indonesia

Windows Server Standard Ed 2000 SP2

IBM xSeries 336, RAM 2GB, Intel Xeon 3 GHz (dual)

NAV 4.0


Hi Andri Darmawan,

welcome to the “Dynamics User Group” [<:o)]

Well, actually it does not matter that much whether you’re using fixed or dynamic memory management, as long as you’re running a dedicated server or the min/max limits are neither set too high or too low. With just 2GB of RAM you should always end up with a “Target Memory” between 1,5 and 1,7 GB.

With only 2GB available you should be very careful with using fixed memory limits. If the OS does not get an sufficient amount of RAM, it could happen that some other services do dot start up or other strange things and problems might happen!

And have in mind: the situation in the original post was that there was only 1 (one) GB available!

Best regards,


The reason that it works is that probably the memory SQL is using ISN’T swapped to disk because SQL uses less memory and the server doesn’t swap it to disk. Fetching a swapped out page from disk is slow and nulifies the use of the cache.

I’d say add more RAM to that machine, it’s very inexpensive nowadays.

But if you have SQL2000 standard 32bit, it won’t help a lot because it is limited to 2GB.

Is that total RAM on the machine, or RAM that you can assign to SQL Server? I think you could put 4GB on the machine, and set memory usage on SQL Server to static 2GB, and still have enough left over for the OS and quite possible other apps as well.

Hi All.

You were right. The impact is nothing, the sql server still eat my RAM [:(]

Then I restart the system. My IBM x336 said there’s some services don’t running, pls check the event viewer.

Attached (picture 1) is telling you the problem.

Then i opened ServerRAID Management and the picture 2 make it more clear.

Then I check the physical server, and it is so clear that my first disk light indicator is turn to yellow.

So my premature hypothesis is my first disk is defunction / malfunction.

I read the Help. It tells me to right click the ID 0 (first disk) and choose to ‘Set drive state to defunct’ but i’m afraid to do that since i dont have any experience on this case. Is it save to do that ? What will happened ? Will my windows / sql server stop working ? or even getting better ?

Any help is appreciated. Thank you