Hi Al,

You've got a difficult task... Before making any conclusions, you must stop guessing and start measuring. It is bad enough that the application is not properly constructed, but it would be even worse if you implement a new disk system and find out afterwards that it does not solve the problem.

This measuring is a specialism on its own, so it might be best to hire a specialist to find out the bottleneck in your performance problem. A few suggestions however.

- Find out whether you can change the design of the application in the most critial areas, reducing the amount of data being pulled from the SQL server.

- USe the SQL profiler to capture the queries that are having th worst performance problems.

- Re-execute those queries in the QA and look at the execution plan. Can you optimize it by adding indexes or choose a better candidate for clustered indexes

- Of those problematic queries, check whether the bottleneck in in I/O or CPU.

- Use the performance monitor to find out whether there is a problem in the logical I/O versus the physical I/O.. the ratio logical/Physical should be well over 95%. If it is not then it clearly indicates the disk and or cache is the problem for your performance problems.

- I assume you're running a 32 bit version of Windows and SQL server. You might solve the problem with running 64 bit and adding more memory to it. SQL server then can cache more than 2GB.

- Make sure there are no other processes on the SQL server that take away valuable resource from the SQL server process (I/O and CP)


IMO, the best thing you can do is working top down and try the cheap solutions first (Examining the Queries, adding/changing indexes, and minor changes in the application to reduce network traffic) before trying the more expesive options (Using 64 bit, add new hardware)

And if you don't have the expertise to do it, stop making guesses and find an expert (or two) to find out the best strategy to solve this problem

Walter,

Walter,









>I have a client running SAP Business One 2007 (SBO) on their LAN, with about 15 active workstations. SBO is "nominally" a client/server app, with a .Net 2.0 client running against a SQL Server 2005 backend.
>
>I say "nominally", because according to the top guru at the VAR who put everything together, SBO is architected like a VFP app - queries suck tons of raw data across the wire to workstations, which process them locally, then return updates to the back end as required.
>
>The server is currently a Dell PowerEdge 1800 w/two dual-core Xeons, 4GB RAM and 5x 15K SCSI hard drives on a PERC4/SC controller in a RAID5 array. There is only one logical volume, C:. This server is dedicated to SQL Server, running 32-bit W2K3 R2 and 32-bit SQL Server 2005. At time of purchase it was thought by everyone that this server would be more than adequate to support the user load.
>
>Pretty much from day 1, my client has been experiencing SBO crashes on the workstations, apparently caused by "timeouts" waiting for data from the backend. The VAR is now saying my client needs to upgrade to a high performance disk subsystem. They're recommending:
>
>- 4 disks in RAID10 for Windows and the swap file ( C: )
>- 2 disks in RAID1 for SQL Server logs ( D: )
>- 4 disks in RAID10 for SQL Server databases ( E: )
>- all disks 15K SAS, 73GB on a suitable SAS controller ( e.g. Dell PERC5 or PERC6 )
>
>Also, it's expected my client will switch their software licenses to the 64-bit versions and run those on more RAM (at least 8GB, their current server will take up to 12GB). My client's databases are small, they have 2 active companies (databases) neither of which is over 2.5GB, plus some smaller test databases and snapshots for reporting purposes.
>
>While doing some independent research on this recommendation I found a Dell whitepaper:
>
>http://www.dell.com/downloads/global/power/ps4q07-20070555-Chen.pdf
>
>Their recommendations pretty much mirror the VARs. However, Dell recommends putting the SQL Server tempdb on yet another logical volume with at least a couple more drives. I'm guessing that if this isn't done, tempdb should be located on the random access volume E: along with the SQL databases.
>
>After all this build-up < g > , a couple of questions:
>
>1. Can anyone offer any real-world advice on whether it's worthwhile to break out the SQL Server tempdb to a separate logical volume/separate disks?
>
>2. The VAR is also claiming it's "important" that the server have the latest 1333MHz FSB, which would mean a server replacement as the PE1800 is "only" 800MHz FSB. I find this a little hard to swallow, this fast FSB is very new, it implies that any other sites running SBO on server hardware more than a year or so old are in trouble. Anyone have any insight on whether fast FSB helps SQL Server, particularly in improving effective disk I/O and reducing "average disk queue length"?
>
>It is unusual that a nominally C/S app with relatively small databases and low number of workstations would require such a powerful SQL Server. However, the VAR now admits that SBO is an SMB product with enterprise-class I/O performance requirements.
>
>Any war stories you'd like to share? TIA.