Here is what I explored from various sources.
To optimize tempdb performance, pay attention to the physical disk configuration, file configuration, as well as some settings in the database.
Physical disk configuration
tempdb must be on its own dedicated physical disks . This allows you to split I / O transactions with other volumes on SQL Server.
To move tempdb to a new disk, use ALTER DATABASE . This is the key T-SQL command to perform this operation. Microsoft offers a good example in SQL Server 2005 Books Online. The title of the article is ALTER DATABASE (Transact-SQL), and the specific section is "G. Moving tempdb to a new location.
Tempdb is a very high write database. Therefore, a RAID 5 array is not suitable for this. You should put tempdb in a RAID 0 or RAID 10 array , as they are optimized for high-write applications. If you can provide additional RAID 0 or RAID 10 arrays for each physical database file for tempdb, you will get increased performance.
Database files
You must have one physical file per processor core on the server . So, if you have a dual-core dual-core server, you should have four physical database files for the tempdb database. When adding more database files, it is important to set up files with the same initial size and the same growth settings . Thus, SQL Server will write data to files as evenly as possible.
Database File Size
The size of the tempdb database can affect system performance. For example, if the size specified for tempdb is too small, part of the system processing load can be considered using tempdb autoload to the size needed to support the workload each time you restart the instance of SQL Server . You can avoid this overhead by increasing the size of the tempdb data and the log file.
Determining the appropriate size for tempdb in a production environment depends on many factors, including the existing workload and the SQL Server features used. Microsoft recommends analyzing your existing workload by completing the following tasks in the SQL Server test environment:
- Set autoload for tempdb (in a test environment!).
- Run individual requests or workload trace files and monitor tempdb space usage.
- Perform index maintenance operations such as restoring indexes and controlling tempdb space.
- Use the space utilization values from the previous steps to predict the total utilization of the workload; adjust this value for the predicted parallel activity, and then set the tempdb size accordingly.
The minimum size guidelines for tempdb are as follows:
Envir. Size DB Size (MB) Log Size (MB)
Database settings
You can increase tempdb performance by disabling automatic update statistics , which will save your tempdb performance. You can also set the option to automatically generate statistics to false .
Disclaimer: settings must be changed with care. Depending on the type of load that you place on your tempdb, changing the settings can adversely affect system performance.
To achieve optimal tempdb performance, follow the recommendations and recommendations in Optimizing tempdb performance .
How to track usage of tempdb?
Starting from disk space in tempdb can cause significant crashes in the SQL Server production environment and can prevent applications from running after operations are completed.
You can use the sys.dm_db_file_space_usage dynamic management sys.dm_db_file_space_usage to control the disk space used by these functions in tempdb files. In addition, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic control views to control the allocation or deallocation of a page in tempdb at the session or task level.
These views can be used to identify large queries, temporary tables, or table variables that use a large amount of tempdb disk space. There are also several counters that can be used to control the free space available in tempdb, as well as resources that use tempdb.
References: