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.
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]
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:
Tuning Navision for better performance, Revision 5, Created May 21, 2004
Microsoft Dynamics™ NAV 4.0, Hardware Guide, White Paper
Installation & System Management: Microsoft® Business Solutions–Navision®,SQL Server Option
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!
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.
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.
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 ?