Role of TempDB

Question

Post reply

Role of TempDB

karthik M

One Orange Chip

Points: 29139
More actions
November 5, 2007 at 6:08 am

#62579

Hi Experts,
I have read so many documents and articles about tempdb and its size. But all those documents are pointing the same concept.
'You should be very care while specifying the tempdb size'. How to decide the size of tempdb ? In which criteria the tempdb size is designed ? is there any calculation available to design tempdb size ?
karthik

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

karthik M

One Orange Chip

Points: 29139

November 6, 2007 at 2:36 am

#748555

Answer pls....

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 2

karthik M

One Orange Chip

Points: 29139

November 7, 2007 at 12:49 am

#748990

Any output ?

karthik

Oana Schuster SSCommitted Points: 1642 More actions · Answer 3

Hi,

Maybe this will help:

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#EHFAC

Oana.

EdVassie SSC Guru Points: 60476 More actions · Answer 4

This is some basic advice about how to decide the size of tempdb

tempdb needs to be sized so that it will not grow while your server is in use.

This is because growing any database file means the query that needs the extra space is stuck until the space is available. It is bad practice to make end-users wait because of a configuration item such as this. Therefore is becomes important to ensure tempdb has enough space for all the work SQL needs to do from the moment SQL is statred.

For a new server or if a new application is added, it can be difficult to avoid growth. The thing to do here is note how big tempdb grows to, then ALTER the tempdb database files to set the initial size to be a bit bigger than the size actually used. The next time SQL is started tempdb will use the new size from the time of startup.

If tempdb is large, split it into multiple files to improve I-O. I would call tempdb large if it needs to be over 10 GB. There are a number of articles on the web about using multiple files with tempdb, but if you are new to SQL Server then get more experience before you look at more advanced features such as this.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Thanks Edvassie !

Any experts want to share their opinion in 'tempbd size'. Your inputs are highly appreciable.

karthik

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

There are no good techniques for preliminary sizing of the tempdb. This is because it's usage has almost no correlation with anything else.

The short answer is this. It can be too small, but it is hard to make it too big.

Stay away from autogrow, but it is strongly recommended to not do by a percentage. Make it fixed if you must.

A method that I have used it the past that works for the lazy ones around is this:

1) Create one temp file for every PROCESSOR (I have 8)

2) SIZE THEM ALL THE SAME!!!! - Say 250M (Mine are 2G each)

3) Set autogrowth for all to NONE

4) For last file change autogrowth to 64M and unlimited.

5) MONITOR GROWTH regularly

If you grow, then increase ALL to be equal again.

If not, you can consider reducing size, but make sure ALL are the same size.

Repeat this until you don't grow EVER!

Bob Fazio SSChampion Points: 10794 More actions · Answer 7

Oh forgot to add.

DO NOT SHRINK YOUR TEMPDB!!!!! this really screws up the whole thing.

John Maloney-484904 Valued Member Points: 63 More actions · Answer 8

ok tempdb is used for sorting, aggregation, distincts, unions, temp tables and table variables, row versioning. It can also be used for index building.

Oh and cursors

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

Bob Fazio (11/27/2007)
There are no good techniques for preliminary sizing of the tempdb. This is because it's usage has almost no correlation with anything else.
The short answer is this. It can be too small, but it is hard to make it too big.
Stay away from autogrow, but it is strongly recommended to not do by a percentage. Make it fixed if you must.
A method that I have used it the past that works for the lazy ones around is this:
1) Create one temp file for every PROCESSOR (I have 8)
2) SIZE THEM ALL THE SAME!!!! - Say 250M (Mine are 2G each)
3) Set autogrowth for all to NONE
4) For last file change autogrowth to 64M and unlimited.
5) MONITOR GROWTH regularly
If you grow, then increase ALL to be equal again.
If not, you can consider reducing size, but make sure ALL are the same size.
Repeat this until you don't grow EVER!

I'm just starting at a new company, and we have lots of space issues. It looks like the previous admin set up tempdb with 4 data files, but they are all in the same location ?? Is there any point to having this setup ??

I want to move tempdb to another drive (along with a bunch of other file moves) to organize things better. We will be getting a SAN in the next month or 2, but have to do some juggling in the mean time.

justin harper-451435 SSC-Addicted Points: 483 More actions · Answer 10

Yes it does make sense to break tempdb in to different files even if they are on the same drive. This still helps to alleviate contention issues with tempdb and among the different processor threads.

You are right that having different tempdb files and spreading them accross drives would be even better but there is still some benefit to different files on the same drive.