Database Reference
In-Depth Information
In general, you should start with the number of files equal to the number of logical processors in case the system
has eight or fewer logical processors. Otherwise, start with eight data files and add them in groups of four in case there
is still contention in the system. Make sure that the files were created with the same initial size and same auto-growth
parameters, with growth size set in megabytes rather than by percent. This helps to avoid situations where files grow
disproportionately and, as a result, some files would process more allocations than others.
Of course, the best method of optimizing tempdb performance is to reduce unnecessary activity. You can refactor
your code to avoid unnecessary usage of temporary tables, avoid extra load to the version store because of triggers
or unnecessary optimistic transaction isolation levels, reduce the number of internal working tables created by SQL
Server by optimizing the queries and simplifying execution plans, and so on. The less unnecessary activity tempdb
has, the better it performs.
Summary
There are many different object types that can be created in tempdb by users. Temporary tables behave similarly
to regular tables. They can be used to store intermediate data during the processing. In some cases, you can split
complex queries into smaller ones by keeping intermediate results in temporary tables. While this introduces the
overhead of creating and populating the temporary tables, it can help Query Optimizer to generate simpler and more
efficient execution plans.
Table variables are a lightweight version of temporary tables. While they can outperform temporary tables in
some cases, they have a set of restrictions and limitations. These limitations can introduce suboptimal execution
plans when you join table variables with other tables.
Table-valued parameters allow you to pass rowsets as parameters to stored procedures and functions. They are
the one of the fastest ways to pass batches of rows from client applications to T-SQL routines.
The user's table in tempdb can be used as the staging area for data during ETL processes. This approach can
outperform the staging tables in the user databases due to the more efficient logging in tempdb. However, client
applications need to handle the situation when those tables and/or data disappear after a SQL Server restart or
failover to another node.
As opposed to regular tables in the user's database, temporary objects can be created at a very high rate and they
can introduce allocation map pages and system object contention in tempdb. You should create multiple tempdb data
files and use trace flag T1118 to reduce it.
Finally, you should utilize temporary table caching, which reduces the contention even further. You need to avoid
named constraints in temporary tables, and do not alter them to make them cacheable.
