Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

tempDB and single data files - risks

Posted on 2014-04-10
4
Medium Priority
?
530 Views
Last Modified: 2014-04-29
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?
0
Comment
Question by:pma111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 39991837
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
 
LVL 3

Author Comment

by:pma111
ID: 39991977
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
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39992224
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
 
LVL 3

Author Comment

by:pma111
ID: 39993587
Yes mainly OLTP info systems driven by our SQL database apps.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question