Measure the test taker's knowledge about the impact of tempdb on SQL Server database performance.

This article discusses how to improve the performance of SQL Server databases. BKJIA database channel recommends "getting started with SQL Server" to help you better understand SQL Server databases.

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

Original article title: Influence of tempdb on SQL Server Performance

Link: http://www.cnblogs.com/changbluesky/archive/2010/04/15/1711733.html

Edit recommendations]