The impact of Tempdb on SQL Server performance optimization and tempdb Performance Optimization

The impact of Tempdb on SQL Server performance optimization and tempdb Performance Optimization

First, consolidate the basic knowledge of tempdb.

Introduction:

Tempdb is an important part of SQLServer, which is used to store temporary objects. Tempdb is an SQL Server writer. Both applications and databases can use tempdb as a temporary data storage zone. All users of an instance share a Tempdb. Obviously, this design is not very good. When the databases of multiple applications are deployed on the same server, the applications share tempdb, if developers do not pay attention to the use of Tempdb, these databases will affect each other and affect applications.

Features:

1. Any data in tempdb will not persist after the system is restarted. Because in fact, every time SQLServer is started, tempdb will be re-created. This feature indicates that tempdb does not need to be restored.

2. The Restore Mode of tempdb is always set to "simple", and an error will be reported when you try to modify it. That is to say, the transaction log records of committed transactions are marked as reused after each checkpoint.

3. tempdb can only have one filegroup and cannot add more file groups.

4. tempdb is used to store three types of objects: user objects, internal objects, and version storage areas.

Next, we will analyze the problem by presenting the theme:

1. SQL Server System database Introduction

SQL Server has four important system-level databases: master, model, msdb, and tempdb.

Master: records all system-level information of the SQL Server System, including metadata of the instance range, endpoints, linked servers, and system configuration settings, it also records the existence of other databases and the location of these Data Query files. if the master node is unavailable, the database cannot be started.

Model: The template used for all databases created on the SQL Server instance. Because tempdb is created every time you start SQL Server, the model database must always exist in SQL Server.

Msdb: used by the SQL Server proxy to plan alarms and jobs.

Tempdb: a global resource available to all users connected to the SQL Server instance. It stores all temporary tables, temporary worksheets, temporary stored procedures, large types of temporary storage, and intermediate result sets, table variables and cursors. In addition, it is used to meet all other temporary storage requirements.

2. Internal Operating Principle of tempdb

Unlike other SQL Server databases, tempdb stops at SQL Server and automatically drops and re-create upon restart. by default, a new 8 MB (mdf file: 8 MB; ldf file: 1 MB, autogtouth is set to 10%) recovery model is a simple tempdb database.

After the tempdb database is created, DBA can create data objects, temporary tables, temporary stored procedures, and table variables in other databases and add them to tempdb. when tempdb activities are frequent, it can automatically increase because it is a simple recovery model, which minimizes log records and continuously truncates logs.

3. How to optimize tempdb reasonably to improve SQL Server performance

If SQL Server does not frequently access tempdb, tempdb will not affect the database; if the access is frequent, loading will increase, and the performance of tempdb will have an important impact on the entire database. it is important to optimize the performance of tempdb, especially for large databases.

Note: before optimizing tempdb, consider the impact of tempdb on SQL Server performance, and evaluate the problems and feasibility.

　　3.1 minimize the use of tempdb

Many activities of SQL Server occur in tempdb. In some cases, you can reduce excessive use of tempdb to improve the overall performance of SQL Server.

There are several places where tempdb is used:

(1) temporary tables created by the user. If you do not need them, try to avoid them. If you use temporary tables to store large amounts of data and frequently access data, add indexes to increase query efficiency.

(2) Schedule jobs. For example, dbcc checkdb occupies a large amount of resources in the system and uses tempdb. It is best to do this when SQL Server loading is relatively light.

(3) Cursors. Cursors will seriously affect performance and should be avoided as much as possible.

(4) CTE (Common Table Expression). It will also be executed in tempdb.

(5) SORT_INT_TEMPDB. This option is available when index is created.

(6) Index online rebuild.

(7) Temporary worksheet and intermediate result set, such as generated during JOIN.

(8) sorting result.

(9) AFTER and instead of triggers.

It is impossible to avoid the use of tempdb. If there is a tempdb bottleneck or issue, you should return to consider these issues.

　　3.2 reassign the space of tempdb

When SQL Server is restarted, an 8 Mb tempdb is automatically created, and the default value of automatic growth is 10%. 8 Mb is sufficient for small databases. however, for large databases, 8 Mb is far from enough to meet the needs of frequent SQL Server activities. Therefore, it will increase by 10%. For example, if 1 GB is required, it will take a long time, this period of time will seriously affect the performance of SQL Server. we recommend that you set the size of tempdb initialization when SQL Server is started (for example, MDF: 300 MB, LDF: 50 MB) or ALTER DATABASE. in this way, when SQL Server is restarted, tempdb will have enough space to be used to improve efficiency.

The difficulty lies in finding a reasonable initialization Size. It is a suitable value when SQL Server activities frequently and tempdb does not grow. You can set the value to Initial Size at this time; of course there will be more considerations. This is an example.

　　3.3 do not contract tempdb (if not necessary)

Sometimes we will notice that tempdb occupies a lot of space, but when the available space is relatively low, we will think of the shrink database to release the disk space. At this time, we should be careful, which may affect the performance.

As shown in: the space allocated by tempdb is 879.44 MB, and 45% of the space is idle. If shrink is lost, some disks can be released, however, if SQL Server has a large number of operations in the future, the tempdb space will not be enough and will automatically increase according to the proportion of 10%. in this case, the shrink operation is invalid and the system's loading will be added.

　　3.4 distribute tempdb files and other data files to unused I/O files.

Tempdb has high IO requirements. It is best to allocate it to a disk with high IO and allocate it to a non-used disk with other data files to improve read/write efficiency.

Tempdb is also divided into multiple files, which are generally divided according to the CPU. Several CPUs are divided into several tempdb data files. Multiple tempdb files can improve read/write efficiency and reduce IO activity conflicts.

Tempdb is an important part of SQL Server. The above is just a summary of tempdb, and further learning is needed...