sql tempdb清理_SQL Server 2019中的内存优化的TempDB元数据

sql tempdb清理

介绍 (Introduction)

In-memory technologies are one of the greatest ways to improve performance and combat contention in computing today. By removing disk-based storage and the challenge of copying data in and out of memory, query speeds in SQL Server can be improved by orders of magnitude.

内存技术是当今提高性能和解决计算争用的最大方法之一。 通过消除基于磁盘的存储以及将数据复制到内存中和从内存中复制出来的挑战，SQL Server中的查询速度可以提高几个数量级。

TempDB is one of the biggest sources of latency in SQL Server. Servicing requests for temporary data structures and maintaining metadata about those structures comprises one of the most significant bottlenecks in SQL Server.

TempDB是SQL Server中最大的延迟源之一。 服务临时数据结构的请求并维护有关这些结构的元数据是SQL Server中最重要的瓶颈之一。

Memory-Optimized TempDB metadata addresses both of these concerns by allowing TempDB metadata to be moved into memory and greatly improve the performance of any workload that makes the hefty use of temporary data structures.

内存优化的TempDB元数据通过允许将TempDB元数据移到内存中来解决这两个问题，并极大地提高了大量使用临时数据结构的工作负载的性能。

背景 ( Background )

TempDB is a shared resource used by SQL Server for a variety of different purposes, some of which include:

TempDB是SQL Server用于各种不同目的的共享资源，其中一些包括：

• Storage of temporary tables and table variables
  存储临时表和表变量
• Storage of work files needed for hash join/aggregate operations
  存储哈希联接/聚合操作所需的工作文件
• Data returned by table-valued functions
  表值函数返回的数据
• Data cached for use by cursors
  缓存供游标使用的数据
• Work tables for spools and sort operations
  线轴和排序操作的工作表
• Some index rebuild operations, especially if SORT_IN_TEMPDB is turned on
  一些索引重建操作，尤其是如果打开SORT_IN_TEMPDB
• Row versions used by: snapshot isolation, online index rebuilds, AFTER triggers, and multiple result sets
  所使用的行版本：快照隔离，在线索引重建，AFTER触发器和多个结果集

In other words, TempDB is used all over the place, though its function is generally hidden from developers and technicians. It would not be clear that many of these things are stored in TempDB unless you either already had this insight into how SQL Server worked or a desire to test/learn to find out.

换句话说，尽管开发人员和技术人员通常无法使用它的功能，但TempDB却在各处使用。 除非您已经对SQL Server的工作原理有深刻的了解，或者渴望测试/学习以找出答案，否则不清楚其中的许多内容是否存储在TempDB中。

The key to TempDB operations is the fact that objects are only stored until dropped, a session disconnects, or the service restarts. When SQL Server starts up, TempDB will be empty. While TempDB operations are minimally logged, it is not imperative to persist objects permanently as its use will always be transient. As a result, it is the perfect candidate for in-memory technologies as memory is also a transient repository for data.

TempDB操作的关键是仅在删除对象，断开会话连接或重新启动服务之前存储对象。 当SQL Server启动时，TempDB将为空。 尽管TempDB操作的日志记录最少，但是永久保留对象不是必须的，因为它的使用始终是短暂的。 因此，它是内存技术的理想选择，因为内存也是数据的临时存储库。

TempDB can roughly be summarized into 2 types of data:

TempDB可以大致概括为两种数据类型：

• Metadata describing the data structures stored in TempDB
  描述存储在TempDB中的数据结构的元数据
• The data contained within these data structures
  这些数据结构中包含的数据

This article discusses how SQL Server 2019 will allow us to store metadata about objects in memory, rather than on disk. This metadata includes system tables, such as sys.tables, sys.columns, or sys.procedures. The actual data stored within temporary objects is still stored in TempDB using traditional non-memory based storage methods.

本文讨论了SQL Server 2019如何允许我们将有关对象的元数据存储在内存中而不是磁盘上。 此元数据包括系统表，例如sys.tables ， sys.columns或sys.procedures 。 临时对象中存储的实际数据仍使用传统的基于非内存的存储方法存储在TempDB中。

为什么如此重要 (Why This Matters)

Metadata in TempDB is shared across all databases and processes on a given SQL Server. If a database manipulates temporary objects frequently, or if a server has a high volume of databases that make use of temporary objects, then contention on metadata tables may become problematic.

TempDB中的元数据在给定SQL Server上的所有数据库和进程之间共享。 如果数据库频繁地操作临时对象，或者服务器具有大量使用临时对象的数据库，则元数据表上的争用可能会成为问题。

The symptom of this problem would be latency typically associated with page latch waits as TempDB pages are removed from cache, or when DDL operations are executed against existing temporary objects. The more temporary objects exist and the more they are manipulated, the greater the contention that will be seen across a given server’s workload.

此问题的症状是，通常在从缓存中删除TempDB页面时，或者针对现有的临时对象执行DDL操作时，通常与页面闩锁等待相关的等待时间。 存在的临时对象越多，对其进行的操纵就越多，则在给定服务器的工作负载中看到的争用就越大。

There are many optimizations available that improve performance on TempDB. I’ve included some Microsoft references at the end of this article that provide quite a few optimizations and best practices for managing TempDB data and log files, as well as for making the most efficient use of TempDB when developing in TSQL.

有许多优化措施可以提高TempDB的性能。 我在本文末尾提供了一些Microsoft参考，它们提供了许多优化和最佳实践，用于管理TempDB数据和日志文件，以及在TSQL中进行开发时最有效地使用TempDB。

Let’s take a quick look at the performance of some simple SELECT operations against TempDB tables:

让我们快速看一下针对TempDB表的一些简单SELECT操作的性能：

CREATE TABLE #yummy_food
  (id INT NOT NULL,
   food_name VARCHAR(10));
INSERT INTO #yummy_food
  (id, food_name)
VALUES
  (1, 'taco'),
  (2, 'pizza'),
  (3, 'curry'),
  (4, 'sushi'),
  (5, 'chili');
 
SELECT
  *
FROM tempdb.sys.tables;

When this TSQL is executed against my relatively sleepy local server, the following are the execution plans and IO metrics returned:

在相对困倦的本地服务器上执行此TSQL时，以下是返回的执行计划和IO指标：

Whew! That is a ton of complexity for what I thought was a simple query!! The execution plan above comprises about 10% of the plan for the SELECT! The IO stats are similarly wild. The takeaway from this simple demonstration is that metadata storage isn’t cheap and that if simply selecting data is this involved on my quiet SQL Server, then similar queries against a busy server are likely to be even more expensive.

ew！ 我认为这是一个简单的查询，这相当繁琐！！ 上面的执行计划约占SELECT计划的10％！ IO统计数据也同样如此。 从这个简单的演示中可以得出的结论是，元数据存储并不便宜，而且如果在我安静SQL Server上仅涉及选择数据，那么对繁忙的服务器进行类似的查询可能会变得更加昂贵。

Moving TempDB metadata into memory sidesteps much of this pain by removing the hefty IO expense. As is the case for other memory-optimized use-cases, the contention is essentially eliminated when objects are shifted from disk to memory. Faster data access with no latching is a huge win for us, and one that comes at a minimal cost.

通过消除大量的IO开销，将TempDB元数据移到内存中可以避免很多麻烦。 与其他内存优化用例一样，当对象从磁盘转移到内存时，争用基本上被消除。 没有锁存的更快数据访问对我们来说是一个巨大的胜利，而这是以最小的代价实现的。

这个怎么运作 ( How it Works )

Turning on memory-optimized TempDB metadata storage can be accomplished with the following TSQL:

可以使用以下TSQL来打开内存优化的TempDB元数据存储：

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Note that this requires a service restart of the SQL Server service. For a production system, you’ll want to schedule this at a time when a service restart is acceptable.

请注意，这需要重新启动SQL Server服务。 对于生产系统，您需要在可接受的服务重启时间安排该时间。

We can verify this setting at any time with the following SERVERPROPERTY check:

我们可以随时通过以下SERVERPROPERTY检查来验证此设置：

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

The result is straightforward:

结果很简单：

Well, at least some questions in life can be answered in simple terms! With this setting enabled, let’s rerun our query from earlier:

好吧，至少生活中的一些问题可以简单地回答！ 启用此设置后，让我们从前面重新运行查询：

CREATE TABLE #yummy_food
  (id INT NOT NULL,
   food_name VARCHAR(10));
INSERT INTO #yummy_food
  (id, food_name)
VALUES
  (1, 'taco'),
  (2, 'pizza'),
  (3, 'curry'),
  (4, 'sushi'),
  (5, 'chili');
 
SELECT
  *
FROM tempdb.sys.tables;

Now, when we inspect the execution plan, we’ll find it is as complex as before:

现在，当我们检查执行计划时，我们会发现它和以前一样复杂：

If we inspect the properties, though, the storage type for most components of this query can be verified as Memory Optimized. The IO stats can also confirm this change:

但是，如果我们检查属性，则可以将该查询的大多数组件的存储类型验证为“内存优化”。 IO统计信息也可以确认此更改：

We have gone from 62 logical reads to 6. While some reads are still needed to service our query, all of the TempDB-exclusive metadata tables have been eliminated from this list. This is a big improvement, and all for a single, tiny temporary table created for use by this demo. We’ve also eliminated latching against this data, which will greatly reduce contention as we no longer need to move data back and forth to the cache when using it. Since this data is non-durable, it will vanish upon restart…though this is what it does anyway, so there’s no loss to us in that regard.

我们已经从62个逻辑读取增加到6个。虽然仍需要一些读取来服务于我们的查询，但所有TempDB专有的元数据表都已从该列表中删除。 这是一个很大的改进，所有操作都针对此演示创建的单个小型临时表。 我们还消除了针对此数据的闩锁，这将大大减少争用，因为在使用数据时不再需要将数据来回移动到缓存中。 由于此数据是非持久性的，因此它将在重新启动后消失……尽管无论如何它都是这样做的，因此在这方面对我们没有任何损失。

The benefits of this change are significant and can have a big performance impact on a system that makes heavy use of TempDB!

这项更改的好处是巨大的，并且会对大量使用TempDB的系统产生巨大的性能影响！

最佳实践 (Best Practices)

This feature is not free, and it is important to understand how to best utilize it prior to turning it on. First off, it consumes memory. Ensure that your SQL Server has enough memory allocated to it so that it can store TempDB metadata. Make sure the minimum memory setting is increased or already high enough to provide an acceptable baseline to support TempDB operations. If your server has the defaults set for its memory configuration, then it is strongly advised that you adjust the minimum and maximum memory to values that better represent your server’s workload.

此功能不是免费的，并且在打开它之前了解如何最佳利用它很重要。 首先，它消耗内存。 确保为SQL Server分配了足够的内存，以便它可以存储TempDB元数据。 确保最小内存设置已增加或已经足够高，以提供可接受的基准来支持TempDB操作。 如果您的服务器为其内存配置设置了默认设置，则强烈建议您将最小和最大内存调整为更能代表服务器工作负载的值。

My local server does not consume much memory, so my settings reflect a low minimum server memory and a generous maximum memory configuration:

我的本地服务器不占用太多内存，因此我的设置反映出较低的最小服务器内存和足够大的最大内存配置：

At the end of this article, I’ve included a reference link to some additional resources on how to set your minimum and maximum memory settings, which goes well beyond the scope of this discussion. Suffice it to say that this is an important decision to be made when a server is built and to be reviewed as applications grow and consume more memory. Whenever we opt to utilize in-memory features, a similar review should be conducted to determine how much additional memory will be required to service this new memory utilization.

在本文的结尾，我提供了指向一些其他资源的参考链接，这些资源提供了有关如何设置最小和最大内存设置的信息，这超出了本讨论的范围。 可以说，这是构建服务器时要做出的重要决定，并随着应用程序的增长和消耗更多内存而进行审查。 每当我们选择使用内存功能时，都应该进行类似的审查，以确定需要多少额外的内存来满足这种新的内存使用要求。

While a rare issue, be advised that you cannot query memory-optimized across multiple databases, which will not include TempDB metadata:

尽管这是一个罕见的问题，但建议您不能跨多个数据库查询内存优化的数据库，这些数据库将不包含TempDB元数据：

BEGIN TRAN
  SELECT
    *
  FROM tempdb.sys.tables;
  INSERT INTO AdventureWorks2017.dbo.shopping_list
  VALUES ('antacid', GETUTCDATE(), NULL);
COMMIT TRAN

This seemingly innocuous TSQL will result in a new error:

看似无害的TSQL将导致新的错误：

It’s unlikely your code has anything like this, and if it does it is likely on the maintenance/monitoring side of the house. Be sure to consult with your administration and operations folks prior to making this server configuration change, on the off-chance they have a code like this that you do not know about.

您的代码不太可能具有这样的内容，如果确实如此，则很可能是在房屋的维护/监视方面。 进行此服务器配置更改之前，请务必咨询您的管理人员和操作人员，否则，他们偶尔会有您不知道的类似代码。

其他TempDB优化 (Additional TempDB Optimization)

Due to its transient nature, TempDB has far more flexibility for optimization than other parts of SQL Server. For servers where TempDB is used heavily or is found to be a bottleneck, a great way to improve performance is to move it onto faster storage.

由于其临时性，TempDB具有比SQL Server其他部分更大的优化灵活性。 对于大量使用TempDB或发现瓶颈的服务器，提高性能的一种好方法是将其移到更快的存储上。

Since TempDB’s size is typically nowhere near as large as the databases it supports, moving it onto exceptionally fast flash-based storage is not an overly expensive decision. TempDB should always be separated from database and log files and this change allows it to not only be separate, but take advantage of its isolation to provide it with the biggest physical speed-boost as possible.

由于TempDB的大小通常远不及其支持的数据库大，因此将其移动到基于闪存的异常快速的存储中并不是一个过于昂贵的决定。 TempDB应该始终与数据库和日志文件分开，并且此更改不仅使其可以分开，而且可以利用其隔离为它提供最大的物理速度提升。

Another important consideration when initially configuring TempDB is how many files to allocate for data and log files, as well as autogrowth settings. Microsoft provides quite a bit of guidance on this topic and it definitely warrants further research before setting up a new server. The key is to minimize TempDB autogrowth events as these are expensive and will slow down query processing while they occur. In addition, sizing files equally ensures maximum parallelism as SQL Server will attempt to allocate data to files with the freest space. If files are equally sized, then that allocation will be relatively even across all files.

最初配置TempDB时，另一个重要的考虑因素是要为数据和日志文件分配多少文件以及自动增长设置。 Microsoft在此主题上提供了很多指导，因此在安装新服务器之前，绝对值得进一步研究。 关键是要最小化TempDB自动增长事件，因为这些事件很昂贵，并且会在发生时减慢查询处理的速度。 此外，文件大小调整同样可确保最大的并行度，因为SQL Server会尝试将数据分配给具有最空闲空间的文件。 如果文件大小相等，则所有文件之间的分配将相对均匀。

Lastly, whatever storage that TempDB lives on can be optimized for speed over durability. There is no need to back up TempDB, therefore any configuration or architecture decisions involving backups are not necessary. High-availability is important, though, as SQL Server cannot function without TempDB, so a system should be resilient towards the failure of a TempDB drive, regardless of whether this resilience is afforded by software or hardware conventions.

最后，可以优化TempDB赖以生存的任何存储，以提高其持久性。 无需备份TempDB，因此无需任何涉及备份的配置或体系结构决策。 但是，高可用性很重要，因为如果没有TempDB，SQL Server便无法运行，因此，系统应能够应对TempDB驱动器的故障，而不管这种弹性是由软件还是由硬件约定提供的。

未来 ( The Future )

As memory continues to become cheaper and easier to add to SQL Servers en masse, the demand for in-memory services will increase. It is very likely that we will see features in the near future that will allow TempDB data to reside in memory as well. This will force us to further rethink how much memory a SQL Server needs and add all of the TempDB use-cases to that formula.

随着内存变得越来越便宜，并且更容易将其批量添加到SQL Server中，对内存中服务的需求将会增加。 我们很可能会在不久的将来看到一些功能，这些功能也将允许TempDB数据也驻留在内存中。 这将迫使我们进一步重新考虑SQL Server需要多少内存，并将所有TempDB用例添加到该公式中。

This is good news, though, as logically, memory is the smart place for TempDB to reside. The less we have to manage, the better, and the fewer data churn that SQL Server needs to perform while caching data to TempDB, the faster our workloads can execute. The more we rely on TempDB, the more memory we’ll need, but at the same time the greater the performance gains can be made by moving it into memory-optimized structures.

这是个好消息，尽管从逻辑上讲，内存是TempDB驻留的明智之地。 在将数据缓存到TempDB时，我们需要管理的次数越少，SQL Server需要执行的数据流失越好，越少，工作负载执行得越快。 我们越依赖TempDB，我们将需要更多的内存，但是与此同时，通过将其移入内存优化的结构中，可以提高性能。

Expect in-memory technologies to continue to appear in other areas of SQL Server and existing features to become more robust and inclusive. These will be some of the best reasons to modernize to a new version of SQL Server as they have the potential to boost performance significantly, with the cost being more memory, which is no longer the cost-prohibitive upgrade that it used to be.

预计内存技术将继续出现在SQL Server的其他领域，而现有功能将变得更加健壮和包容。 这些是升级到新版本SQL Server的最佳理由，因为它们有潜力显着提高性能，而代价是增加内存，这不再是过去禁止升级的代价。

结论 (Conclusion)

TempDB is a critical component of SQL Server that supports many different features and can easily become a bottleneck for applications that make heavy use of it.

TempDB是SQL Server的关键组件，它支持许多不同的功能，并且很容易成为大量使用它的应用程序的瓶颈。

The ability to move TempDB metadata into memory provides an excellent opportunity to boost performance on servers with high TempDB loads. The risk is minimal as this is transient data that will vanish upon restart, regardless of how it is stored.

将TempDB元数据移动到内存中的能力为提高TempDB高负载服务器上的性能提供了绝佳的机会。 风险是最小的，因为这是瞬态数据，无论如何存储，它们都会在重启后消失。

When reviewing query and server performance, don’t forget to include TempDB in that analysis. As a shared resource, it is often overlooked as a black-box system database, but taking the time to compare a server’s settings vs. best practices can provide some easy and inexpensive ways to improve the speed and performance of your apps. Consider new features and TempDB optimizations as they are released, as they will also increase the options available to you for making your workloads faster and less contentious!

查看查询和服务器性能时，请不要忘记在该分析中包括TempDB。 作为共享资源，它通常被视为黑盒系统数据库，但是花时间比较服务器的设置与最佳实践可以提供一些简单而廉价的方法来提高应用程序的速度和性能。 考虑新功能和TempDB优化，因为它们将被发布，因为它们还将增加可供您使用的选项，从而使您的工作负载更快，减少争用！

翻译自: https://www.sqlshack.com/memory-optimized-tempdb-metadata-in-sql-server-2019/

sql tempdb清理