tempDB and single data files - risks

Can I ask for a laymans interpretation (management jargon free summary) of the following issue in SQL Server. By reviewing a recent healthcheck report, they flag it as a medium severity issue that the "tempDB" only has 1 data file. Can anyone elaborate on the risks here, and what exactly the tempDB does in relation to MSSQL?

basically I am asking what is the risk in having only 1 data file for tempDB? How many should you have? Is this the same for every database?
LVL 3
pma111Asked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Depending upon the load in your system you can run into contention on tempdb with only a single data file.

TempDB is used for all sorts of stuff by every database in an instance - it's used for temporary storage, sorting, user temp tables among other things.

Do a google search and read up on SGAM contention in tempdb.

If you have multiple databases and a heavy load - processes may queue up as they try to get allocation in tempdb which is controlled by the SGAM for the data file (Shared Global Allocation Map).

If you have multiple datafiles (I believe a benchmark is 1 datafile per 2 processors - but that is only a rule of thumb as a place to start) you spread that load out.

You need to ensure that when you have multiple data files, that each data file is exactly the same size or the internal algorithm that determines which data file to use won't work properly.

Here's some links to get you started on the reading for this by some assorted SQL Server notables:

Adam Machanic: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/21/analyzing-tempdb-contention-a-month-of-activity-monitoring-part-21-of-30.aspx
Brent Ozar:  http://www.brentozar.com/blitz/tempdb-data-files/
Paul Randall: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx
http://technet.microsoft.com/en-us/library/ms175527%28v=sql.105%29.aspx
0
 
pma111Author Commented:
Thanks... is this only specific to the tempdb database or do you have more than one data file on all (dbs system or user)
0
 
Steve WalesSenior Database AdministratorCommented:
The things I've read about most seem to indicate that it's mainly a tempdb issue, but it depends upon your workload and what the system is used for.

You mainly OLTP ?  Then short in and out transactions probably aren't going to make a huge difference.

You have a data warehouse with huge volumes of data ?  Then maybe partitioning is a better idea.

Paul Randal ran a scenario where he did some testing (for what it is worth in this discussion), but might be worth a read: http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/
0
 
pma111Author Commented:
Yes mainly OLTP info systems driven by our SQL database apps.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.