sql tempdb清理_SQL Server TempDB数据库和闩锁争用

sql tempdb清理

In this article, we will learn latch contention issues that we might experience in the SQL Server TempDB database. We will also discuss reasons and the solution method of these latch contention issues. Especially, we will mention the Memory-Optimized TempDB Metadata feature that was introduced with the SQL Server 2019.

在本文中，我们将学习SQL Server TempDB数据库中可能遇到的闩锁争用问题。 我们还将讨论这些闩锁争用问题的原因和解决方法。 特别是，我们将提到SQL Server 2019引入的内存优化的TempDB元数据功能。

Firstly, we will briefly learn the essential characteristics of the TempDB database, and we will also talk about the latch concept of the SQL Server so that we can understand all aspects of the latch contention problems of the TempDB database more clearly.

首先，我们将简要学习TempDB数据库的基本特征，还将讨论SQL Server的闩锁概念，以便我们可以更清楚地了解TempDB数据库的闩锁争用问题的各个方面。

SQL Server中使用的TempDB数据库是什么？ (What is the TempDB database used for in SQL Server?)

TempDB database is one of the system databases of the SQL Server, but it has various unique functionalities as distinct from other system databases. Global and local temporary tables are created in this SQL Server TempDB database, and the data of these tables are stored by this database. At the same time, table variables, temporary stored procedures, and cursors are used in this database resource. In addition, TempdDB resources are also used by the following features.

TempDB数据库是SQL Server的系统数据库之一，但是它具有与其他系统数据库不同的各种独特功能。 全局和本地临时表在此SQL Server TempDB数据库中创建，这些表的数据由该数据库存储。 同时，此数据库资源中使用表变量，临时存储过程和游标。 此外，以下功能还使用TempdDB资源。

• Snapshot Isolation and Read-Committed Snapshot Isolation
  快照隔离和读取提交的快照隔离
• Online index operations
  在线索引操作
• MARS – (Multiple Active Result Sets)
  MARS –（多个活动结果集）

When we restart the SQL engine TempdDB database is dropped and re-created. We can not take back up this database and can not change the recovery model from simple to others. When we are taking into account all of these, we can say that the TempDB database settings directly affect query performances.

当我们重新启动SQL引擎时，将删除TempdDB数据库并重新创建它。 我们无法备份此数据库，也无法将恢复模式从简单更改为其他。 当我们考虑所有这些因素时，可以说TempDB数据库设置直接影响查询性能。

SQL Server中的闩锁是什么？ (What is the latch in SQL Server?)

A SQL buffer pool is the memory place that is reserved by the operating system for the SQL Server, and it is also called as SQL buffer cache. SQL Server transfers the data pages into the memory from the disk in order to read or manipulate them and sends them back to disk according to a special logic. The main purpose of this mechanism is the desire to deliver faster performance to clients because memory is always faster than the storage systems. In this context, we need a mechanism to guarantee the data pages consistency in the buffer pool. A latch is a synchronization object used to protect data structures held in memory against inconsistency and corruption so that SQL Server ensures the consistency of data pages in the memory. This synchronization operation is managed by the SQL Server internally.

SQL缓冲池是操作系统为SQL Server保留的内存位置，也称为SQL缓冲区高速缓存。 SQL Server将数据页从磁盘传输到内存中，以便读取或操作它们，然后根据特殊逻辑将它们发送回磁盘。 这种机制的主要目的是希望为客户端提供更快的性能，因为内存始终比存储系统快。 在这种情况下，我们需要一种机制来保证缓冲池中数据页的一致性。 闩锁是一个同步对象，用于保护内存中保存的数据结构免于不一致和损坏，以便SQL Server确保内存中数据页的一致性。 此同步操作由SQL Server在内部进行管理。

TempDB数据库元数据争用 (TempDB database Metadata Contention)

TempDB metadata contention occurs when many sessions try to access the SQL Server TempDB’s system tables at the same time during the creation of the temp tables. This heavy workload causes latency on these system tables due to this reason, and the query performance will be decreased.

当在创建临时表的过程中许多会话尝试同时访问SQL Server TempDB的系统表时，就会发生TempDB元数据争用。 由于这种原因，繁重的工作负载导致这些系统表上的延迟，并且查询性能将降低。

Now, we will create a fake workload on the TempDB to simulate this problem. We will use an oldie but goodie tool named