Optimize tempdb to improve SQL server performance

[CCID news] If SQL server does not frequently access tempdb, tempdb will 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.

Tempdb is an important part of SQL Server. The above is just a summary of tempdb and further learning is required.

 

Http://news.ccidnet.com/art/32849/20100517/2061689_1.html