Configuration Best Practices for SQL Server Tempdb–Placement
This
part of a three-part article consolidating a number of best practices
for configuring SQL Server tempdb focuses on tempdb placement. You won’t
just find prescriptive rules here, but also the background to the
recommendations and guidance on how to choose the best configuration for
any particular environment. In particular this section covers the
following:
- Where to place tempdb
- Initial sizing and autogrowth (see part 2)
- Configuring multiple files (see part 3)
Tempdb file placement
It’s quite a well-known best practice to separate
data, transaction logs, and tempdb, and if you knew that already, are
you sure you know why? The origin of this recommendation lies with the
separation of types of workload between different physical storage, i.e.
separate physical disks.
This is still a valid recommendation for environments
where you can guarantee that separation, but more commonly we see
customers deploying SQL Server in a shared storage environment, where
physical separation is much harder to achieve and usually isn’t even
necessary for performance reasons.
It is still a good idea however to maintain separation
to help with manageability so that potential problems are easier to
isolate. For example, separating tempdb onto its own logical disk means
that you can pre-size it to fill the disk without worrying about space
requirements for other files, and the more separation you implement the
easier it is to correlate logical disk performance to specific database
files.
At the very minimum you should aim to have one logical
disk for data files, one for transaction log files, and one for tempdb
data files. I prefer to keep the tempdb data files on their own drive so
they can be sized to fill the drive and place the tempdb log files with
the user database log files where there should be enough free disk
space for unexpected autogrow events for any log file.
Local tempdb for failover cluster instances
Until SQL Server 2012, a failover cluster instance of SQL Server required all
its database files to be on shared disk resources within the cluster.
This was to ensure that when the instance failed over to another node in
the cluster, all its dependent disks could be moved with it.
Nothing in tempdb persists after a restart and it’s
effectively recreated every time. The failover process for a clustered
instance involves a restart of SQL Server so nothing in tempdb needs to
be moved across to the other node and there’s no technical reason why
tempdb should be on a shared disk.
In SQL Server 2008 R2 you could force tempdb onto a
local disk but it wasn’t supported; in SQL Server 2012 it’s fully
supported and very straightforward to implement. All you need to do is
use ALTER DATABASElike this:
You will see messages after execution that look like this:
That’s all there is to it. All you need to remember is
that you need to have the same path available on all cluster nodes, and
the service account needs to have read/write permission so that tempdb
can start after failover.
Why might a local tempdb be useful?
There are two reasons why you might want to move tempdb from a shared disk to a local disk, and both are related to performance.
The first reason is that the relatively recent
increase in cost effective, ultra-fast solid-state storage presents an
opportunity to achieve significant performance gains on servers
experiencing heavy tempdb usage. The challenge prior to SQL Server 2012
was that solid-state storage cards, like those provided by FusionIO and
Texas Instruments, plug straight into a server’s motherboard to avoid
all the overhead of traditional storage buses. This made it very
difficult to use them at all in failover cluster instances and now they
can be used for the discrete task of running tempdb.
The second reason you might want to use a local tempdb
is to take I/O requests off your shared storage to improve the
performance of the shared storage. We used this to great effect for one
customer who was really at the peak of their SANs performance capacity; a
FusionIO card was placed in each node of several failover clusters and
all tempdb activity was re-directed locally. Even though tempdb
performance was never bad before, the result was a significant reduction
in load against the SAN which extended its life by an additional six
months.
Nenhum comentário:
Postar um comentário