Blog Post

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating