Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

SQL Server - Define IOPs for all database servers

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 .   

Related Posts

RAID server configuration and disk amounts

SQL Server Storage and IO performance

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on SQL Server - Define IOPs for all database servers


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer