Database Reference
In-Depth Information
Figure 6.3
SQL Database data file allocation.
Note
As
Figure 6.3
illustrates, there is only one transaction log file per database
and per Temp DB. Log files are written to sequentially, so there is no
benefit in having multiples of them, unless you exceed the maximum log file
size (2TB) between backups. There is a benefit of having them on very fast
and reliable storage, which will be covered later.
Size of Database Files
Let's start the discussion on data file sizes with some fundamentals that are important to
understand. Data files, unlike transaction log files, are accessed in parallel and the IO
pattern is more random. Temp DB files are accessed in parallel in a round-robin
fashion. This is why having more database files improves the parallelism of IO access
to storage. In effect, the IO is striped across the data files.
It is important to configure your database files to be equal size to start with. SQL Server
will write data to the available files evenly if they are the same size, as it uses a
proportional fill algorithm that favors allocations in files with more free space. If the
files are the same size, then each file will have the same free space. Having equally
sized files ensures even growth and more predictable performance.
Tip
Always configure SQL data files to be equal size to maximize parallelism and
overall system performance. This will prevent hot spots that could occur if
different files have different amounts of free space. SQL Server having equally
