22 August,2011 by Jack Vamvas
The storage guy is configuring a new storage system. It includes RAID arrays, SVC, HBA & Fibre Channel configurations.
He’s asked me to define some configurations for optimal SQL Server performance. Rather than supply him with configurations , I prefer a different approach.
Create a IOPS per database server report for the storage expert.
Why focus on IOPS ? As a DBA I’m looking for IOPs to be delivered within an acceptable timeframe. I prefer this approach as a starting point . I can compare figures from the existing system against the new system.
Systems administrators \ Storage experts raise other questions such as :
What block size to use?
Fibre Channel Settings?
There are “best practises” recommended by vendors. It’s important to be aware of these figures , but also consider the specific workloads and IO profile of your servers.
A Simple Method
1) Collect Logical Disk:Disk Writes/Sec and Logical Disk: Disk Reads/Sec per database server
Logical Disk: Disk Reads/Sec The rate of read operations from disk
Logical Disk:Disk Writes/Sec The rate of write operations to disk
2) Why use these 2 counters? These counters give you some clues on answering some important IO question s
Is disk dirve performance being achieved ? < 10 ms
Is throughput at the limit?
Reads/sec and Writes/sec indicates the workload . Storage expert can use this workload estimate to configure storage arrays .
Using instructions in Window Perfmon scripting, SQL Server perfmon and how to perfmon .