We have a pretty big NAV 2009 database(500GB), 120+database users (classic client). We have constant compalints from end users for poor performance of the system. We have done a lot of performance tricks in the SQL Server 2005 and now we are looking for upgrade of the disk storage system. However, we cannot find any official recommendations regarding this. Are there any official benchmarks on how the system should perform based on number of users and number of ledger entries, what number of IOPS is good and bad or any other benchmark of similar parameters?
IMHO, IOPS is probably the most over-hyped hardware specification lately. Seems the only thing some hardware vendors want to talk about. “How many IOPS does NAV need”? There are no official numbers, and from some real systems I’ve had measured, the real world is all over the map.
I’m not saying don’t look at the IOPS. Just don’t use it as the sole basis for a decision. With a transaction processig system, things like latency are actualy more critical to the drive system.
Also don’t expect hardware to be your sole solution. We’ve got a few large systems out there. While hardware does play a role, it is far from the only component to the solution. I see that you say you’ve done lots of performance tricks.
Well, first question should be if your disk-subsystem is indeed the cause of your performance issues - there could be zillion other reasons. Besides the disks, the amount of RAM is crucial - the more SQL Server could cache, the less pressure is on the disks.
Over all, most of that 500GB is probably just historical data, thus data which is not permanently “moved around”, so it might be likley that there are just few business-processes which are affected and those you might be able to fix by programming-chages or indexing.
Also you current disk-subsystem/layout is most important!
So IF you have indeed identified an IO issue - the response times of the disks should be less than 15 msec at average - then you have multiple options:
Expand the RAID striping; adding more disks
Segregate IO intensive tables to separate disks
Replace HDD with SSD
Increase RAM
etc.
All those options need to be discussed thoroughly! There’s PRO and CON …