Data Storage Internals - Pro SQL Server Internals - page 6

drop database Dummy
go

dbcc traceoff(3004,3605,-1)
go

If Instant File Initialization is not enabled, the SQL Server error log indicates that SQL Server is zeroing out the
.mdf data file in addition to zeroing out the log .ldf file, as shown in Figure 1-4 . When Instant File Initialization is
enabled, it would only show zeroing out of the log .ldf file.

Figure 1-4. Checking if Instant File Initialization is enabled - SQL Server error log

Another important database option that controls database file sizes is Auto Shrink. When this option is enabled,
SQL Server shrinks the database files every 30 minutes, reducing their size and releasing the space to operating
system. This operation is very resource intensive and rarely useful, as the database files grow again when new data
comes into the system. Moreover, it greatly increases index fragmentation in the database. Auto Shrink should never be
enabled . Moreover, Microsoft will remove this option in future versions of SQL Server.

■ Note We will talk about index fragmentation in greater detail in Chapter 5, “Index Fragmentation.”

Data Pages and Data Rows
The space in the database is divided into logical 8KB pages . These pages are continuously numbered starting with
zero, and they can be referenced by specifying a file ID and page number. The page numbering is always continuous
such that when SQL Server grows the database file, new pages are numbered starting from the last highest page
number in the file plus one. Similarly, when SQL Server shrinks the file, it removes the highest number pages from
the file.
Figure 1-5 shows the structure of a data page.