Database Reference
In-Depth Information
database files, and the database file layout on the file system. If you don't have these
practices already in place, here we provide you with some guidelines to start with that
have proven successful.
Your SQL Server database has three primary types of files you need to consider when
architecting your storage to ensure optimal performance: data files, transaction log files,
and Temp DB files. Temp DB is a special system database used in certain key
operations, and has a big performance impact on your overall system. The file
extensions you'll see are .mdf (master data file), .ndf (for secondary data files), and .ldf
for transaction log files. We will go over all of these different file types later in this
chapter.
Number of Database Files
First, we need to determine the number of database files. There are two main drivers for
the number of files you will specify. The first driver is the number of vCPUs allocated
to the database, and the second is the total capacity required for the database now and in
the future.
Two design principles come into play here: The parallelism of access to storage should
be maximized by having multiple database files, and storage performance is more than
just the underlying devices. In the case of data files and Temp DB files, they are related
to the number of CPU cores allocated to your database.
Table 6.1
provides
recommendations from Microsoft and the authors in relation to file type.
Table 6.1
Number of Data Files and Temp DB Files Per CPU
Note
