Database Reference
In-Depth Information
(
/* Table Columns */
) on [Orders];
Figure
1-2
shows physical layout of the tables in the database and disks.
Figure 1-2.
Physical layout of the tables
The separation between logical objects in the filegroups and the physical database files allow us to fine-tune
the database file layout to get the most out of the storage subsystem without worrying that it breaks the system. For
example, independent software vendors (ISV), who are deploying their products to different customers, can adjust the
number of database files during the deployment stage based on the underlying I/O configuration and the expected
amount of data. These changes will be transparent to developers who are placing the database objects into the
filegroups rather than into database files.
It is generally recommended to avoid using the PRIMARY filegroup for anything but system objects. Creating a
separate filegroup or set of the filegroups for the user objects simplifies database administration and disaster recovery,
especially in the case of large databases. We will discuss this in great detail in Chapter 30, “Designing a Backup
Strategy.”
You can specify initial file sizes and auto-growth parameters at the time that you create the database or add new
files to an existing database. SQL Server uses a proportional fill algorithm when choosing to which data file it should
write data. It writes an amount of data proportional to the free space available in the files—the more free space a file
has, the more writes it handles.
■
It is recommended that all files in a single filegroup have the same initial size and auto-growth parameters with
grow size being defined in megabytes rather than by percent. This helps the proportional fill algorithm to balance write
activities evenly across data files.
Tip
Every time SQL Server grows the files, it fills the newly allocated space with zeros. This process blocks all sessions
that are writing to the corresponding file or, in case of transaction log growth, generating transaction log records.
SQL Server always zeros out the transaction log, and this behavior cannot be changed. However, you can control
if data files are zeroed out or not by enabling or disabling
Instant File Initialization
. Enabling Instant File Initialization
helps speed up data file growth and reduces the time required to create or restore the database.
