http://www.sqlservercentral.com/blogs/brian_kelley/2006/06/15/whitepaper-sql-server-2005-physical-database-storage-design/ Printed 2015/08/04 10:30AM
Whitepaper: SQL Server 2005 Physical Database Storage Design
2006/06/15Microsoft 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:
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
"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.