Printed 2014/09/01 03:20PM

Whitepaper: SQL Server 2005 Physical Database Storage Design


Microsoft has released a whitepaper on SQL Server 2005 Physical Database Storage Design (Microsoft Word document). It's 35 pages in length including title page, table of contents, and appendix. It's brief and not a deep dive, but a good read. After looking at the whitepaper, here are some things I keyed in on:

Compression for Read-Only File Groups

Compression is recommended for read-only filegroups in order to gain "storage efficiency." Well, let me better state that. The whitepaper says to consider it as it acknowledges the performance due to the processing time required to render the data in a useable form. In SQL Server 2000 deploying data and log files into compressed folders/drives wasn't supported. However, in SQL Server 2005 it is for read-only file groups. This whitepaper gives a bit stronger language than Books Online by recommending the setup for consideration.

Use of Instant File Initialization

Ever create or expand a large database and sat there waiting on your hands until it completed? SQL Server 2005 supports instant file initialization, which basically means the OS gets told how big the file will be and the file is "created." Normally this means zeroing out all the data, and when you're writing Gigs of data, it takes a bit of time. With instant file initialization you skip the zeroing out but still get the file. By the way, this was also a recommendation of SQL Server MVP Kimberly Tripp at her TechEd sessions.

Row-Level Versioning and TempDB

Row-level versioning uses TempDB. That means when you're doing things like making heavy use of triggers be aware of the use of TempDB for this. The whitepaper points to another whitepaper on database concurrency and the use of row-level versioning:

Data Partitions

There is a discussion of data partitions and how to consider utilizing them. Data partitions can improve performance if implemented properly. The whitepaper spends some time discussing how (in two sections) without going too deep.

Sector and Stripe Size

We see questions on these topics every so often in the forums. The whitepaper differentiates between the two terms and then specifies two stripe sizes, depending on how the data is being used. The initial recommendation for stripe size is 64 KB, however, if there are table and index range scans on tables > 100 MB, the recommendation is for a strip size of  256 KB.

Discussion of when to use IDE, SCSI, SATA, SAN, NAS, and iSCSI

Basically, don't use IDE if you have a choice (i.e. the money). And use SCSI over SATA. A summary of recommendations at the beginning of the white paper say SAN is fine, but use iSCSI instead of NAS. However, there is really no support for these positions directly in the white paper.

RAID Levels

"What RAID level should I use?" is another question we see from time to time on the forums. Microsoft doesn't try to answer this question directly. Rather, the whitepaper covers the different RAID levels we typically see and the pros and cons of each. There is more in the appendix which contributes to the discussion of which RAID level to use.


A brief discussion of the various types of workloads can be found in the whitepaper, but once again, it's not very in-depth. However, the section does have example configurations based on workload, from which you can extract some recommendations on things like RAID levels.
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.