Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the role of tempdb in SqlServer is, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Brief introduction:

Tempdb is the system database of SQLServer, which has always been an important part of SQLServer and is used to store temporary objects. It is easy to understand that tempdb is the sketchboard of SQLServer. Both applications and databases can use tempdb as a temporary data store. All users of an instance share a Tempdb. Obviously, this kind of 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 interact with each other and affect the application.

Properties:

1. Any data in tempdb will not persist after the system is restarted. Because the tempdb is actually recreated every time SQLServer starts. This feature means that tempdb does not need to be restored.

2. The recovery model in which tempdb is always set to "simple" will report an error when you try to modify it. That is, transaction log records for committed transactions are marked for reuse after each checkpoint.

3. Tempdb can only have one filegroup and cannot add more filegroups.

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

The above is the basic knowledge.

If SQL Server accesses tempdb infrequently

Tempdb has no effect on the database; on the contrary, if the access is frequent, the loading will be aggravated.

The performance of tempdb will have an important impact on the whole DB.

. It is important to optimize the performance of tempdb, especially for large databases.

Note: before optimizing tempdb, consider how much impact tempdb has on SQL Server performance, and evaluate the problems encountered and feasibility.

1. Minimize the use of tempdb

Much of the activity in SQL Server takes place in tempdb, so in some cases overuse of tempdb can be reduced to improve the overall performance of SQL Server.

There are several uses for tempdb as follows:

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

(2) Schedule jobs. For example, DBCC CHECKDB will occupy more resources of the system and use tempdb more. It's best to do it when the SQL Server loading is light.

(3) Cursors. Cursors can 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 establishing an index.

(6) Index online rebuild.

(7) temporary worksheet and intermediate result set. Such as those produced by JOIN.

(8) the result of sorting.

(9) AFTER and INSTEAD OF triggers.

It's impossible to avoid using tempdb, and if you have tempdb bottlenecks or issue, it's time to come back and consider these issues.

2. Reallocate the space size of tempdb

A 8MB-sized tempdb is automatically established when SQL Server is restarted, and the automatic growth defaults to 10%. 8MB size is sufficient for small databases. However, for larger databases, 8MB is far from meeting the needs of frequent activities of SQL Server, so it will increase by 10%. For example, if you need 1GB, it will take a long time, which will seriously affect the performance of SQL Server. It is recommended to set the initialization size of tempdb when SQL Server starts (the following image is set to MDF:300MB,LDF:50MB), or through ALTER DATABASE. In this way, when SQL Server is restarted, tempdb will have enough space to make use of to improve efficiency.

The difficulty is to find a reasonable initialization size, which will be an appropriate value when SQL Server activity is frequent and tempdb is not growing. You can set the value to Initial Size;. Of course, there will be more considerations. This is an example.

3. Do not shrink tempdb (if not necessary)

Sometimes we notice that tempdb takes up a lot of space, but when the available space is low, we think of the shrink database to free disk space, so be careful at this time, which may affect performance.

4. Assign tempdb files and other data files to unused IO

Tempdb has higher requirements for IO, so it is best to allocate to disks with high IO and other data files to unused disks to improve the efficiency of reading and writing.

Tempdb is also divided into multiple files, generally according to CPU, several CPU is divided into several tempdb data files. Multiple tempdb files can improve read and write efficiency and reduce conflicts in IO activity.

Common scripts are as follows:

-check the size of the tempdb library

Select a.name, sum (size) * 8.0 from master.dbo.sysdatabases an inner join master.dbo.sysaltfiles 1024 as [GB], sum (size) * 8.0 as [MB] from master.dbo.sysdatabases an inner join master.dbo.sysaltfiles b on a.dbid=b.dbidWHERE a.name='tempdb'group by a.nameorder by 1 about the role of tempdb in SqlServer is shared here. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.