SQL Server Tips - How to improve your tempdb performance

Tempdb is used for sorted data, temporary object (such as tables, stored procedures or cursors), indexes if the option of SORT_IN_TEMPDB is specified and transaction isolation level snapsnot. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. You can improve it by using these methods :

• Tempdb has to reside in separate disk with primary files, data files and log files. put them to a fast I/O to guarantee good performance. You can use this code to move tempdb data files to other location.
  
  USE master;
  GO
  ALTER DATABASE tempdb
  MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
  GO
  ALTER DATABASE tempdb
  MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
  GO
  
  verify the changes using this query :
  SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');
  GO
  
• Increase the number of data files in tempdb to maximum disk bandwith and reduce contention in allocation structures.- Set the file growth increment percentage to a reasonable size to avoid the tempdb growing by small value.
• Implement trace flag -T1118. Under this trace flag, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages.
• Examine the query plans of the queries associated with the user session. If the amount of tempdb space used by a query is higher than expected, try to modify the query to minimize tempdb usage. For example, an index may be missing, or the query may use a cursor and could be rewritten to use a set-based statement instead.