在我之前关于SQL Server 系统数据库的文章中，我们了解了作为SQL Server 安装一部分的每个系统数据库。当前文章将重点介绍围绕tempdb数据库经常遇到的问题以及如何正确解决这些问题。

SQL Server 临时数据库

正如该系统数据库的名称所示，tempdb保存由SQL Server 创建的临时对象。它们与多个操作相关，并充当连接到SQL Server 实例的所有用户的全局工作区。

当用户执行操作时，Tempdb数据库将保存以下对象类型：

临时对象是由用户明确创建的。它们可以是本地或全局临时表和索引、表变量、表值函数中使用的表和游标。

由数据库引擎创建的内部对象，如

存储假脱机、游标、排序和临时大对象(LOB)的中间结果的工作表。

执行哈希联接或哈希聚合操作时的工作文件。

如果SORT_IN_TEMPDB设置为ON，以及其他操作（如GROUP BY、ORDER BY 或SQL UNION查询），则创建或重建索引时的中间排序结果。

支持行版本控制功能的版本存储，通用版本存储或在线索引构建版本存储使用tempdb数据库文件。

每次SQL Server 服务启动时都会创建Tempdb数据库。因此，可以将tempdb数据库创建时间视为SQL Server 服务启动时间的近似值。我们可以使用下面显示的查询从sys.databases DMV 中识别它：

但是，SQL Server Service的实际启动涉及以特定顺序启动所有系统数据库。它可能发生得比tempdb创建时间早一点。我们可以通过在sys.dm_os_sys_info DMV上执行以下查询，使用sys.databases DMV获取值。

该ms_ticks列指定自从计算机或服务器启动的毫秒数。 sqlserver_start_time_ms_ticks列指定自SQL Server服务启动时ms_ticks数字以来的毫秒数。  

我们可以在SQL Server 错误日志中找到有关启动SQL Server 服务时启动的数据库顺序的更多信息。

在SSMS中，展开Management > SQL Server Error Logs > 打开当前错误日志。应用Starting up database过滤器并单击Date 以升序对其进行排序：

我们可以看到，在启动SQL Server 服务时，master数据库已经先启动了。然后是所有用户数据库和所有其他系统数据库。最后，tempdb启动。您还可以通过执行xp_readerrorlog系统过程以编程方式获取此信息：

注意：如果SQL Server 服务最近没有重新启动，并且SQL Server错误日志被回收，这可能会将较旧的错误日志推送到较旧的文件，则上述两种方法可能都不会显示必要的信息。在这种情况下，我们可能需要扫描存档的SQL Server 错误日志文件中的数据。

SQL TempDB 数据库中的常见问题

由于tempdb为所有用户会话或活动提供了一个全局工作区，如果不仔细配置，它可能成为用户操作的性能瓶颈。在我之前的文章中，我们讨论了在tempdb数据库中实施的推荐最佳实践。但是，即使在实施它们之后，我们也可能经常遇到问题：

跨tempdb数据文件的文件增长不均。

Tempdb数据文件正在增长到一个巨大的价值，需要缩小Tempdb。

TempDB数据文件中的文件增长不均

从SQL Server 2000 开始，默认建议是根据服务器中可用的逻辑核心数拥有多个数据文件。

当我们有多个数据文件时，例如下图中的4个tempdb数据文件，tempdb数据文件的自动增长将以循环方式发生64 MB，从tempdev > temp2 > temp3 > temp4 > tempdev >等开始在。

如果其中一个文件大小由于某种原因无法自动增长，则会导致某些文件与其他文件相比变得非常大。这会导致对大文件造成额外过载，并对tempdb数据库产生负面性能影响。

我们需要手动确保所有tempdb数据文件在任何时间点手动均匀大小以避免争用或性能问题，直到SQL Server 2014。从SQL Server 2016开始及之后的版本，Microsoft通过实现一些功能改变了这种行为，这些将在本文后面讨论。

为了克服上述性能问题，SQL Server 引入了2个名为1117和1118的跟踪标志以避免围绕tempdb的争用问题。

跟踪标志1117 – 启用单个文件组中所有文件的自动增长

跟踪标志1118 – 为tempdb启用UNIFORM FULL EXTENTS

跟踪标志1117

如果没有启用跟踪标志1117，每当tempdb配置有多个均匀大小的数据文件并且数据文件需要自动增长时，默认情况下SQL Server 将尝试以循环方式增加文件大小（如果所有文件）。如果数据文件的大小不均匀，则SQL Server 将尝试增加tempdb的最大数据文件的大小，并将使用这个较大的文件进行大多数用户操作，从而导致tempdb争用问题。

为了解决这个问题，SQL Server 引入了Trace Flag 1117。一旦启用，如果文件组中的一个文件需要自动增长，它将自动增长该文件组中的所有文件。它解决了tempdb争用问题。但是，问题是一旦启用了跟踪标志1117，就会为所有用户数据库配置自动增长。

跟踪标志1118

跟踪标志1118用于启用UNIFORM FULL EXTENTS。让我们退一步来了解SQL Server 如何存储基本数据。

页是SQL Server 中的基本存储单位，大小为8千字节(KB)。

Extent是一组8个物理上连续的页面，大小为64KB(8*8KB)。根据Extent中存储数据的对象或所有者的数量，Extent可以分为：

Uniform Extents是由单个对象或所有者使用或访问的8个连续页面；

Mixed Extents – 8 个连续页面被最少2个和最多8个对象或所有者使用或访问

启用跟踪标志1118将允许tempdb具有统一的范围，从而获得更好的性能。

如何启用跟踪标志1117和1118

可以通过多种方法启用跟踪标志。您可以从以下选项中定义合适的方式：

SQL Server 服务启动参数

即使在SQL服务重新启动后也永久可用。推荐的方法是通过SQL Server 服务启动参数启用Trace Flags 1117 和1118。

打开SQL Server Configuration Manager并单击SQL Server Services以列出该服务器中的可用服务：

1. 右键单击SQL Server (MSSQLSERVER) >Properties >Startup Parameters。

2. 在空白字段中键入 – T以指示Trace Flag。

3. 提供值1117和1118，如下所示。

4. 单击Add 将跟踪标志添加为启动参数。

然后单击 OK为SQL Server 的此实例永久添加跟踪标志。重新启动SQL Server Service以反映更改。

DBCC TRACEON (, -1)

全局启用跟踪标志。SQL Server 服务将在服务重新启动时丢失跟踪标志。要全局启用跟踪标志，请在新的查询窗口中执行以下脚本：

DBCC TRACEON ()

在会话级别启用跟踪标志。它仅适用于用户创建的当前会话。要在会话级别启用跟踪标志，请在新查询窗口中执行以下脚本：

要查看在SQL Server 实例中启用的跟踪标志列表，我们可以使用DBCC TRACESTATUS命令：

正如我们所见，跟踪标志1117和1118在我的实例中与Session一起全局启用。

要关闭跟踪标志，我们可以使用DBCC TRACEOFF 命令，例如：

SQL Server 2016 TempDB 增强功能

在从SQL Server 2000 到SQL Server 2014 的SQL Server 版本中，我们必须启用跟踪标志1117和1118以及对tempdb的完整监控以避免tempdb争用问题。从SQL Server 2016 及更高版本开始，默认实现跟踪标志1117和1118。

但是，根据我的个人经验，最好将tempdb预先增长到一个巨大的大小，以避免多次自动增长的需要，并消除不均匀的文件大小或SQL Server 广泛使用的单个文件。

我们可以验证Trace Flag 1117 和1118在SQL Server 2016 中是如何实现的：

设置文件组内所有文件的自动增长的跟踪标志1117现在是文件组的属性。我们可以在创建新文件组或修改现有文件组时对其进行配置。

要验证Filegroup的自动增长属性，请从sys.filegroups DMV执行以下脚本：

要修改AdventureWorks数据库的主文件组的自动增长属性，我们使用AUTOGROW_ALL_FILES执行以下脚本以平均自动增长所有文件或使用AUTOGROW_SINGLE_FILE仅允许自动增长单个数据文件。

默认情况下，为tempdb和从SQL Server 2016 开始的所有用户数据库启用设置数据文件的统一范围属性的跟踪标志1118。我们无法更改tempdb的属性，因为它现在仅支持Uniform Extent 选项。

对于用户数据库，我们可以修改这个参数。默认情况下，系统数据库master、model和msdb支持混合范围，并且也不能更改。

要修改用户数据库的混合页面分配属性值，请使用以下脚本：

为了验证混合页分配属性，我们可以从sys.databases DMV查询is_mixed_page_allocation_on列，值为0，表示统一范围页分配，1表示混合范围页分配。

TempDB数据文件变大，需要压缩TempDB

在SQL Server 2014或更早的版本中，如果没有将跟踪标志1117和1118与tempdb数据库创建的多个数据文件正确配置，其中一些文件将不可避免地变大。 如果发生这种情况，DBA通常会尝试缩小tempdb数据文件。 但这是一个处理这种情况不恰当的方法。

还有其他选项可用于缩小tempdb。

让我们考虑可用于Shrink tempdb 的DBCC命令以及执行这些操作的影响。

DBCC收缩数据库

该DBCC SHRINKDATABASE控制台命令是通过缩小数据\日志文件的末尾。

要成功收缩数据库，该命令需要文件末尾的可用空间。如果文件末尾有任何活动事务，则无法缩小数据库文件。

执行DBCC SHRINKDATABASE的影响是它会尝试清除每个数据文件或日志文件末尾的可用空间，这些空间可能已为表数据的未来增长保留。因此，运行此命令可能会导致文件大小不均匀，从而导致tempdb争用问题。

缩小用户数据库（例如Adventureworks数据库）的语法是

DBCC收缩文件

该DBCC SHRINKFILE控制台命令的工作原理类似DBCC SHRINKDATABASE，但它缩小了指定的数据库数据或日志文件。

如果您发现某个特定的tempdb数据文件很大，我们可以尝试使用DBCC SHRINKFILE 缩小该特定项目，如下所示。

在tempdb上使用此命令时要小心，因为如果文件收缩到低于或高于其他数据文件的值，则该特定数据文件将无法有效使用。或者，它会被更频繁地使用，从而导致tempdb争用问题。

在AdventureWorks数据文件上执行DBCC SHRINKFILE 操作到1GB (1024 MB) 的语法为：

DBCC DROPCLEANBUFFERS

DBCC DROPCLEANBUFFERS控制台命令用于清除Buffer池中的所有干净缓冲区，以及columnstore对象池中的columnstore对象。

只需执行以下命令：

DBCC FREEPROCCACHE

该DBCC FREEPROCCACHE命令清除所有的存储过程的执行计划缓存。

SQL Server 使用过程执行计划缓存来更快地执行相同的过程调用。执行DBCC FREEPROCCACHE 后，Plan Cache 被清除。因此，当在实例中执行存储过程时，SQL Server 必须再次创建该缓存。在生产数据库实例中执行时会留下严重的负面影响。

不建议在生产数据库实例上执行DBCC FREEPROCCACHE！

执行DBCC FREEPROCCACHE 的语法如下：

DBCCFREESESSIONCACHE

该DBCC FREESESSIONCACHE命令将清除SQL Server实例的分布查询连接缓存。当在特定SQL Server 实例上执行许多分布式查询时，这将很有帮助。

执行DBCC FREESESSIONCACHE 的语法是：

DBCC FREESYSTEMCACHE

该DBCC FREESYSTEMCACHE命令清除所有缓存中的所有未使用的缓存条目。默认情况下，SQL Server 这样做是为了让更多内存可用于新操作。但是，我们可以使用以下命令手动执行它：

.众所周知，tempdb存储所有临时用户对象或内部对象，包括执行计划缓存、缓冲池数据、会话缓存和系统缓存。因此，执行上述6条DBCC命令将有助于清除阻止正常收缩过程的tempdb数据文件。

尽管我们已经通过各种方法完成了如何缩小tempdb的步骤，但下面列出了处理tempdb数据库的推荐最佳实践：

A.如果可能，重新启动SQL Server 服务以均匀地重新创建tempdb数据文件。潜在的影响是，我们将丢失上面讨论的所有执行计划和其他缓存信息。

B.将tempdb数据文件预增长到保存tempdb数据文件的驱动器中可用的巨大文件大小。这将防止SQL Server 在SQL Server 2014 及更早版本中不均匀地增加文件大小。

3. 如果由于 RTO 或 RPO 导致 SQL Server 服务无法重新启动，请在清楚了解影响后尝试上述 DBCC 命令。

D.收缩tempdb数据库或数据文件不是推荐的方法，因此永远不要在您的生产环境中这样做，除非没有其他选择。

结论

我们已经了解了有关tempdb工作原理的更多信息，以便我们可以配置tempdb以获得更好的性能，从而避免tempdb上的争用问题。我们还讨论了tempdb中经常遇到的问题、SQL Server 中跨各种版本的可用措施以及如何有效地处理它。除此之外，我们还研究了为什么在处理tempdb数据库时不推荐使用收缩tempdb数据库或数据文件的方法。

Raja Jegan

Raja Jegan Ramesh 是一位经验丰富的数据库架构师，在各种RDBMS技术方面拥有超过15年的经验。他主要专注于SQL Server 和相关领域，在日常工作中处理其他RDBMS平台，如Oracle、MySQL等。在业余时间，他在Experts-Exchange平台上做出贡献，帮助几个人解决日常问题，这帮助他遇到了许多与SQL Server 相关的场景，以及如何通过与其他SQL Server 专家合作来有效地处理这些场景。

公司名称：北京哲想软件有限公司

北京哲想软件官方网站：cogitosoft.com

北京哲想软件微信公众平台账号：cogitosoftware

北京哲想软件微博：哲想软件

北京哲想软件邮箱：sales@ cogitosoft.com

销售（俞先生）联系方式：+86（010）68421378

微信：18610247936     QQ：368531638