Blog

Check here for frequent updates with news and tips from the SQL Server Performance Pros at SQL Tuners.


All posts

SQL Tuners' Disk Partition Best Practices

SQL Server disk subsystem design is probably the most important aspect in preparing for a production SQL Server installation. There are a lot of questions involving spindle count, RAID configuration, direct attached, iSCSI, SAN, disk sizes etc. We do quite a few preliminary analysis and recommendations for our clients preparing for a new SQL Server rollout. The type of information we gather can include performance traces of existing systems (i.e. this is an upgrade of existing hardware), data size, whether the database is OLTP or a BI implementation, rate of expected change, concurrent users, transactions per second, RPO (Recovery Point Objective), RTO (Recovery Time Objective), DR (Disaster Recovery), Environment refresh requirements, etc. It can be quite a bit of information to cover.

But in general, we follow a Best Practices guideline for setting up SQL Server drives. The following is our generic recommendations. From those recommendations a final design is put together taking into account the previous data points mentioned above.

Enjoy!

SQL Tuners'
SQL Server Performance Standards, Rationalizations and Implementations

Raid Sets

Standard:

Data drives:           Raid 1+0

Backup drives:        Raid 5

Log drives:             Raid 1+0

Batch drives:          Raid 5

Application drives:   Raid 1

System drives:        Raid 1

TempDB drive:         Raid 5 (Raid 1+0 for higher performance)

 

Rationalization:

Raid arrays provide varying degrees of fault tolerance and performance depending on the raid level used. We design the SQL build specifications with different raid levels to match the performance and fault tolerance requirements for each drive’s specific function. Data drives need very high performance as well as high fault tolerance while backup drives require lots of space and moderate performance and moderate fault tolerance. By varying the Raid levels on a given server we can maximize cost savings on some drives while maximizing performance and / or fault tolerance on others.

 

Drive layout

Standard:

Drive letters on SQL builds are pre-determined and uniform across all production SQL servers. Additionally, each drive’s usage is restricted to specific functions within the server.

 

Rationalization:

Specifying drive letters uniformly across the enterprise, allows for quicker support and resolution of work requests as well as troubleshooting issues. It allows us to know exactly where pertinent information is stored on each server regardless of server function or application support. Additionally, this allows us to build and support high performance SQL servers by designing hardware specifications for SQL builds where each drive has been tuned to provide specific functions. This approach reduces the total cost of support by IT as well as extended operations teams by reducing resolution times, eliminating confusion, streamlining communication and providing applications with high performance SQL platforms.

  
            Implementation:

Note: Drive Size is determined by the SQL Build Specification and will vary based upon size of SQL server being built. Sizes given below are meant for illustration in regards to relationships between drives. Additionally, not all SQL build specs will contain all the drives specified below, e.g. a 240GB SQL server may only contain G$ and H$ for data drives and R$ and S$ for backup drives.

 

Drive

Functions

Size

Raid

C$

System

9GB

1

D$

Application (SQL Server Binaries)

9GB

1

E$

User Batch / Share

20GB

5

F$

SQL TLog Backups

(O$ + Q$) * .5

5

G$

SQL Data 1

120GB

1+0

H$

SQL Data 2

120GB

1+0

I$

SQL Data 3

120GB

1+0

J$

SQL Data 4

120GB

1+0

K$

SQL Data 5

120GB

1+0

L$

SQL Data 6

120GB

1+0

M$

SQL Data 7

120GB

1+0

N$

SQL Data 8

120GB

1+0

O$

SQL Log 1

((G$:N$) * .20) / n

where n represents number of Log drives

1+0

P$

TempDB Data + Log

50GB

1+0

Q$

SQL Log 2

((G$:N$) * .20) / n

where n represents number of Log drives

1+0

R$

SQL Backup 1

((G$:N$) + (O$+Q$)) / n

where n represents number of backup drives

5

S$

SQL Backup 2

((G$:N$) + (O$+Q$)) / n

where n represents number of backup drives

5

T$

SQL Backup 3

((G$:N$) + (O$+Q$)) / n

where n represents number of backup drives

5

U$

SQL Backup 4

((G$:N$) + (O$+Q$)) / n

where n represents number of backup drives

5

V$

SQL Backup 5

((G$:N$) + (O$+Q$)) / n

where n represents number of backup drives

5

W$

SQL Backup 6

((G$:N$) + (O$+Q$)) / n

where n represents number of backup drives

5

X$

SQL Backup 7

((G$:N$) + (O$+Q$)) / n

where n represents number of backup drives

5

Y$

SQL Backup 8

((G$:N$) + (O$+Q$)) / n

where n represents number of backup drives

5

 

SCSI Controllers

Standard:

Data drives should be separated onto dedicated SCSI controllers separated from each other and the rest of the system. Log drives controllers shall be separated from data drive controllers as well as backup controllers. Backup drive controllers shall be separate from both data and log drive controllers.

 

Rationalization:

In extensive testing over the years, we have seen over and over that bottlenecks within the disk subsystem are usually found at the SCSI controller levels as well as at the drive(s) themselves. We have developed tests that simulate the multi-threaded read and write I/Os that SQL generates and have shown that when SQL drives share a controller, the overall throughput of those drives decrease accordingly. In response to this issue, we design the SQL server disk subsystem to handle distributed I/O generated by SQL Server by dedicating all of a given SCSI controllers’ throughput to specific tasks. By controlling where Data, Log, Backup and TempDB data files are stored, we can force distributed I/O to isolated SCSI controllers dedicated to specific types of requests and specific threads. This translates into reduced throughput bottlenecks within the disk subsystem.

 

Implementation:

Obviously there is a need to balance high performance with costs. As such we build different SQL Server hardware specs that address standard vs. high performance requirements. For standard performance requirements such as those that service non-mission critical or low transactions / sec applications, we lower the SCSI controller (a.k.a. SAC) count and move lower priority drives onto shared controllers. For example, a backup drive would share a controller with other drives such as TempDB or User Batch. Reducing the number of SCSI controllers results in lower performance but higher cost savings. For mission critical, high concurrency, high transactions / sec applications, we dedicate and separate the critical I/O drives onto separate and at times, dedicated SCSI controllers. This results in huge performance benefits but higher initial hardware costs.

  

Note: The following schematics are intended as a representation of the layout of SCSI controllers and the drives they service. Please refer to specific SQL hardware build specifications for an accurate depiction.

 

e.g. Standard Performance SQL Specification

 

 

 

 

 

e.g. High Performance SQL Specification

 

 

 

 

 

SQL File Layout and Distributed I/O

Standard:  

·         Databases should be created, where the hardware specification allows, with one data file on each data drive, evenly allocated up to the total size of the database requirements for the first year.

·         Databases should be created with the log file located on a dedicated log drive apart from any data drives.

·         TempDB should be created on a dedicated TempDB drive with both log and data residing on same.

·         SQL backups are recommended to be written to a striped set equal to the number of data drives. Each striped set shall reside on a separate drive than either the Log or Data drives.

Rationalization:

MS SQL Server is a multi-threaded application which does a very good job at thread balancing and distributing its I/O. As a result, all data that is written to a database created with the default filegroup options is separated evenly across all data files with space available. This means that a single table with 1 million rows will most likely have half of those rows on one data file and the other half on the other data file, etc. This is not true however for Log files. Due to the sequential nature of the architecture of MS SQL Server’s transaction logging system, the log I/O cannot take advantage of concurrent data writes. It can, however, take advantage of a separate thread within SQL server to write to itself. Because all data modification requests (a.k.a. DML – Data Manipulation Language) require a write to the Transaction Log, by separating the Log file from the data files, we eliminate concurrency bottlenecks on the same drive. This way we can issue DML requests and multiple threads will handle the data manipulation itself while also recording the changes within the log file – all separated from each other at the physical drive layer.

TempDB is a temporary holding area for SQL server. Users can also make use of this space by creating and utilizing temporary tables as well as table variables. SQL server may use this space to help in join plans during query execution. Thus TempDB is used globally across SQL server both internally and by each database. Due to this nature of TempDB it is a sandbox of highly concurrent DDL (Data Definition language) and DML requests. Thus by separating this space away from the databases themselves, we place the large overhead associated with this database onto its own dedicated drive.

SQL Backup files, created during a BACKUP DATABASE command contain both the actual data within the specified database as well as that database’s transaction log. During the backup command, SQL server reads from all the data files as well as the Log file and writes this information into the backup file specified. Since our databases are created with separate data files on separate data drives, we can take advantage of the dedicated nature of this configuration and separate out the backup file into a set of striped files instead of a single large file. This allows multiple threads to read from multiple data files concurrently and write to multiple backup files concurrently. This is also the reason we want to separate out the backup files onto separate controllers. This allows maximum asynchronous transfers from the data drives into the backup drives using completely separate I/O streams. This results in an elimination of any contention between the multiple threads reading and writing.

 
 

Contact Us Today!

Speak with a representative now regarding your unique SQL Server needs:

1-800-2-MY-DBAS

email: support@sqltuners.net

web: contact form

"When you need someone who understands SQL Server performance from the database to the disks, this is the company you call. They'll keep your database running and SAN vendor honest."

- Bill Minton, Sr Director of Corporate Information Systems, ANPI