Database Reference
In-Depth Information
Caution
You should monitor your database for signs of contention in the underlying
storage subsystem. You can do this by querying the top wait states and checking
PAGEIOLATCH. If you see excessive waits, that is a sign of database IO
contention, and you may need to adjust your file system layout or underlying
storage that supports your virtualized databases.
slow-disk-i-o-in-sql-server.aspx
.
The example in
Figure 6.5
illustrates how you might arrange your database files for a
standalone instance. If you start to see IO contention and your database is growing (or is
expected to grow) very large or makes a lot of use of Temp DB, then you may wish to
separate out Temp DB files onto their own drive letters or mount points. This would
remove the chance of Temp DB IO activity impacting the IO activity of your other data
files and allow you to put Temp DB onto a separate IO controller (point 2 of our file
system layout objectives).
Figure 6.5
Sample SQL Server file system layout—Temp DB with data files.
Having a single Temp DB file on the same drive with two data files in general will
balance the IO activity patterns and achieve acceptable performance without an
excessive number of drives to manage. The reason for this layout is more likely on a
standalone instance instead of with a clustered instance, which will become clear on the
next page.
Tip
You should size each drive letter or mount point so that the preallocated database
files on it consume no more than 80% of the available capacity. When you need
to grow the capacity of your database, you have the option of either extending the
