Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 237

Once IFI is working, you can set autogrow to be large i xed amounts for data i les. 50MB or 500MB
are good values depending on the size of the database, but any size is created virtually instantly so
you avoid any downtime.
NOTE If you've coni gured multiple data i les and you want to allow autogrow,
consider enabling trace l ag 1117, which will force all data i les to grow uniformly
so you don't break the load balancing between i les.
For transaction log i les, however, you need to be a lot more conservative and use a i gure that
balances the time it takes to autogrow and the usefulness of the extra space. Autogrowing by 1MB,
for example, is quick, but you might need to do it so often that it becomes a bottleneck. Autogrowing
by at least 10MB for the transaction log is a good place to start, but you may need it to be higher to
provide enough space to avoid autogrowing again quickly. The best option is to avoid autogrowing
in the i rst place by correctly sizing the i les.
Confi guring Multiple Tempdb Data Files
The use of multiple data i les as an aid to reducing allocation contention problems for tempdb was
covered earlier in the chapter. Another reason you might want to use multiple data i les is to increase
the I/O throughput to tempdb — especially if it's running on very fast storage.
When you create multiple data i les they will all be in the primary i legroup and SQL Server uses a
proportional i ll algorithm to determine which i le to use for each request to create an object. If all
the i les are exactly the same size, then SQL Server uses the i les in a “round robin” fashion,
spreading the load equally across the i les. This is, of course, exactly what you want.
Microsoft recommends up to a 1:1 mapping between the number of i les and logical CPUs because
during testing of massive workloads they've seen performance benei ts, even with hundreds of
data i les.
A more pragmatic approach however, is to have a 1:1 mapping between i les and logical CPUs up to
eight , and then add i les if you continue to see allocation contention or if you're looking to push the
I/O subsystem harder. The performance benei t from adding i les diminishes each time, and in our
experience, eight is the sweet spot, especially if you're implementing this as a pro-active measure.
Whether or not you coni gure multiple data i les as a best practice on all your SQL Servers or just
on those for which you've detected issues is a choice only you can make. However, you might want
to coni gure them on all the servers you work with as a proactive measure, as it's hard to see a
downside.
SUMMARY
This chapter introduced the concept of, and many uses for, the tempdb database in SQL Server
2012, as well as some of the most common problems you might encounter and how to avoid them.