Talking about the important role of tempdb in the SqlServer system, tempdbsqlserver

Talking about the important role of tempdb in the SqlServer system, tempdbsqlserver

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.

The above is the basic knowledge.

If SQL Server does not frequently access tempdb,

Tempdb does not affect the database; on the contrary, if the access is frequent, loading will increase,

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.

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 using tempdb. If there is a tempdb bottleneck or an issue, you should return to consider these issues.

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

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 by CPU. Several CPUs are divided into several tempdb data files. Multiple tempdb files can improve read/write efficiency and reduce IO activity conflicts.

Common scripts are as follows:

-- Query the size of the tempdb Database

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.dbidWHERE a.name='tempdb'group by a.nameorder by 1

Postscript:

Tempdb is an important part of SQL Server. Today, we find that the data in tempdb is very large. So we need to learn more about tempdb.

What is the role of the tempdb database?

The Tempdb database is used by all databases of the entire system. When SQL Server is started, the tempdb database is re-established. When the user and SQL S... model database are templates provided for the user to create a database.

One interview question: What database systems does SQL server 2000 have? What are their respective roles?

Master, model, msdb, northwind, pubs, tempdb

Pubs example Database
The pubs sample database uses a book publishing company as a model to demonstrate Microsoft®SQL Server™Many options available in the database. This database and its tables are often used in the example described in the document.

Northwind Sample Database
The Northwind Traders Sample Database contains sales data from a fictitious company called Northwind Traders, which is engaged in the import and export of specialty foods from all over the world.

Master Database
The master database records all system-level information of the SQL Server system. It records all logon accounts and system configuration settings. The master database is such a database that records all other databases, including the location of database files. The master database records the initialization information of SQL Server. It always has an available backup of the latest master database.

Tempdb Database
The tempdb database stores all temporary tables and temporary stored procedures. It also meets any other temporary storage requirements, such as storing worksheets generated by SQL Server. The tempdb database is a global resource. All temporary tables and stored procedures of users connected to the system are stored in the database. The tempdb database is re-created when SQL Server is started. Therefore, the database is always clean when the system is started. Temporary tables and stored procedures are automatically removed when the connection is disconnected, and no connections are active when the system is closed, therefore, no content in the tempdb database is saved from one session of SQL Server to another.

By default, when SQL Server is running, the tempdb database will automatically grow as needed. However, unlike other databases, each time the database engine is started, it is reset to its initial size. If the size defined for the tempdb database is small, automatically increasing the size of the tempdb database to the size required to support the workload may become part of the system's processing load. To avoid this overhead, you can use alter database to increase the size of the tempdb DATABASE.

Model Database
The model database is used as a template for all databases created on the system. When the create database Statement is issued, the first part of the new DATABASE is created by copying the content in the model DATABASE, and the rest is filled with blank pages. Since SQL Server creates a tempdb database every time it starts, the model database must always exist in SQL Server.

Msdb Database
The msdb database is used by the SQL Server Agent to schedule alarms and jobs and record operators.