On the important role of tempdb in SQL Server system _mssql

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

Above is the basic knowledge.

If SQL Server does not have frequent access to tempdb,

Tempdb does not have an impact on the database; Conversely, if the access is frequent, the loading will increase,

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.

1, minimize the 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.

2. Reassign 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. 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.

4. Assign tempdb files and other data files to the 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.

The common scripts are as follows:

--Check the size of the tempdb library

Select A.name, sum (size) *8.0/1024/1024 as [GB]

, sum (size) *8.0/1024 as [MB] from

master.dbo.sysdatabases a inner Join Master.dbo.sysaltfiles B on a.dbid=b.dbid

WHERE a.name= ' tempdb '

Group by A.name ORDER by

1

Postscript:

Tempdb is an important part of SQL Server, and today it is found that the data is especially large, so some understanding and summary of tempdb will require further study.