Purpose

 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 .   

Related Posts

RAID server configuration and disk amounts

SQL Server Storage and IO performance

Author: Jack Vamvas (http://www.sqlserver-dba.com)