22 August,2011 by Tom Collins
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?
HBA settings?
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 .
RAID server configuration and disk amounts
SQL Server Storage and IO performance
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |