Solved

tempDB and single data files - risks

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 setup several different housekeeping processes for a SQL Server.

840 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