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 Reads/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 questions.

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.

Republished from http://www.sqlserver-dba.com.