Sunday, 7 February 2010

SQL Server Storage

This is a short post on SQL Server storage best practice and what i have learned over the years… Much of this information can be found in Microsoft’s document on Storage Best Practice and this can be found here

Test your setup before deployment. MS have useful utility called SQLIO that can be used for this purpose.

Place log file on a RAID 10 disks…This provides fast write performance, which suits the sequential writes of the log while maintaining fault tolerance.

Separate log and data files at the physical disk levels. Spreading the write intensive write IO of the log from the Read/Write of the data files across different spindles can reduce can reduce contention

Configuring TEMPDB correctly – MS state in its SQL Server storage best practices document that you should create one TEMPDB data file per CPU and place the TEMPDB database on a RAID 10 disks

Database data files should be of equal size…The SQL Server allocation algorithm favours files with more space.

Pre-size data and log file, this will prevent these files growing in small increments as the database grows in size, (especially if you have AUTOGROW) these small but high frequency growth can cause physical file fragmentation on the disk.

Mange file database file growth manually, AUTOGROW can be left as a safety measure but don’t rely on it for regular database growth.

The deeper the HBA queue depth the better for SQL IO volumes.

2 comments:

  1. Good, basic article. I would love to see an in-depth article on calculating the IOPS requirements for an application to determine your SAN requirements .... if that's something you are willing and able to conquer.

    ReplyDelete
  2. Hi Robert, Thanks for the comments. I will try and put together an in-depth article in due course.

    ReplyDelete