sql server 临时表(中) Tempdb监控
一. 监控概述
Tempdb库空间使用的一大特点，是只有一部分对象，例如用户创建的临时表、table变量等，可以用sys.allocation_units和sys.partitions这样的管理视图来管理，许多内部对象和版本存储在这些管理视图中没有体现，所以sp_spaceused的结果和真实的使用情况会有很大差异，tempdb的空间使用是不能用sp_spaceused来跟踪的。使用dbcc showfilestats 可以反映数据文件使用的整体情况，但不能监控到空间被谁以什么样的方式用掉。
对于Tempdb的磁盘空间不足，可能会严重破坏 SQL Server 生产环境，并会使正在运行的应用程序无法完成操作。可以使用 sys.dm_db_file_space_usage 动态管理视图来监视 tempdb 文件中的这些功能使用的磁盘空间。此外，若要在会话级或任务级监视 tempdb 中的页分配或页释放活动，可以使用动态管理视图 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage。这些视图可用于标识使用 tempdb 中大量磁盘空间的大型查询、临时表或表变量。还可以使用若干个windows性能计数器来监视 tempdb 中的可用空间以及使用 tempdb 的资源。
(1)使用sys.dm_db_file_space_usage 视图能反映tempdb在几个大类里的空间使用分布， 使用sys.dm_db_session_space_usage 视图能反映tempdb在各会话中空间使用分布。(2)监控磁盘I/O来确认是否有瓶颈。(3)监视TempDB相关的DDL。
二. 监视tempdb空间分布
2.1 sys.dm_db_file_space_usage视图
使用该视图可以：返回数据库中每个文件的空间使用信息。下面例出该视图的主要信息,详细视图查看msdn。通过这个视图就能知道tempdb的空间是被哪一块对象使用掉的，是用户对象，还是内部对象，还是版本存储。
|
列名 |
数据类型 |
说明 |
|
database_id |
smallint |
数据库 ID |
|
file_id |
smallint |
文件 ID |
|
unallocated_extent_page_count |
bigint |
文件的未分配区中的总页数。 不包括已分配区中的未使用页 |
|
version_store_reserved_page_count |
bigint |
为版本存储分配的统一区中的总页数 |
|
user_object_reserved_page_count |
bigint |
从统一区为数据库中的用户对象分配的总页数 |
|
internal_object_reserved_page_count |
bigint |
从统一区为文件中的内部对象分配的总页数 |
2.2 sys.dm_db_session_space_usage视图
使用该视图，能返回数据库每个会话分配和释放的页数。此视图仅适用于tempdb数据库。只有在任务结束时才更新计数器; 统计不反映正在运行的任务。
|
列名 |
数据类型 |
描述 |
|
SESSION_ID |
SMALLINT |
会话ID。 |
|
user_objects_alloc_page_count |
BIGINT |
此会话为用户对象保留或分配的页数 |
|
user_objects_dealloc_page_count |
BIGINT |
此会话取消分配且不再为用户对象保留的页数 |
|
internal_objects_alloc_page_count |
BIGINT |
此会话为内部对象保留或分配的页数 |
|
internal_objects_dealloc_page_count |
BIGINT |
此会话取消分配且不再为内部对象保留的页数。 |
|
user_objects_deferred_dealloc_page_count |
BIGINT |
已标记为延迟释放的页数。 |
2.3 监视的sql脚本
对于监视tempdb三类对象空间分布，这里使用比较"轻量极"对系统影响不大的方法。通过间隔时间运行来监视空间使用，监视包括 DBCC,DMV,DMF 等，把结果输出到一个文件或数据库表里。
-- 间隔每秒执行一次，手工终止或加入作业 use tempdb while 1=1 BEGIN select GETDATE() AS '当前时间' --------------query1 从文件级查看tempdb使用情况------------------ /* TotalExtents 是磁盘占用的空间,一个extent为64k UsedExtents 是磁盘使用的空间 */ dbcc showfilestats ------------- query2 查看当前Tempdb各对象，占用总空间------------- /* user_object_reserved_page_count 用户对象包括： 临时表,表变量,表值函数中返回的表,定义的表和索引 internal_object_reserved_page_count 内部对象包括：排序段,哈希联接,游标 version_store_reserved_page_count 行版本包括： 触发器,运行联机索引,快照隔离级别或使用行版本控制 unallocated_extent_page_count:未分配空间(可用空间) */ select 'Tempdb' as DB, GETDATE() as [Time], SUM(user_object_reserved_page_count)*8.0 as '用户对象占用总空间_kb', SUM(internal_object_reserved_page_count)*8.0 as '内部对象占用总空间_kb', SUM(version_store_reserved_page_count) * 8.0 as '行版本占用总空间_kb', SUM(unallocated_extent_page_count) *8.0 as '未分配总空间_kb' from sys.dm_db_file_space_usage where database_id=2 -----------------query3 查询tempdb各会话，占用和释放的空间------------- SELECT t1.session_id, (t1.internal_objects_alloc_page_count * 8.0) AS internal_objects_alloc_kb, (t1.user_objects_alloc_page_count * 8.0)AS user_objects_alloc_kb, (t1.internal_objects_dealloc_page_count* 8.0) AS internal_objects_dealloc_kb, (t1.user_objects_dealloc_page_count * 8.0) AS user_objects_dealloc_kb from sys.dm_db_session_space_usage as t1, sys.dm_exec_sessions as t3 where t1.session_id = t3.session_id and (t1.internal_objects_alloc_page_count >0 or t1.user_objects_alloc_page_count>0 or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0) and t1.session_id <>@@SPID -----------------query4 查询tempdb 各会话以及sql语句，占用空间------------- /* 会话的内部对象和用户对象的保留或释放的页数 */ SELECT t1.session_id, (t1.internal_objects_alloc_page_count * 8.0) AS internal_objects_alloc_kb, (t1.user_objects_alloc_page_count * 8.0)AS user_objects_alloc_kb, (t1.internal_objects_dealloc_page_count* 8.0) AS internal_objects_dealloc_kb, (t1.user_objects_dealloc_page_count * 8.0) AS user_objects_dealloc_kb, st.text from sys.dm_db_session_space_usage as t1, sys.dm_exec_requests as t4 CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st where t1.session_id = t4.session_id and t1.session_id >50 and (t1.internal_objects_alloc_page_count>0 or t1.user_objects_alloc_page_count >0 or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0) and t1.session_id <>@@SPID END
监听如下图所示：
2.4 行版本监控
行版本是可以跨会话的，所以在sys.dm_db_session_space_usage中只有内部对象和用户对象所占用的空间查看。如果要监听和回收这部分空间，可使用下面脚本：
--(1)另外查询行版本运行最长的事务 select top 2 transaction_id, transaction_sequence_num, elapsed_time_seconds from sys.dm_tran_active_snapshot_database_transactions order by elapsed_time_seconds DESC --(2)根据得到当前会话 select session_id from sys.dm_tran_session_transactions where transaction_id=@transaction_id
三. 监视TempDB的I/O磁盘
由于TempDB被多个地方广泛使用，有可能造成磁盘的消耗较大，成为I/O瓶颈。除了监视windows性能计数器,还有就是DMV视图。
3.1 对于windows性能计数器主要包括三个
-- 每次数据传输的平均时间 AVG.Disk sec/Transfer --磁盘读数据所需的平均时间 AVG.Disk sec/Read --磁盘写数据所需的平均时间 AVG.Disk sec/Write
3.2 DMV视图
下面使用sys.dm_io_virtual_file_stats来确认IO瓶颈，它返回数据文件和日志文件的 I/O 统计信息，包括对文件发出的读取/写入次数以及总字节数， file_id字段中1 是数据文件，2是日志文件，脚本如下：
SELECT DB_NAME(database_id) AS 'DBName', file_id, io_stall_read_ms/num_of_reads AS 'Avg Read ms/Transfer', io_stall_write_ms/num_of_writes AS 'Avg Write ms/Transfer' FROM sys.dm_io_virtual_file_stats(NULL,null) WHERE database_id=2
下面是数据文件及日志文件性能指标建议列表，仅供参考
| 数据文件 | 日志文件 |
| 目标:<10ms | 目标:<5ms |
| 可接受:10 ~ 20 ms | 可接受:5~15ms |
| 不可接受:>20ms | 不可接受:>15ms |
四 监视TempDB相关的DDL
DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上。对于大量，频繁地创建和删除临时表和表变量会引起元数据上的争用。在2008版本中在一定条件下局部临时表和表变量会被缓存，以减少元数据的争用。通过windows性能计数器或sys.dm_os_waiting_tasks 视图可以周期性的检查等待时间较长的会话。
4.1 sys.dm_os_waiting_tasks
使用该视图返回有关正在等待某些资源的任务的等待队列的信息。脚本如下
SELECT session_id,wait_duration_ms,resource_description,GETDATE()AS '当前时间' FROM sys.dm_os_waiting_tasks WHERE resource_description LIKE '2:%' AND wait_type LIKE 'PAGE%LATCH_%' ORDER BY wait_duration_ms desc
4.2 windows性能计数器
SQL Server:Access Methods\Workfiles Created/Sec SQL Server:Access Methods\Worktables Created/Sec SQL Server:Access Methods\Mixed Page Allocations/Sec SQL Server:Access Methods\Temp Tables Created/Sec SQL Server:Access Methods\Temp Tables for destruction
最后还可参考msdn文档：解决 tempdb 中磁盘空间不足的问题
