Chapter 9. Data management
A significant percentage of SQL Server performance and administration problems stem from poor choices in laying out and sizing database files. By default, a database has a single data and log file, both of which are located in the same directory and set to automatically grow by small amounts. With the exception of the smallest databases, such a configuration is almost guaranteed to constrain performance, increase disk fragmentation, and lead to various other administration challenges, particularly with large databases and/or those with high usage rates.
Successful database administration requires a solid understanding of database file layout, sizing, and management strategies. In this chapter, we’ll begin by exploring database file configuration, including volume separation and initial size. We’ll then take a look at using secondary filegroups and their performance and administration benefits.
We’ll conclude the chapter with coverage of two significant new data management features introduced in SQL Server 2008: FileStream and data compression.