解决 tempdb 中磁盘空间不足的问题.docx
《解决 tempdb 中磁盘空间不足的问题.docx》由会员分享，可在线阅读，更多相关《解决 tempdb 中磁盘空间不足的问题.docx（10页珍藏版）》请在冰点文库上搜索。
解决tempdb中磁盘空间不足的问题
解决tempdb中磁盘空间不足的问题
tempdb空间要求
tempdb系统数据库是可供连接到SQLServer实例的所有用户使用的全局资源。
tempdb数据库用于存储下列对象：
用户对象、内部对象和版本存储区。
您可以使用sys.dm_db_file_space_usage动态管理视图监视tempdb文件中的用户对象、内部对象和版本存储区使用的磁盘空间。
此外，若要在会话级或任务级监视tempdb中的页分配或页释放活动，可以使用动态管理视图sys.dm_db_session_space_usage和sys.dm_db_task_space_usage。
这些视图可用于标识使用大量tempdb磁盘空间的大型查询、临时表或表变量。
诊断tempdb磁盘空间问题
下表列出了指示tempdb数据库中磁盘空间不足的错误消息。
可以在SQLServer错误日志中找到这些错误，也可以将它们返回到任何正在运行的应用程序。
错误
引发错误的情况
1101或1105
任何会话都必须分配tempdb中的空间。
3959
版本存储区已满。
此错误在日志中通常出现在错误1105或1101之后。
3967
由于tempdb已满，版本存储区被强制收缩。
3958或3966
事务在tempdb中找不到所需的版本记录。
数据库设置为自动增长且数据库大小快速增长时，也会指示出现tempdb磁盘空间问题。
监视tempdb磁盘空间
下列示例说明了如何确定tempdb中的可用空间量，以及如何确定版本存储区、内部对象和用户对象使用的空间量。
确定tempdb中的可用空间量
下面的查询将返回tempdb中所有文件的总可用页数和总可用空间量（MB）。
复制
SELECTSUM（unallocated_extent_page_count）AS[freepages],
（SUM（unallocated_extent_page_count）*1.0/128）AS[freespaceinMB]
FROMsys.dm_db_file_space_usage;
确定版本存储区使用的空间量
下面的查询将返回tempdb中版本存储区使用的总页数和总空间量（MB）。
复制
SELECTSUM（version_store_reserved_page_count）AS[versionstorepagesused],
（SUM（version_store_reserved_page_count）*1.0/128）AS[versionstorespaceinMB]
FROMsys.dm_db_file_space_usage;
确定运行时间最长的事务
如果版本存储区使用了tempdb中的大量空间，则必须确定运行时间最长的事务。
使用下面的查询可按顺序（事务的最长运行时间）列出活动事务。
SELECTtransaction_id
FROMsys.dm_tran_active_snapshot_database_transactions
ORDERBYelapsed_time_secondsDESC;
与联机索引操作无关的长时间运行的事务需要很大的版本存储区。
此版本存储区保存自事务启动以来生成的所有版本。
联机索引生成事务可能需要较长时间才能完成，但是使用了专用于联机索引操作的单独的版本存储区。
因此，这些操作不会防止删除其他事务的版本。
有关详细信息，请参阅行版本控制资源的使用情况。
确定内部对象使用的空间量
下面的查询将返回tempdb中内部对象使用的总页数和总空间量（MB）。
SELECTSUM（internal_object_reserved_page_count）AS[internalobjectpagesused],
（SUM（internal_object_reserved_page_count）*1.0/128）AS[internalobjectspaceinMB]
FROMsys.dm_db_file_space_usage;
确定用户对象使用的空间量
下面的查询将返回tempdb中用户对象使用的总页数和总空间量。
SELECTSUM（user_object_reserved_page_count）AS[userobjectpagesused],
（SUM（user_object_reserved_page_count）*1.0/128）AS[userobjectspaceinMB]
FROMsys.dm_db_file_space_usage;
确定总空间量（可用空间和已用空间）
下面的查询将返回tempdb中所有文件使用的磁盘空间总量。
SELECTSUM（size）*1.0/128AS[sizeinMB]
FROMtempdb.sys.database_files
监视查询使用的空间
最常见的tempdb空间使用量问题中，有一种与使用大量空间的大型查询相关联。
通常，此空间用于内部对象，例如工作表或工作文件。
虽然监视内部对象使用的空间可以使您了解空间的使用情况，但不会直接标识出使用该空间的查询。
下列方法可帮助您标识出使用了tempdb中的大多数空间的查询。
第一种方法是检查批处理级数据，此方法比第二种方法使用的数据少。
第二种方法可用于标识占用磁盘空间的特定查询、临时表或表变量，但要获得答案必须收集更多数据。
方法1：
批处理级信息
如果批处理请求只包含少量查询，并且其中只有一个查询是复杂查询，则此信息通常仅能确定占用空间的批处理，而无法确定特定的查询。
若要继续使用此方法，必须通过使用大约几分钟的轮询间隔，将SQLServer代理作业设置为从动态管理视图sys.dm_db_session_space_usage和sys.dm_db_task_space_usage轮询。
下面的示例使用了三分钟的轮询间隔。
由于sys.dm_db_session_space_usage不包括当前活动任务的分配活动，因此必须从两个视图轮询。
通过比较在两个时间间隔分配的页数之差，可以计算出在这两个间隔之间分配的页数。
下列示例提供了SQLServer代理作业所需的查询。
A.获取每个会话中当前运行的所有任务中的内部对象占用的空间
下面的示例创建视图all_task_usage。
执行查询后，视图将返回tempdb中当前运行的所有任务中的内部对象使用的总空间量。
CREATEVIEWall_task_usage
AS
SELECTsession_id,
SUM（internal_objects_alloc_page_count）AStask_internal_objects_alloc_page_count,
SUM（internal_objects_dealloc_page_count）AStask_internal_objects_dealloc_page_count
FROMsys.dm_db_task_space_usage
GROUPBYsession_id;
GO
B.获取当前会话中正在运行的任务和已完成任务的内部对象占用的空间
下面的示例创建视图all_session_usage。
执行查询后，视图将返回tempdb中正在运行的任务和已完成任务中的所有内部对象使用的空间。
CREATEVIEWall_session_usage
AS
SELECTR1.session_id,
R1.internal_objects_alloc_page_count
+R2.task_internal_objects_alloc_page_countASsession_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count
+R2.task_internal_objects_dealloc_page_countASsession_internal_objects_dealloc_page_count
FROMsys.dm_db_session_space_usageASR1
INNERJOINall_task_usageASR2ONR1.session_id=R2.session_id;
GO
假设查询这些视图的时间间隔为三分钟，结果集将提供下列信息。
∙在下午5:
00时，会话71自会话开始后分配了100页并释放了100页。
∙在下午5:
03时，会话71自会话开始后分配了20100页并释放了100页。
分析此信息时，您可以由这两个度量得出以下结论：
会话为内部对象分配了20,000页，并且没有释放任何页。
这指示了存在一个潜在问题。
注意
作为数据库管理员，您可以决定将轮询的时间间隔改为小于三分钟。
但是，如果查询运行时间少于三分钟，则该查询可能不会占用tempdb中的大量空间。
若要确定在此期间运行的批处理，请使用SQLServer事件探查器来捕获RPC:
Completed和SQL:
BatchCompleted事件类。
除了使用SQLServerProfiler之外，还可以选择每三分钟为所有会话运行一次DBCCINPUTBUFFER，如下面的示例所示。
DECLARE@maxint;
DECLARE@iint;
SELECT@max=max（session_id）
FROMsys.dm_exec_sessions
SET@i=51
WHILE@i<=@maxBEGIN
IFEXISTS（SELECTsession_idFROMsys.dm_exec_sessions
WHEREsession_id=@i）
DBCCINPUTBUFFER（@i）
SET@i=@i+1
END;
方法2：
查询级信息
有时，仅查看输入缓冲区或SQLServerProfiler事件SQL:
BatchCompleted并不能确定哪一查询使用了tempdb中的大多数磁盘空间。
下列方法可用于查找此答案，但是这些方法要收集的数据比方法1中定义的过程多。
若要继续使用此方法，请将SQLServer代理作业设置为从动态管理视图sys.dm_db_task_space_usage轮询。
与方法1相比，轮询间隔应该更短（每分钟一次）。
使用这样短的间隔的原因为：
如果当前未运行查询（任务），则sys.dm_db_task_space_usage不会返回数据。
在轮询查询中，将动态管理视图sys.dm_db_task_space_usage上定义的视图与sys.dm_exec_requests联接在一起，以返回sql_handle、statement_start_offset、statement_end_offset和plan_handle列。
CREATEVIEWall_request_usage
AS
SELECTsession_id,request_id,
SUM（internal_objects_alloc_page_count）ASrequest_internal_objects_alloc_page_count,
SUM（internal_objects_dealloc_page_count）ASrequest_internal_objects_dealloc_page_count
FROMsys.dm_db_task_space_usage
GROUPBYsession_id,request_id;
GO
CREATEVIEWall_query_usage
AS
SELECTR1.session_id,R1.request_id,
R1.request_internal_objects_alloc_page_count,R1.request_internal_objects_dealloc_page_count,
R2.sql_handle,R2.statement_start_offset,R2.statement_end_offset,R2.plan_handle
FROMall_request_usageR1
INNERJOINsys.dm_exec_requestsR2ONR1.session_id=R2.session_idandR1.request_id=R2.request_id;
GO
如果查询计划位于缓存中，则可以随时检索查询的Transact-SQL文本和XML显示计划格式的查询执行计划。
若要获得已执行查询的Transact-SQL文本，请使用值sql_handle和动态管理函数sys.dm_exec_sql_text。
若要获得查询执行计划，请使用值plan_handle和动态管理函数sys.dm_exec_query_plan。
SELECT*FROMsys.dm_exec_sql_text（@sql_handle）;
SELECT*FROMsys.dm_exec_query_plan（@plan_handle）;
如果查询计划不在缓存中，则可以使用下列方法之一来获得查询的Transact-SQL文本和查询执行计划。
A.使用轮询方法
从视图all_query_usage轮询，并运行下面的查询以获得查询文本：
SELECTR1.sql_handle,R2.text
FROMall_query_usageASR1
OUTERAPPLYsys.dm_exec_sql_text（R1.sql_handle）ASR2;
由于sql_handle对每个唯一的批处理都应该是唯一的，因此不必保存重复的sql_handle项。
若要保存计划句柄和XML计划，请运行下面的查询。
SELECTR1.plan_handle,R2.query_plan
FROMall_query_usageASR1
OUTERAPPLYsys.dm_exec_query_plan（R1.plan_handle）ASR2;
B.使用SQLServer事件探查器事件
除了轮询sys.dm_exec_sql_text和sys.dm_exec_query_plan函数之外，还可以使用SQLServerProfiler事件。
有一些事件探查器事件可用于捕获查询计划和生成的查询文本。
例如，事件165将返回跟踪、SQL文本、查询计划和查询统计信息的性能统计信息。
监视临时表和表变量使用的空间
可以使用一种类似于轮询查询的方法来监视临时表和临时变量使用的空间。
在临时表或临时变量中获取大量用户数据的应用程序可能会导致tempdb的空间使用问题。
这些表或变量属于用户对象。
可以使用动态管理视图sys.dm_db_session_space_usage中的user_objects_alloc_page_count和user_objects_dealloc_page_count列并按照前面介绍的方法进行操作。
监视会话的页分配和页释放
下表显示了动态管理视图sys.dm_db_file_space_usage、sys.dm_db_session_space_usage和sys.dm_db_task_space_usage返回的指定会话的结果。
每行表示tempdb中一个指定会话的一次分配或释放活动。
活动在“事件”列中列出。
其他列显示动态管理视图的列中将返回的值。
对于此情况，假设开始时tempdb数据库的未分配区中有872页，用户对象保留区中有100页。
会话为一个用户表分配了10页，然后将它们全部释放。
前8页位于混合区中。
其余2页位于统一区中。
事件
dm_db_file_space_usage
unallocated_extent_page_count列
dm_db_file_space_usage
user_object_reserved_page_count列
dm_db_session_space_usage
和dm_db_task_space_usage
user_object_alloc_page_count列
dm_db_session_space_usage
和dm_db_task_space_usage
user_object_dealloc_page_count列
开始
872
100
0
0
从现有混合区分配页1
872
100
1
0
分配页2到8：
占用一个新的混合区
864
80
8
0
分配页9：
占用一个新的统一区
856
108
16
0
从现有统一区分配页10
856
108
16
0
从现有统一区释放页10
856
108
16
0
释放页9和统一区
864
100
16
8
释放页8
864
100
16
9
释放页7到1，并在混合区释放
872
100
16
16
升级会员 