SQL 2005 - tempdb

MS suggests your tempdb be atleast 25% of your largest database. What does this mean and give you give me some examples to explain this?

more ▼

asked Jan 09, 2010 at 07:42 AM in Default

Rakesh gravatar image

31 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

MS recommendation is not always the best for your implementation. It is as always, it depends. As you already know, tempdb is used to sort, aggregate etc and depending on how your database is used, you need enough space for that. But lets say you have a database where the largest table is larger than 25% of the whole database, and you have some terrible queries to select, aggregate and sort the whole table, 25% of the database will not be enough. And to make things more complicated, if you activate row-versioning you will even more space in tempdb, because every updated row is stored in the "version store" in tempdb. At first when I activated row-versioning I underestimated the space requirements.

more ▼

answered Jan 09, 2010 at 10:51 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

(comments are locked)
10|1200 characters needed characters left

That is a good rule of thumb for the amount of space that may be needed by TempDb, but it is only a rule of thumb. As Hakan has said, it is possible you will need much more. In addition to the excellent scenarios he listed, if you have numerous small databases with numerous queries being run simultaneously tempDB may need to be far larger than 25% of the largest.

more ▼

answered Jan 09, 2010 at 01:08 PM

TimothyAWiseman gravatar image

15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

Rakesh, Timothy and Hakan are right with their answers but to be specific to your question, it means that the size of the tempdb can be set to a certain size to optimise performance. This can be done from T-SQL or via SSMS. Once you have an idea of what size you want to set the Tempdb files to either run this script

USE [master]
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 128000KB )

(substituting the size that you want it to be set to) or right click on the database, select properties and the the Files tab and set the values in that dialog.

The 25% is probably a figure that Microsoft decided on as a best fit for most scenarios. As other answers have explained there are many ways that 25% of the largest database would not be optimal. It wouldn't be awful, it just might not be the best possible.

more ▼

answered Jan 09, 2010 at 03:25 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

Depending on the disk system and number of cpu:s you may need more than one file to allocate the space, to prevent contention in tempdb, but that is a completely different question and has been debated here before.
Jan 09, 2010 at 04:05 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

I think the best thing to do is just make sure that your tempdb is fast, and has enough space to grow as needed. It gets used for all kinds of things, but if you can put it on SSD, or RAID 10 at the very least, then you'll see a significant performance improvement.

Size-wise - work it out in your load-testing. It's frustratingly hard to judge. Once you know how large it's likely to get, set the size (which will stop it needing to grow to that size if the service is restarted), and then continue to monitor it.

25% is only a guide. It could be much larger, or much smaller. You should also focus on getting your indexes as good as possible, to avoid as much sorting and hashing.

more ▼

answered Jan 11, 2010 at 03:34 AM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left

I always try to keep as much space as possible available for tempdb if I cannot get good estimates from the supplier of the system. As an example, one db I am working with is now about 400 GB in size, and the supplier said that I would need 5 GB of tempdb disk. I didnt believe them, and I was right. We are currently at about 50 GB of tempdb in normal operation, and 100 GB i extreme cases. So 25% is a good estimate in this case, however it's not good enough. And we didn't know from the start what would be needed. What I have learned from this is to get much more than the estimate, and even so, to keep an eye on the tempdb disk and extend it before it fills up. But you can never say inadvance exactly how much you need. Say you have a table at 25% size of your db as in Hakans example. What if a bad query locks up / orphans, and you get another one of those running at the same time? Or several? In that case you might need more than 100% of your db size for tempdb to avoid running out of space.

more ▼

answered Jan 11, 2010 at 05:58 AM

Steinar gravatar image

1.7k 3 4 6

I agree, and if you don't allocate enough space from the beginning and the database need to grow your performance will decrease while expanding the database and your processes may be blocked due to the exclusive db lock during the expand process.
Jan 11, 2010 at 11:22 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 09, 2010 at 07:42 AM

Seen: 2361 times

Last Updated: Jan 09, 2010 at 10:56 AM