SQL 2005 - tempdb

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.

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.

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]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 128000KB )
GO

(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.

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.

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.