SQL Performance Issues

We’re running a 2.5 database on SQL 2000 SP2. Server is quad xeon 550, 4 gb RAM - current db size is about 35 gb. Clients connect with 2.6c client. Running financials and advanced distribution. The performance we get is terrible. We’re trying to determine if it relates to SQL tuning, modified code issues, etc. We see a lot of blocking and beginning to see more and more deadlocks. Anyone else having this sort of trouble? Posting of any sort is very slow. Is it true that although SQL utilizes record-level locking, that the Navision code continues to utilize table locking? We’re looking at some of the posting processes and find that when an invoice is posted, for example, the Item Ledger Entry table is locked and cannot even be read until the process is complete. I also see a lot of discussion on RAID. Is it also true that RAID-5 is less-than-desirable solution in regards to performance even running SQL? I’m getting very conflicting reports… Yet another question - do the commit cache and dbms cache settings apply to navision db only? I don’t see any settings for these in the SQL version.

You should be able to get a lot of answers from the product documentation, particularly the Application Designer’s Guide. In order to solve your problems, the first step would be to upgrade to the latest version of C/SIDE (currently 3.60). It will perform significantly better than your current version. But that probably won’t satisfy you, and you will want to look at the real source of the problems: The application code. You should plan for upgrading to a later version of the application (preferable the most recent) and further closely analyze your modifications using the “Performance Troubleshooting Guide”. It comes with a number of tools and can be obtained from your NTR. The tools require an NSC license. The SQL Server HW configuration rarely is an issue. Use the NT Performance Monitor to check whether your main hardware resources (CPU, disk I/O, net) are exhausted. Regarding RAID 5: It is not desirable from a performance point of view. Use RAID 1+0 for optimal disk performance. However, as the HW is probably not the main source of your problems, you should not worry too much about that until you have analyzed the app. - Jens

Hi Jeby… For sure the RAID 5 is extremely not recomemded in SQL server and Navision environments. Please, move to Raid 0+1. Your RAM looks great but you need to check other issues: 1.- your HardDisk and Array configuration. 2.- Network adapter (check the level or CRC errors) 3.- Network backbone (see the speed of your Switches or Hubs) They shouldn’t create bottle necks because then your transactions will be delayed and the performance goes down. I hope this will help you. Grettings from Spain!

quote:


Originally posted by jeby
We’re running a 2.5 database on SQL 2000 SP2. Server is quad xeon 550, 4 gb RAM - current db size is about 35 gb. Clients connect with 2.6c client. Running financials and advanced distribution. The performance we get is terrible. We’re trying to determine if it relates to SQL tuning, modified code issues, etc. We see a lot of blocking and beginning to see more and more deadlocks. Anyone else having this sort of trouble? Posting of any sort is very slow. Is it true that although SQL utilizes record-level locking, that the Navision code continues to utilize table locking? We’re looking at some of the posting processes and find that when an invoice is posted, for example, the Item Ledger Entry table is locked and cannot even be read until the process is complete. I also see a lot of discussion on RAID. Is it also true that RAID-5 is less-than-desirable solution in regards to performance even running SQL? I’m getting very conflicting reports… Yet another question - do the commit cache and dbms cache settings apply to navision db only? I don’t see any settings for these in the SQL version.