Database Reference
In-Depth Information
performance impacts.
Caution
Instant File Initialization is not available when you're using Transparent Data
Encryption (TDE) or when trace flag 1806 is set (which disables Instant File
Initialization). Although IFI has a positive impact on performance, there are
security considerations. For this reason, using IFI may not be suitable for all
environments. See
http://technet.microsoft.com/en-
us/library/ms175935(v=sql.105).aspx
. Based on ou
r experience, in most
environments, the highlighted security considerations can be addressed by proper
controls and good system administration practice.
SQL Server File System Layout
We have covered how you determine how many files your databases need and how big
each file should be preallocated. We now need to assign these files to the appropriate
locations on the file system and configure the file system within the Windows operating
system to achieve the best possible performance. The file system layout may differ
slightly between database instances that are standalone, that use AlwaysOn Failover
Clustering, or that use AlwaysOn Availability Groups. We will discuss the possible
differences and give you recommendations based on our experience to help you achieve
performance that can be built in to your standardized base templates.
OS, Application Binaries, and Page File
The OS, application binaries, and page file should be separated from the data, Temp
DB, and log files in a SQL Server database. These components of the system generally
produce little IO, but we don't want any IO interfering with productive IO from the
database. For this reason, we recommend that OS, application binaries, and the page
file be on a separate drive (or drives) and IO controller from data files, log files, and
Temp DB.
Tip
Separate the operating system, application binaries, and page file from core
database files so they don't impact the performance of the SQL Server Database.
From a database storage performance perspective, any paging is bad and should be
avoided. Details of the page file and SQL Server memory configuration will be covered
in
Chapter 7
,
“
Architecting for Performance: Memory
.”
Chapter 7
will show you how to
