Solved

tempDB and single data files - risks

Posted on 2014-04-10
4
503 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 22

Accepted Solution

by:
Steve Wales earned 500 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 22

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

710 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