>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

Hi Walter,

I've put more info in replies to Thomas.

Basically, the app is SAP Business One (SBO), a canned app from SAP (not custom) so we're not in any position to change its architecture or queries.

We're working with a VAR that is apparently a world leader in fixing SBO performance problems and related reliability issues. What I'm looking for is a "sanity check" for their recommendations, and to find out if anyone else can offer any tips for working with high-performance disk subsystems with SQL Server (or, in general).

I agree about 64-bit software and more RAM, that's in the plan.

Thanks for the tip about SQL Server logical I/O vs. physical I/O, we'll look into that.