VIII. IO optimization (6) Optimize tempdb Performance

I. Introduction to tempdb

1. Concept

Tempdb is a system database that can be used by all users connected to the SQL Server instance.

Every time SQL Server is started, tempdb is re-created to maintain a clean copy of the database at system startup. When the connection is closed, temporary tables and stored procedures are automatically deleted, and no active connections are established after the system is closed. Therefore, tempdb does not store any content from one SQL Server session to another.

Backup and restoration of tempdb are not allowed.

2. Objects saved by tempdb

1) explicitly created user objects

These objects are explicitly created by the user. It exists in the scope of user sessions, and can also be located in the scope of routine stored procedures, triggers, or user-defined functions used to create objects.

Including: Tables and index systems, or user-defined), temporary tables and global indexes, or partial ones), table variables, tables returned by Table value functions.

2) internal objects created by the Database Engine

These internal objects are created by the database engine as needed to process SQL Server statements. You can create and delete statements in the statement scope. Each internal object must have at least nine pages: One IAM page and one eight consecutive pages.

Includes: worksheets for cursor, offline, and temporary large object LOB) storage; worksheets for HASH connections or HASH aggregation; create or regenerate indexes if SORT_IN_TEMPDB is specified) or the intermediate sorting results of some group by, order by, or UNION queries.

3) version storage Zone

The version storage area is a collection of data pages. It contains the data rows required to use the row version control function and is mainly used to support snapshot transaction isolation level, and some other new features to improve database concurrency performance. There are two main types: public version storage area and online index generation version storage area.

Including modifying the row version generated by a transaction in a database that uses the Snapshot isolation level or the submitted isolation level based on Row version control; the row version generated by the data modification transaction to implement online index operations, multiple active result sets, and the AFTER trigger.

3. Performance Improvement of tempdb

In SQL Server, the performance of tempdb is improved in the following ways:

1) temporary tables and table variables may be cached. The cache allows you to quickly delete and create temporary objects and reduce the contention for page distribution.

2) The allocation page locks protocol has been improved. This reduces the number of UP locks used.

3) reduces the log overhead of tempdb. This reduces the disk I/O bandwidth usage on the tempdb log file.

4) The algorithm for mixing pages in tempdb is improved.

Ii. Monitor the size of tempdb

The size and physical location of the tempdb database may affect the system performance. The size of tempdb may affect system performance. For example, if the size of tempdb is too small, the system may be busy with automatic database growth every time SQL Server is started, rather than supporting workload requirements. You can avoid this overhead by increasing the size of tempdb.

If the disk space in tempdb is insufficient, it may seriously damage the SQL Server production environment and make the running application unable to complete the operation.

You can use the sys. dm_db_file_space_usage dynamic management view to monitor the disk space used by these functions in the tempdb file.

To monitor page assignment or page release activities in tempdb at the session or task level, you can use the dynamic management view sys. dm_db_session_space_usage and sys. dm_db_task_space_usage. These views can be used to identify large queries, temporary tables, or table variables that use a large amount of disk space in tempdb.

You can also use several counters to monitor the available space in tempdb and use tempdb resources.

Http://technet.microsoft.com/zh-cn/library/ms176029 (v = SQL .105). aspx

The file size and file growth parameter values are used each time you create tempdb. For example, if you increase the size of the tempdb data file to 20 MB and increase the file increment to 15%, the new value takes effect immediately. If subsequent transaction activities increase the size of tempdb, the data file size is returned to 20 MB each time the SQL Server instance is restarted.

Iii. General principles of tempdb

1. Number of Files

Create enough files as needed to maximize the disk width. Using multiple files can reduce tempdb storage contention and achieve greater scalability. However, do not create too many files because this operation may reduce performance and increase management overhead.

As a general principle, create a data file for each CPU in the server to interpret any associated mask settings), and then adjust the number of files as needed.

2. recovery mode

Set the recovery mode of tempdb to SIMPLE. This mode automatically recycles the log space to maintain a small space requirement.

3. Automatic Growth

You can pre-allocate space for all tempdb files by setting the file size to a sufficient value for a typical workload in the environment. This prevents the performance of tempdb from being affected due to too many extensions.

Conclusion:

Plan the size of the tempdb database and use multiple files based on the number of CPUs.

This article is from the "MSSQL we have Chased Together" blog. For more information, contact the author!