When setting up your server remember to set up a disk partition structure that is meant for SQL server. Just to be clear, you don’t need more than one disk partition to install and get SQL server to run, but if you would like to configure a disk setup that support the internal architecture of SQL server, please feel free to follow these guidelines.
- Your first disk partition will be for the OS, this is a 4KB block size partition, that are meant for your OS. The speed of this disk will determine your restart time of the server, your temp storage file response if used default environment variables for temp. among other OS operations.
- Your second disk partition is for your TempDB data files 64KB block size. These files will be where you read from TempDB. All of your databases will share the resources of these files so it’s important that this is a disk partition that can withstand the pressure from your databases. A TempDB datafile is used when SQL server need to temporary store data from a query before using it in the next operation, or when a CTE, variable or a temp table is defined by the user.
- Your third disk partition is your TempDB log disk 64KB block size. This is where your TempDB logfile will be stored.
- Your fourth disk partition is meant for your database log files 64KB block size. Your log files have a sequential write structure. All the data that you write to your database will be written from memory and to your log file. To make sure your writes are optimized, make sure these files are on a fast storage.
- Your fifth disk partition is your Backup disk 64KB block size. This is where you will place your backup files.
- Your sixth disk partition is your archived/cold data partition 64KB block size. This partition can if you like, be a slower storage disk that may save you some money.
- Your next disk partitions will be for your hot data files 64KB block size. This is where most of your reads will be coming from. Reads are can be done in parallel and will be a typical bottleneck for getting your data from the disk and to your user. If you are experiencing slow read from disk you can add multiple hot data partitions to split out your IO on multiple drives and get a much faster database.
So, what have we actually done here? We have configured a disk setup that is aligned with SQL server internals, but this will cost a lot, especially since you are probably configuring for a RAID at you HW side. The result will then be a higher cost than most of us would like to pay. So what is the minimum configuration disk setup you should allow yourself for a medium sized database?
Well it all depends, as always. First of all, make up your mind: is the database we are doing this for going to have a priority “write” or “read” setup? If your database is a front-end data mart for a reporting system, you would probably want to set your money on the read disk, and if you have a transaction log database for sensor data you might want to prioritize your writes. But let’s get back to the minimum.
- one OS partition
- one log partition
- one data partition
- one tempDB Partition
- one Backup partition
This minimum setup isolates the OS, log and data files. This is important because we would like SQL server to be able to maximize the throughput on each operation. Think of it this way: when we do an update on a table we would like to read from the datafiles in parallel, and while we do that we write to a RAM temporary internal storage before we write to the log file. Then the checkpoint runs the log-records that are written to the datafile. In the sequence of these steps we want our disk not to have to read and write in between operations, but make sure that it all works like a circular workflow.