Windows / HP Storage Array – Review Configuration

Background

In this post, let us review our storage configuration.

Our platform is MS Windows 2008/R2 and the back-end storage are internal HP drives.

Forward

How did we get here…

We are taken a cursory look at wait stats and found some pointing at IO and so we used Paul’s script:

How to examine IO subsystem latencies from within SQL Server
http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

Code

/*
  How to examine IO subsystem latencies from within SQL Server
  http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
*/
SELECT
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]

ORDER BY ( 
		( [io_stall_read_ms] + [io_stall_write_ms] )
		    / NULLIF
                       (
			   ( 
				( [num_of_reads] + [num_of_writes] )
			  )
			  , 0
		      )	

         ) desc
GO

Output:

IOStalls

Explanation:
Unfortunately, most of our SQL Server files are on drive E:

Review Storage Configuration

As our server is HP, let us use HP’s Array Configuration Utility to examine our configuration.

Software

Download and install the latest HP Array Configuration Utility.

Layout

Screen Shots

Mount Point – C

LogicalDrive1MountPointC

Mount Point – D

LogicalDrive2MountPointD

Mount Point – E

LogicalDriveE-MountPointE

Mount Point – F

LogicalDrive4-MountPointF

Mount Point – G

LogicalDrive5-G

Tabulated

Here are the screen shots pasted above, arranged in a more concise layout.

Mount Point Fault Tolerance Physical Drive Cylinders Stripe Size Size
 C:  RAID 1  Disc 0  35132  128 KB  136 GB
 D:  RAID 1  Disc 1  35132  128 KB  280 GB
 E:  RAID 5  Disc 2  65535  64 KB  1.4 TB
 F:  RAID 1  Disc 3  65535  64 KB  2.5 TB
 G:  RAID 1  Disc 4 65535  256 KB  838.2 GB

Interpretation

Detail

  1. We have 5 physical discs ( Disk 0, 1, 2, 3, 4 )
  2. RAID Level
    • Four of our drives ( C:, D:, F: G: )  are RAID 0
    • Disk E is RAID 5
  3. Strip Size
    • Drives C: and D: are 128 KB
    • Drives E: and F: are 64 KB
    • Drive G: is 256 KB

Summary

  1. There are no spare drives
  2. Our Disk Controller is Smart Array P410i

Hardware Drivers

Disc Controller Driver

I will suggest that you visit your hardware vendor’s web site and see how close you are to the latest driver.

Our machine is  HP Proliant DL 380 G6.

Vendor

  1. Proliant DL 380 G6
    http://h20564.www2.hpe.com/hpsc/swd/public/readIndex?sp4ts.oid=3884088&swLangOid=8&swEnvOid=4064

StorageControllerDriver

Our Current Driver

HPArrayP410iController

Observe

We can quickly see that our driver is 6.20.0.64 ( 2010.02.22) and the vendor has a newer package 6.28.0.64 ( 2014.02.18)

MS Windows

If RAIDs are defined at the OS level, we can use MS Windows tools such as diskpart.

Here is what we look like.

Code


echo list volume | diskpart

Output

diskpart

Explanation:

  1. Type Column
    • Simple
      • Basic
    • Partition
      • Partition ???
    • Mirror
      • The disc is mirrored
    • Stripped
      • The volume is stripped
      • That is data will be written across two or more discs.  Keep in mind that parity bits will be included, as well
      • RAID 5, etc
  2. Status
    • Healthy
      • We are good
    • Rebuild
      • The disc is been rebuilt
    • Offline or Missing
      • When listed as Offline or Missing, the disk might have been physically taken out, temporarily taken offline, or faulty
  3. Info
    • System
      • When tagged as SYSTEM, the volume contains OS system software in addition to any 3rd party Application Software and data data
    • Boot
      • Indicates the volume is a boot volume

Recommendation

Here is the recommended RAID Levels for each SQL Server file type:

Data Type Requirement Reccommended RAID
 Data Numerous read operations  RAID 5
RAID 10
 Log High writes  Raid 1
RAID 10
 Tempdb RAID 5  RAID 5
RAID 10
 Backup High writes  RAID 1
RAID 10

References

  1. Books
  2. Diskpart
  3. 3rd Party
  4. SQL Server Storage

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s