Database Reference
In-Depth Information
avoid paging and optimize performance from the memory configuration of your SQL
Server.
File System Layout for Data Files, Log Files, and Temp DB
When considering the design of the file system layout for data files, log files, and Temp
DB, our objectives are as follows:
1.
Optimize parallelism of IO (Principle 1).
2.
Isolate different types of IO from each other that may otherwise cause a
bottleneck or additional latency, such as OS and page file IO from database IO, or
sequential log file IO from random data file IO.
3.
Minimize management overheads by using the minimum number of drive letters or
mount points required to achieve acceptable performance (Principle 5).
In order to achieve objectives 1 and 2, we recommend splitting out data files and Temp
DB files from log files onto separate drive letters or mount points. This has the effect of
killing two birds with one stone. By separating log files into their own drive or mount
point, you maintain the sequential nature of their IO access pattern and can optimize this
further at the hypervisor and physical storage layer later if necessary. If the log files
share a drive or mount point, the access pattern of that device will instantly become
random. Random IO is generally harder for storage devices to service. At the same
time, you are able to increase the parallelism needed for the IO patterns of the data files
and Temp DB files.
To achieve greater IO parallelism at the database and operating system layer, you need
to allocate more drive letters or mount points. The reason for this is that each storage
device (mount point or drive) in Windows has a certain queue depth, depending on the
underlying IO controller type being used. Optimizing the total number of queues
available to the database by using multiple drives or mount points allows more
commands to be issued to the underlying storage devices in parallel. We will discuss
the different IO controllers and queue depths in detail later.
As a starting point for standalone database instances, we recommend that you configure
a drive letter or mount point per two data files and one Temp DB file. This
recommendation assumes each file will not require the maximum performance
capability of the storage device at the same time. The actual number of drive letters or
mount points you need will be driven by your actual database workload. But by having
fewer drives and mount points will simplify your design and make it easier to manage.
The more users, connections, and queries, the higher the IO requirements will be, and
the higher the queue depth and parallelism requirements will be, and the more drive
letters and mount points you will need.
