2024年2月20日发(作者：)
解决 tempdb 中磁盘空间不足的问题
tempdb 空间要求
tempdb 系统数据库是可供连接到 SQL Server 实例的所有用户使用的全局资源。tempdb 数据库用于存储下列对象：用户对象、内部对象和版本存储区。
您可以使用 _db_file_space_usage 动态管理视图监视 tempdb 文件中的用户对象、内部对象和版本存储区使用的磁盘空间。此外，若要在会话级或任务级监视 tempdb 中的页分配或页释放活动，可以使用动态管理视图 _db_session_space_usage 和 _db_task_space_usage。这些视图可用于标识使用大量 tempdb 磁盘空间的大型查询、临时表或表变量。
诊断 tempdb 磁盘空间问题
下表列出了指示 tempdb 数据库中磁盘空间不足的错误消息。可以在 SQL Server 错误日志中找到这些错误，也可以将它们返回到任何正在运行的应用程序。
错误
1101 或 1105
引发错误的情况
任何会话都必须分配 tempdb 中的空间。
3959 版本存储区已满。此错误在日志中通常出现在错误 1105 或 1101 之后。
3967 由于 tempdb 已满，版本存储区被强制收缩。
3958 或 3966 事务在 tempdb 中找不到所需的版本记录。
数据库设置为自动增长且数据库大小快速增长时，也会指示出现 tempdb 磁盘空间问题。
监视 tempdb 磁盘空间
下列示例说明了如何确定 tempdb 中的可用空间量，以及如何确定版本存储区、内部对象和用户对象使用的空间量。
确定 tempdb 中的可用空间量
下面的查询将返回 tempdb 中所有文件的总可用页数和总可用空间量 (MB)。
复制
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM _db_file_space_usage;
确定版本存储区使用的空间量
下面的查询将返回 tempdb 中版本存储区使用的总页数和总空间量 (MB)。
复制
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM _db_file_space_usage;
确定运行时间最长的事务
如果版本存储区使用了 tempdb 中的大量空间，则必须确定运行时间最长的事务。使用下面的查询可按顺序（事务的最长运行时间）列出活动事务。
SELECT transaction_id
FROM _tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
与联机索引操作无关的长时间运行的事务需要很大的版本存储区。此版本存储区保存自事务启动以来生成的所有版本。联机索引生成事务可能需要较长时间才能完成，但是使用了专用于联机索引操作的单独的版本存储区。因此，这些操作不会防止删除其他事务的版本。有关详细信息，请参阅行版本控制资源的使用情况。
确定内部对象使用的空间量
下面的查询将返回 tempdb 中内部对象使用的总页数和总空间量 (MB)。
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM _db_file_space_usage;
确定用户对象使用的空间量
下面的查询将返回 tempdb 中用户对象使用的总页数和总空间量。
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM _db_file_space_usage;
确定总空间量（可用空间和已用空间）
下面的查询将返回 tempdb 中所有文件使用的磁盘空间总量。
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM se_files
监视查询使用的空间
最常见的 tempdb 空间使用量问题中，有一种与使用大量空间的大型查询相关联。通常，此空间用于内部对象，例如工作表或工作文件。虽然监视内部对象使用的空间可以使您了解空间的使用情况，但不会直接标识出使用该空间的查询。
下列方法可帮助您标识出使用了 tempdb 中的大多数空间的查询。第一种方法是检查批处理级数据，此方法比第二种方法使用的数据少。第二种方法可用于标识占用磁盘空间的特定查询、临时表或表变量，但要获得答案必须收集更多数据。
方法 1：批处理级信息
如果批处理请求只包含少量查询，并且其中只有一个查询是复杂查询，则此信息通常仅能确定占用空间的批处理，而无法确定特定的查询。
若要继续使用此方法，必须通过使用大约几分钟的轮询间隔，将 SQL Server 代理作业设置为从动态管理视图 _db_session_space_usage 和 _db_task_space_usage 轮询。下面的示例使用了三分钟的轮询间隔。由于 _db_session_space_usage 不包括当前活动任务的分配活动，因此必须从两个视图轮询。通过比较在两个时间间隔分配的页数之差，可以计算出在这两个间隔之间分配的页数。
下列示例提供了 SQL Server 代理作业所需的查询。
A. 获取每个会话中当前运行的所有任务中的内部对象占用的空间
下面的示例创建视图 all_task_usage。执行查询后，视图将返回 tempdb 中当前运行的所有任务中的内部对象使用的总空间量。
CREATE VIEW all_task_usage
AS
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM _db_task_space_usage
GROUP BY session_id;
GO
B. 获取当前会话中正在运行的任务和已完成任务的内部对象占用的空间
下面的示例创建视图 all_session_usage。执行查询后，视图将返回 tempdb 中正在运行的任务和已完成任务中的所有内部对象使用的空间。
CREATE VIEW all_session_usage
AS
SELECT n_id,
al_objects_alloc_page_count
+ _internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
al_objects_dealloc_page_count
+ _internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
FROM _db_session_space_usage AS R1
INNER JOIN all_task_usage AS R2 ON n_id = n_id;
GO
假设查询这些视图的时间间隔为三分钟，结果集将提供下列信息。
在下午 5:00 时，会话 71 自会话开始后分配了 100 页并释放了 100 页。
在下午 5:03 时，会话 71 自会话开始后分配了 20100 页并释放了 100 页。
分析此信息时，您可以由这两个度量得出以下结论：会话为内部对象分配了 20,000 页，并且没有释放任何页。这指示了存在一个潜在问题。
注意
作为数据库管理员，您可以决定将轮询的时间间隔改为小于三分钟。但是，如果查询运行时间少于三分钟，则该查询可能不会占用 tempdb 中的大量空间。
若要确定在此期间运行的批处理，请使用 SQL Server 事件探查器来捕获 RPC:Completed 和
SQL:BatchCompleted 事件类。
除了使用 SQL Server Profiler之外，还可以选择每三分钟为所有会话运行一次 DBCC INPUTBUFFER，如下面的示例所示。
DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM _exec_sessions
SET @i = 51
WHILE @i <= @max BEGIN
IF EXISTS (SELECT session_id FROM _exec_sessions
WHERE session_id=@i)
DBCC INPUTBUFFER (@i)
SET @i=@i+1
END;
方法 2：查询级信息
有时，仅查看输入缓冲区或 SQL Server Profiler事件 SQL:BatchCompleted 并不能确定哪一查询使用了
tempdb 中的大多数磁盘空间。下列方法可用于查找此答案，但是这些方法要收集的数据比方法 1 中定义的过程多。
若要继续使用此方法，请将 SQL Server 代理作业设置为从动态管理视图 _db_task_space_usage
轮询。与方法 1 相比，轮询间隔应该更短（每分钟一次）。使用这样短的间隔的原因为：如果当前未运行查询（任务），则 _db_task_space_usage 不会返回数据。
在轮询查询中，将动态管理视图 _db_task_space_usage 上定义的视图与 _exec_requests
联接在一起，以返回 sql_handle、statement_start_offset、statement_end_offset 和 plan_handle 列。
CREATE VIEW all_request_usage
AS
SELECT session_id, request_id,
SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count
FROM _db_task_space_usage
GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
SELECT n_id, t_id,
t_internal_objects_alloc_page_count, t_internal_objects_dealloc_page_count,
_handle, ent_start_offset, ent_end_offset, _handle
FROM all_request_usage R1
INNER JOIN _exec_requests R2 ON n_id = n_id and t_id = t_id;
GO
如果查询计划位于缓存中，则可以随时检索查询的 Transact-SQL 文本和 XML 显示计划格式的查询执行计划。若要获得已执行查询的 Transact-SQL 文本，请使用值 sql_handle 和动态管理函数
_exec_sql_text。若要获得查询执行计划，请使用值 plan_handle 和动态管理函数
_exec_query_plan。
SELECT * FROM _exec_sql_text(@sql_handle);
SELECT * FROM _exec_query_plan(@plan_handle);
如果查询计划不在缓存中，则可以使用下列方法之一来获得查询的 Transact-SQL 文本和查询执行计划。
A. 使用轮询方法
从视图 all_query_usage 轮询，并运行下面的查询以获得查询文本：
SELECT _handle,
FROM all_query_usage AS R1
OUTER APPLY _exec_sql_text(_handle) AS R2;
由于 sql_handle 对每个唯一的批处理都应该是唯一的，因此不必保存重复的 sql_handle 项。
若要保存计划句柄和 XML 计划，请运行下面的查询。
SELECT _handle, _plan
FROM all_query_usage AS R1
OUTER APPLY _exec_query_plan(_handle) AS R2;
B. 使用 SQL Server 事件探查器事件
除了轮询 _exec_sql_text 和 _exec_query_plan 函数之外，还可以使用 SQL Server
Profiler事件。有一些事件探查器事件可用于捕获查询计划和生成的查询文本。例如，事件 165 将返回跟踪、SQL 文本、查询计划和查询统计信息的性能统计信息。
监视临时表和表变量使用的空间
可以使用一种类似于轮询查询的方法来监视临时表和临时变量使用的空间。在临时表或临时变量中获取大量用户数据的应用程序可能会导致 tempdb 的空间使用问题。这些表或变量属于用户对象。可以使用动态管理视图 _db_session_space_usage 中的 user_objects_alloc_page_count 和
user_objects_dealloc_page_count 列并按照前面介绍的方法进行操作。
监视会话的页分配和页释放
下表显示了动态管理视图 _db_file_space_usage、_db_session_space_usage 和
_db_task_space_usage 返回的指定会话的结果。每行表示 tempdb 中一个指定会话的一次分配或释放活动。活动在“事件”列中列出。其他列显示动态管理视图的列中将返回的值。
对于此情况，假设开始时 tempdb 数据库的未分配区中有 872 页，用户对象保留区中有 100 页。会话为一个用户表分配了 10 页，然后将它们全部释放。前 8 页位于混合区中。其余 2 页位于统一区中。
事dm_db_file_space_usdm_db_file_space_usadm_db_session_spadm_db_session_space件
age ge ce_usage _usage
和
unallocated_extent_puser_object_reserved_和
dm_db_task_space_dm_db_task_space_usage_count 列 page_count 列
usage age
user_object_alloc_puser_object_dealloc_age_count 列 page_count 列
开872
始
100 0 0
从872
现有混合区分配页
1
100 1 0
分864
配页
2
到
8：占用一个新的混合区
80 8 0
分856
配页
9：占用一个新的统一区
108 16 0
从856
现有统一区分配页
10
108 16 0
从856
现有统一区释放108 16 0
页
10
释864
放页
9
和统一区
100 16 8
释864
放页
8
100 16 9
释872
放页
7
到
1，并在混合区释放
100 16 16
发布评论