Data Storage Internals - Pro SQL Server Internals - page 3

(name = N'OrderEntry_Orders_F2', filename = N'p:\OEOrders_F2.ndf')
log on
(name = N'OrderEntryDb_log', filename = N'l:\OrderEntryDb_log.ldf')

You can see the physical layout of the database and data files in Figure 1-1 . There are five disks with four data files
and one transaction log file. The dashed rectangles represent the filegroups.

Figure 1-1. Physical layout of the database and data files

The ability to put multiple data files inside a filegroup lets us spread the load across different storage drives,
which could help to improve the I/O performance of the system. Transaction log throughput, on the other hand, does
not benefit from multiple files. SQL Server works with transactional logs sequentially, and only one log file would be
accessed at any given time.

■ We will talk about the transaction Log internal structure and best practices associated with it in Chapter 29,
“Transaction Log Internals.”

Note

Let's create a few tables, as shown in Listing 1-2. The Clients and Articles tables are placed into the Entities
filegroup. The Orders table resides in the Orders filegroup.

Listing 1-2. Creating tables

create table dbo.Customers
(
/* Table Columns */
) on [Entities];

create table dbo.Articles
(
/* Table Columns */
) on [Entities];

create table dbo.Orders