·
Should I use
1 file or multiple files for TempDB?
o
One
TempDB data file per physical corefor the SQL Server instance.This prevents
helps contention that can occur during peak tempdb activity.
o
The
data files for tempdb should all be the same size. This allows for optimal
proportional-fill performance.
o
All
data files have equal growth increment
o
No
data files have percent growth increment
·
Where (i.e.
what disks) should I create TempDB?
o
Put
the tempdb database on a fast I/O subsystem. Use disk striping if there are
many directly attached disks.
o
Put
the tempdb database on disks that differ from those that are used by user
databases.Tempdb should be separated and isolated from user databases.
This creates contention between the user databases and tempdb.
o
Tempdb
data files and log files should be on separate disks.
o
The
TempDB files should definitely not be on the system (C) drive. If
TempDB grows out of control, perhaps due to a large sort query, the system can
run out of hard drive space and fail to start.
·
What should
be the size of TempDB?
o
Unlike
user databases where you can probably estimate the growth rate of tables over
time based on the workload, estimating the size of TempDB is non-trivial.
o
The
size of TempDB can be affected for example by a change in query plans, version
store when there is a long running transaction and so on.
o
The
best way to estimate the size of TempDB is by running your workload in a test
environment. Once you have a good estimate of the size of TempDB, use Alter
Database command to set its size with a safety factor that you feel is
appropriate.
o
Don’t
rely on auto-grow to manage file sizes. You must set the appropriate size of
TempDB using Alter Database command.
o
Auto-grow
causes a pause during processing when you can least afford it
o
Auto-grow
leads to physical fragmentation
o
Tempdb
Data files should be large enough to handle the workload for all 24 hours of
the day.
References:
No comments:
Post a Comment