Database Reference
In-Depth Information
existing drives or mount points or adding in more. These operations can be done
online without any disruption to your running database. Auto Grow should be
configured so that in the worst-case scenario, the maximum growth of all the files
on the drive or mount point combined will never exceed the total capacity.
In the example in
Figure 6.6
, we have split out the Temp DB files onto separate drive
letters from the data files of the production database. If you have a very large database
or your database will have heavy IO demands on Temp DB, it makes sense to split it out
onto its own drives and a separate IO controller.
Figure 6.6
Sample SQL Server file system layout—data files separate from Temp DB.
In databases that make extremely heavy use of Temp DB, such as peaking at more than
50% of total database size, it might make sense for each Temp DB file to be on its own
drive or mount point to allow each file access to more parallel IO resources. This
assumes that the underlying storage infrastructure can deliver more IO in parallel, which
we will cover later in this chapter.
In an AlwaysOn Failover Cluster Instance, an additional reason to separate Temp DB
onto different drives or mount points from other data files is that it can be hosted locally
to the cluster node, rather than on the shared storage.
This makes a lot of sense given
that the Temp DB data doesn't survive instance restarts.
This allows you to
optimize the performance of Temp DB without impacting the data files and log files that
are shared between cluster nodes. If you have extreme Temp DB IO requirements, you
could consider locating it on local flash storage, but consider that this would prevent the
guest restarting in a VMware HA event. In this case, the cluster node would be
unavailable if the local flash storage failed, which would trigger a failover to another
