SQL Server disk configuration tips

Most important metrics for SQL Server: ‘throughput’: how many MB/s can the disks handle and what is the load placed on the disks.

If you’re buying a standard package your vendor should know this.
Surprisingly however in the hardware proposal (top end server(s) / high rpm disks right?) this figure isn’t mentioned anywhere in most situations.
What you can do, is ask your vendor if they could run a fn_virualfilestats() function (see my fn_virtualfilestats post) on a similar production configuration of another customer (better: talk to them yourself!) . This gives you insight on the I/O pattern (more read of more write activity) and the load on TempDB, to determine if it’s a good candidate –which it usually is- to store on it’s own disk spindles.

Run the Microsoft SQLIO tool to determine a baseline on MB/s and latency per disk partition.
Store it for later reference, the Windows performance tool can also measure the throughput and it gives you the possibility to compare the numbers and check if there might be a disk throughput performance bottleneck in your production system.

Disk configuration tips:

Use as many disk spindles as you can, and try spreading data / log and tempdb on different raid arrays.
Stripe your RAID-config at 256k instead of 64k / 128k.This will increase performance of SQL Server read-aheads.
If you use Windows 2000 / 2003 align the disk partitions!!!:
(see Miscrosoft knowledge base article KB300415 on how to use Diskpart).
At the Diskpart command prompt,
type: Create Partition Primary Align=X,
where X is either 32 or 64, depending on the recommendation from your storage vendor.
If your storage vendor does not have any specific recommendations, it is recommended that
you use 64.
This step is not needed in Windows 2008

Full (not quick) format with a 64kb stripe size for disks holding data/log and backup files.

Full (not quick) format with a 4kb stripe size for OS and program/swap devices

Data raid5 or raid10 when there’s more write then read activity (check with fn_virualfilestats),
Log (raid1 or raid10 under heavy load), separate backup disk (raid5)

Put TempDB on its own physical disks (raid1, raid10 under heavy load).
In a non-clustered environment, I normally use the local server storage for TempDB.

-->
>
Use NTFS volumes

Do not use more then 80% of the volume

Defragment the disks on a regular basis

Create multiple files per database and increase performance

In this post I want to explain why to create multiple data files per SQL Server database

Note: this is only relevant for data files, not for log files. Log files always have one file!

Microsoft documentation says:
• The number of data files within a single filegroup should equal to the number of physical CPU cores (hyper threading cores should not be counted)

This is also true for TempDB

So create all the files in your new database with the same initial size – make an estimate on how big you want the files to be- and set a reasonable growth factor. Do not set it to a percentage, but on the same fixed size in MB for all data files. Use the same growth factor for all the files you have created.

SQL Server will fill the data files in a round robin way, meaning that data is spread equally across all the files as the database grows.

Tip!
Another advantage from this approach is that if you have performance issues and you can pin this down to an disk I/O related problem
(see my http://sqlserveradvisor.blogspot.com/2009/02/microsoft-included-fnvirtualfilestats.html on fn_virtualfilestats() post for this), it is then an easy change to add a new raid disk configuration to your server and then spread the data files evenly over both the raid arrays.
Giving you an instant performance boost!!

fn_virtualfilestats: Measure the I/O load on your disk system

Microsoft included the fn_virtualfilestats function in SQL Server so you can monitor the I/O load the instance executes on the disk subsystem.

I use this function mainly to:
- Get the load on TempDB and check if it’s a good idea to move it to it’s own disks (if you have not already done so)
- Get the I/O profile (more read of write activity?) on the database level to help determine if it might be an idea to replace a raid-5 array to a raid-10 or to move datafiles to additional diskarrays (see my post http:// for this)
- Check if there are any disk related issues, in other words are there many stalled I/O’s?
(stalled I/O is an I/O waiting for another I/O)
- Check if the system can handle more db’s, eliminating additional servers

Real life example:
I managed to save big $$$ and could avoid buying new hardware by letting our vendor run this script in comaparable environent of another customer and so proving the load was not so big as they claimed, so we could run the new system on our existing hardware.

Here’s the T-SQL to get the I/O profile of all your database and data files:

DECLARE @TotalIO    BIGINT,
    @TotalBytes BIGINT,
    @TotalStall BIGINT

SELECT @TotalIO  = SUM(NumberReads + NumberWrites),
       @TotalBytes = SUM(BytesRead + BytesWritten),
       @TotalStall = SUM(IoStallMS)
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)

SELECT [DbName] = DB_NAME([DbId]),
      (SELECT name FROM sys.master_files
        WHERE database_id = [DbId]
              and FILE_ID = [FileId]) filename,
    [%ReadWrites]       = (100 * (NumberReads + NumberWrites) / @TotalIO),
    [%Bytes]        = (100 * (BytesRead + BytesWritten) / @TotalBytes),
    [%Stall]        = (100 * IoStallMS / @TotalStall),
    [NumberReads],
    [NumberWrites],
    [TotalIO]       = CAST((NumberReads + NumberWrites) AS BIGINT),
    [MBsRead]       = [BytesRead] / (1024*1024),
    [MBsWritten]        = [BytesWritten] / (1024*1024),
    [TotalMBs]      = (BytesRead + BytesWritten) / (1024*1024),
    [IoStallMS],
    IoStallReadMS,
    IoStallWriteMS,
    [AvgStallPerIO]     = ([IoStallMS] / ([NumberReads] + [NumberWrites] + 1)),
    [AvgStallPerReadIO] = (IoStallReadMS / ([NumberReads] + 1)),
    [AvgStallPerWriteIO]= (IoStallWriteMS / ( [NumberWrites] + 1)),

    [AvgBytesPerRead]  = ((BytesRead) / (NumberReads + 1)),
    [AvgBytesPerWrite] = ((BytesWritten) / (NumberWrites + 1))
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)
ORDER BY dbname
 

Be ware: counters are re-set after the instance is restarted!

The result (not all columns shown here). Click on image to enlarge