When it comes down to it, the reason the tempdb fills up is because the query is returning way too much data, and you need to find out why and fix it. Often, it is because the query allows a user to specify one or more criteria that was not specific enough, and way too much data was returned.
In fact, it is a good idea to pre-size the tempdb database anyway, so that when SQL Server is restarted, it will automatically be set at a specific size. This way, when the tempdb really does need to be used, time doesn't have to be wasted as the tempdb has to autogrow.
Predicting the ideal size for the tempdb is not easy. Generally, I take a guess, and then watch it, seeing how good my guess is. And if I am wrong, then I will make the necessary adjustments.
Many people think that tempdb is only used for #temp tables. When in fact, you can easily fill up tempdb without ever creating a single temp table. Some other scenarios that can cause tempdb to fill up:
* any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;
* if the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;
* DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;
* DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;
* large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb;
* any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;
* use of an ODBC DSN with the option 'create temporary stored procedures' set can leave objects there for the life of the connection.
The following will tell you how tempdb's space is allocated:
The following should give you some clues as to which table(s) consume most of the space in the data file(s) -- this will help you narrow down any transactions that are either taking a long time or repeatedly being left in limbo:
SELECT OBJECT_NAME(id), rowcnt
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC
The higher rowcount values will likely indicate the biggest temporary tables that are consuming space. And while it won't tell you everything, since tempdb is used for internal I/O and other processes such as sorting, it may help you narrow down the stored procedure(s) that are causing the growth (you can query INFORMATION_SCHEMA.ROUTINES for ROUTINE_DEFINITION LIKE '%#table_name%' from above).
In addition to this, you can use Profiler to watch for events like database file auto grow and log file auto grow. If this is happening often, then you know that the space you've allocated to tempdb is not sufficient.
You can also watch performance monitor's counter for PhysicalDisk: CurrentDiskQueueLength on the drive where tempdb exists. If this number is consistently greater than 2, then there is likely a bottleneck in disk I/O.
Here are some suggestions for maintaining a healthy tempdb:
* Make sure that tempdb is set to autogrow -- do *NOT* set a maximum size for tempdb. If the current drive is too full to allow autogrow events, then buy a bigger drive, or add files to tempdb on another device (using ALTER DATABASE) and allow those files to autogrow. You will need at least one data file and at least one log file in order to avoid this problem from halting your system in the future.
* For optimal performance, make sure that its initial size is adequate to handle a typical workload (autogrow events can cause performance to suffer as it allocates new extents). For an approach to setting a non-default size for tempdb.
* If possible, put tempdb on its own physical disk, array or disk subsystem (see KB #224071 for more information).
* To prevent tempdb log file growth, make sure tempdb is in simple recovery mode.
* Try to make sure you have covering indexes for all large table that are used in queries that can't use a clustered index / index seek.
* Batch larger heavily-logged operations (especially deletes) that *might* overflow into tempdb into reasonable 'chunks' of rows, especially when joins are involved.
* Pore over your code for potential uncommitted transactions and other elements from the list at the top of the page.
* In general, try to make your code as efficient as possible... avoid cursors, nested loops, and #temp tables if possible.
Check out the WebCast in KB #834846 for some ideas from Microsoft about administering and maintaining TempDB. http://support.microsoft.com/default.aspx/kb/834846