SQL Server 临时对象缓存

最新推荐文章于 2024-07-20 08:00:00 发布

薛定谔的DBA

最新推荐文章于 2024-07-20 08:00:00 发布

阅读量695

点赞数

文章标签： sql mssql sqlserver 数据库微软

本文链接：https://blog.csdn.net/kk185800961/article/details/126085295

版权

本文详细介绍了SQLServer中临时对象缓存的工作原理，包括表和表变量的创建、删除、缓存机制，以及如何减少tempdb的资源竞争。临时对象缓存可以显著降低元数据和存储的开销，但也有其限制和潜在问题，如统计信息的准确性。建议在适当情况下使用表变量和本地临时表，并注意缓存策略以优化性能。

摘要由CSDN通过智能技术生成

创建表是一个相对资源密集型和耗时的操作。服务器必须为新的数据和索引结构去定位和分配存储空间，并在多个系统元数据表中创建相应的条目。所有工作都必须以在高并发下完成，并且满足关系数据库的事务特性ACID。

在 SQL Server 中，这意味着以正确的顺序使用正确的锁和闩锁，同时还要确保在对数据库进行任何物理更改之前，将详细的事务日志条目安全地提交到持久存储中。这些日志条目确保在事务回滚或系统崩溃的情况下，可以将数据库恢复到一致的状态。

删除表也是类似的昂贵操作。幸运的是，大多数数据库不会非常频繁地创建或删除表。一个明显的例外就是系统数据库 tempdb。这个数据库包含整个 SQL Server 实例中所有临时表和表变量的物理存储、分配结构、系统元数据和事务日志条目。与其他数据库相比，tempdb 中的临时表和表变量的创建和删除比较频繁。当我们高并发下使用临时表和表变量时，在 tempdb 数据库就会出现争用。

临时对象缓存

为了减少对 tempdb 结构的影响，SQL Server 可以缓存临时对象以供重用。SQL Server 没有删除临时对象，而是保留系统元数据，并截断表数据。如果表为 8MB 或更小，则同步执行截断，否则将使用延迟删除。在任何一种情况下，将存储需求截断减少到单个空数据页，并将分配信息减少到单个IAM页。

缓存避免了下次创建临时对象时，减少了几乎所有的分配和元数据的开销成本。与完整模式数据库的删除和重新创建相比，tempdb 临时对象缓存减少了没必要的事务日志。

只有在以下模块中创建的本地临时表或表变量可以缓存：

存储过程（包括临时存储过程）
触发器
多语句表值函数
标量用户定义函数

多语句表值函数的返回值是一个表变量，它本身可能被缓存。当参数从客户端应用程序发送时，表值参数(也就是表变量)可被缓存，例如在 .net 代码中使用SqlDbType.Structured。当语句被参数化，表值参数结构只能在 SQL Server 2012 或更高版本中缓存。

以下内容不能缓存:

全局临时表
即时查询SQL创建的对象
使用动态SQL创建的对象(例如使用 EXECUTE 或 sys.sp_executesql )

为了使临时对象可以缓存，不可进行以下操作:

命名约束
创建对象后执行“DDL”
在使用 WITH RECOMPILE 选项定义的模块中
EXECUTE 语句时使用 WITH RECOMPILE 选项

常见的误解:

TRUNCATE TABLE 不能阻止缓存
DROP TABLE 不能阻止缓存
UPDATE STATISTICS 不会阻止缓存
自动创建统计信息不会阻止缓存
手动 CREATE STATISTICS 将阻止缓存

禁用临时表缓存的一种常见模式是在创建表语句之后创建索引。在大多数情况下，可以使用主键和唯一约束来解决这个问题。在SQL Server 2014及后续版本中，我们可以选择使用 INDEX 子句在表创建语句中直接添加非唯一的非聚集索引。

监控和维护

我们可以看到有多少临时对象当前缓存使用缓存计数器DMV:

SELECT * FROM sys.dm_os_memory_cache_counters WHERE [type] = N'CACHESTORE_TEMPTABLES'

结果如下:

只要包含模块的任何部分正在执行，缓存条目就被认为在使用中。同一模块的并发执行将导致创建多个缓存的临时对象。同一个模块的多个执行计划(可能是由于不同的会话 SET 选项)也会导致同一个模块的多个缓存条目。

缓存条目可能会随着时间的推移而老化，以响应对内存的竞争性需求。当父模块的执行计划从计划缓存中删除时，缓存的临时对象也可以被删除(由后台系统线程异步处理)。当然，临时对象缓存存储也可以手动完全清除（生产环境慎用）:

DBCC FREESYSTEMCACHE('Temporary Tables & Table Variables')  WITH MARK_IN_USE_FOR_REMOVAL;WAITFOR DELAY '00:00:05';

5 秒的延迟允许后台清理任务运行。注意，这个命令实际上是危险的。你应该只在你有独占访问权的测试实例上使用它(风险自己承担)。

缓存的实现细节

表变量是由 tempdb 数据库中的用户表实现的，名称前缀符号是"#"，后面是对象id的8位十六进制表示形式。关系如下所示:

DECLARE @tab AS table (id int NULL);SELECT    T.[name],    ObjIDFromName = CONVERT(int, CONVERT(binary(4), RIGHT(T.[name], 8), 2)),    T.[object_id],    T.[type_desc],    T.create_date,    T.modify_dateFROM tempdb.sys.tables AS T WHERE T.[name] LIKE N'#[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]';

示例结果如下所示。注意从对象名称计算出来的对象id是如何与实际的对象id匹配的:

上面的脚本每次执行时都产生不同的 tempdb对象ID和对象名称，因为即时操作的表变量没有缓存。若将相同的脚本放入一个模块(例存储过程中)将允许表变量被缓存(只要不使用动态SQL)，因此对象ID和名称在每次执行时都是相同的。

当表变量没有被缓存时，每次都会创建并删除底层表。当启用临时对象缓存时，表将在模块的末尾被截断，而不是被删除。当表变量被缓存时，系统元数据不会发生变化。当模块结束时，对分配结构和事务日志记录的影响仅限于删除表中的行和删除任何多余的数据和分配页。

临时表

当使用临时表而不是表变量时，基本机制本质上是相同的，需要两个额外的重命名步骤：当临时表没有被缓存时，可以在 tempdb 中看到它，它是用户提供的熟悉的名称，及下划线和后面跟一串对象id的十六进制表示作为最终后缀。本地临时表将一直存在，直到显式删除它，或者直到创建它的作用域结束。对于临时SQL，这意味着当会话与服务器断开连接时才删除。

对于缓存的临时表，模块第一次运行时，会像非缓存的情况一样创建临时表。在模块的末尾，临时表不会被自动删除，临时表将被截断，然后重命名为对象 ID 的十六进制表示（如表变量一样）。下次模块运行时，缓存表从十六进制格式重命名为用户提供的名称（加上下划线和十六进制对象 ID）。

模块开始和结束时的额外重命名操作涉及少量的系统元数据更改。因此，在非常高的重用率下，缓存的临时表仍然会至少遇到一些元数据争用。然而，缓存的临时表对元数据的影响要比非缓存的情况(每次创建和删除表)低得多。