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:

http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx

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.

Workloads

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.