Solved

tempDB and single data files - risks

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

635 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