Is it possible to estimate the space requirement for TEMPDB or factors that are affecting space?
No straight answer to say yes you need so and so value to set on TEMPDB for such space requriement. By default it is difficult to estimate the tempdb space requirement for an application. In general and one of the best practice is to allow 20% more space on tempdb as compared to the user database or number of user databases.
There are various factors such as processes & connections from your application that you need to consider for the space requirement in TEMPDB data files, the following are most important factors:
• Query or Queries (with no hints)
• Triggers (too many)
• Online index creation (specific to SQL 2005)
• Temporary tables, table variables, and table-valued functions (TVF blog post)
• DBCC CHECKs (frequently performed)
• LOB parameters (Large objects in database)
• Cursors (too many)
• Service Broker and event notification (if you are using)
• XML and LOB variable
• Query notifications
• Database mail
• Index creation (fillfactor)
• User-defined functions
There is not hard-coded carving that every SQL Server instance may use the above factors and this depends on database design and query optimization. Also this is not a strict guidline and it vary from installation to installation.
TEMPDB Usage: You may be aware that specifically SQL Server 2005 caches temporary objects and when table-valued functions, table variables, or local temporary tables are used in a stored procedure, function, or trigger, the frequent drop and create of these temporary objects can be time consuming. This is where you will see lot of contention within the TEMPDB database and its system catalog tables and allocation pages. Though they are chached that means dropping and creating temporary objects is very fast, it is more dependant on the available memory on the server. This will have a trend to slow down the performance even for small queries.
One of the Technet article refers that, SQL Server behaviour is when a temporary object is dropped, it does not remove the catalog entry for the object. If a temporary object is smaller than 8 MB, then one data page and one IAM page are also cached so that there is no need to allocate them when re-creating the objects. If a temporary object is larger than 8 MB, defer drop is used. Only when there is a issue of TEMPDB is low on space, SQL Server frees up the cached temporary objects. You can drop the associated stored procedure(s) or free the procedure cache to get rid of these temporary tables.