Talk about how tempdb affects SQL Server performance optimization _mssql

First of all, to consolidate the basic knowledge of tempdb.

Brief introduction:

tempdb is SQL Server's system database, which is always an important part of SQL Server, and is used to store temporary objects. It is easy to understand that tempdb is a sketch board for SQL Server. Both the application and the database can use tempdb as a staging data store. All users of an instance share a tempdb. Obviously, such a design is not very good. When multiple application databases are deployed on the same server, the application shares tempdb, and if developers are not paying attention to the use of tempdb, these databases can interact with each other and affect the application.

Characteristics:

1. Any data in tempdb will not persist after the system restarts. Because of the fact that tempdb is recreated every time SQL Server starts. This feature shows that tempdb does not need to be recovered.

2, tempdb is always set to "simple" recovery mode, when you try to modify the error will be. This means that transaction log records for committed transactions are marked for reuse after each checkpoint.

3, tempdb can only have one filegroup, can not add more filegroups.

4. Tempdb is used to store three types of objects: User objects, internal objects, version stores

Next, focus on the topic to show the problem analysis:

1.SQL Server System Database Introduction

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

Master: Records all system-level information for the SQL Server system, including instance-scoped metadata, endpoints, linked servers, and system configuration settings, as well as the presence of other databases and the location of the data to ask for files. If master is unavailable, the database will not start.

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

MSDB: Used by the SQL Server Agent to schedule alerts and jobs.

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

2.TEMPDB Internal Operating principle

Unlike other SQL Server databases, tempdb is automatically drop,re-create in the case of SQL Server shutdown and restart. According to the model database, a new 8MB (MDF file:8mb;ldf file:1mb, Autogtouth set to 10%) size recovery model simple tempdb database is established by default.

After the tempdb database is established, the DBA can set up data objects in other databases, temporary tables, temporary stored procedures, table variables, and so on, to be added to tempdb. When the activity of tempdb is very frequent, it can grow automatically because it is simple recovery model, Will minimize logging, and the logs will be truncated continuously.

3. How to optimize tempdb to improve SQL Server performance

If SQL Server is not frequent to tempdb, tempdb has no impact on the database; Conversely, if the access is frequent, the loading will increase, and the performance of tempdb will have a significant impact on the entire DB. Optimizing the performance of tempdb is important, Especially for large databases.

Note: Before optimizing tempdb, consider the impact of tempdb on SQL Server performance, assess the problems encountered, and the feasibility.

　　3.1 Minimized use of tempdb

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

Here are a few places to use tempdb:

(1) temporary table established by the user. If you can avoid it, try to avoid it. If you use temporary tables to store large amounts of data and frequently access them, consider adding index to increase query efficiency.

(2) Schedule jobs. such as DBCC CHECKDB will take up more resources in the system and more use of tempdb. It's best to do it when SQL Server loading is lighter.

(3) Cursors. Cursors can severely affect performance should be avoided as much as possible.

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

(5) sort_int_tempdb. This option is available when you set up index.

(6) Index online rebuild.

(7) Temporary worksheets and intermediate result sets. When the join is generated.

(8) The result of the order.

(9) After and INSTEAD of triggers.

It is not possible to avoid using tempdb, and if there is a bottleneck or issue of tempdb, it is time to go back and consider these issues.

　　3.2 Redistributing the space size of tempdb

The 8MB size of tempdb is automatically created when SQL Server restarts, and the automatic growth defaults to 10%. For a small database, the 8MB size is sufficient. But for larger databases, 8MB is far from meeting the needs of SQL Server frequent activity, so it will increase by 10%, for example, 1GB, which will take a long time, which will seriously affect the SQL Server's performance. It is recommended that you set the initialization size of tempdb at SQL Server startup (the following picture is set to MDF:300MB,LDF:50MB), or you can implement it by ALTER DATABASE. This makes it more efficient to have enough space available for tempdb when SQL Server restarts.

The difficulty is finding a reasonable initialization size, which is an appropriate value when SQL Server activity is frequent and tempdb is not growing, and you can set the value at this point to initial size; There will certainly be more to consider, for example.

　　3.3 Do not shrink tempdb (if not necessary)

There are times when we notice that Tempdb takes up a lot of space, but when the available space is low, it's possible to think of a shrink database to free up disk space, so be careful and it may affect performance.

As shown in the figure above: tempdb is allocated 879.44MB of space, 45% of the space is idle, if shrink off, you can release some of the disk idle, but then SQL Server if there is a large number of operations, the tempdb space is not enough, and will be automatically increased according to the proportion of 10% . In this case, the shrink operation is invalid and will increase the loading of the system.

　　3.4 Allocation of files and other data files of tempdb to unused IO

Tempdb has a high requirement for IO, preferably on a high IO disk and on disk with other data files to improve read-write efficiency.

Tempdb is also divided into several files, typically based on the CPU, several CPUs are divided into several tempdb data files. Multiple tempdb files can improve read and write efficiency and reduce the collision of IO activities.

Tempdb is an important part of SQL Server, and this is just a summary of some of the things you know about tempdb, and you need to learn more ...