SqlServer 临时表tempdb存储对象

临时表tempdb存储的三种类型对象:用户对象,内部对象,版本存储区




【用户对象:存储临时表,表变量】

--在任意数据库创建临时表,都存储在tempdb下的临时表目录中create table #tab1(id int)--作用当前session,退出当前session则消失create table ##tab2(id int)--作用所有session,退出当前session则消失--在临时数据库创建的实体表,存储在tempdb下的表目录中use tempdbcreate table tab3(id int) --重启服务时重建tempdb则消失--变量作用当前session某个批处理中declare @var1 int;declare @@var2 intdeclare @tab4 table(id int)declare @@tab5 table(id int)

临时表和表变量主要区别:1. 表变量不会产生统计信息,估计行数都为1.2. 表变量不能创建索引,可以创建约束.创建主键约束或唯一约束时创建的索引也无效.3. 表变量不支持架构更改,临时表可以更改.4. 表变量和临时表都是存储在tempdb中的,不是内存结构.

下面测试tempdb的存储情况

--查看当前session在tempdb的存储情况select * from sys.dm_db_session_space_usagewhere database_id = 2 and session_id = @@SPID--添加临时表数据create table #tab1(id int)insert into #tab1 values(1)select * from sys.dm_db_session_space_usagewhere database_id = 2 and session_id = @@SPID--tempdb增加1页用户对象user_objects_alloc_page_count--添加表变量数据declare @tab4 table(id int)insert into @tab4 values(1)select * from sys.dm_db_session_space_usagewhere database_id = 2 and session_id = @@SPID--可以看到，tempdb增加1页user_objects_alloc_page_count，然后减少1页user_objects_dealloc_page_count--这是因为表变量@tab4被释放了，查询已无效select * from @tab4

【内部对象:哈希,分组,排序,spool,游标等临时存储对象】
每个内部对象至少使用九页；一个 IAM 页，一个八页的区

【版本存储区:版本存储区是数据页的集合，它包含支持使用行版本控制的功能所需的数据行:触发器,快照隔离,联机索引操作,多活动结果集】

更多参考：tempdb 容量规划

--行版本在活动事务必须对其进行访问时一直保存在 tempdb 版本存储区中。--查看当前版本存储区的二进制内容SELECT * FROM sys.dm_tran_version_store--由于版本存储区页是全局资源，所以在文件级别对其进行跟踪。--查看版本存储区的当前页数SELECT SUM(unallocated_extent_page_count) AS 'tempdb pages',  SUM(user_object_reserved_page_count) AS 'user object pages',  SUM(internal_object_reserved_page_count) AS 'internal object pages',  SUM(version_store_reserved_page_count) AS 'in use pages',  SUM(mixed_extent_page_count) AS 'Total Mixed Extent Pages'  FROM sys.dm_db_file_space_usage WHERE database_id = 2  --'Tempdb' 总申请大小Select 'Tempdb' as DB, getdate() as Time,                                                            SUM (user_object_reserved_page_count)*8 as user_objects_kb,              SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,      SUM (version_store_reserved_page_count)*8  as version_store_kb,          SUM (unallocated_extent_page_count)*8 as freespace_kb                From sys.dm_db_file_space_usage                                          Where database_id = 2  --版本存储区清除必须考虑需要访问特定版本的运行时间最长的事务--发现与版本存储区清除相关的运行时间最长的事务SELECT * FROM sys.dm_tran_active_snapshot_database_transactions order by elapsed_time_seconds desc --使用计数器(SQLServer:Transactions )监视 tempdb 中行版本存储区的大小和增长速率SELECT * FROM sys.dm_os_performance_counterswhere counter_name='Free Space in tempdb (KB)'or counter_name='Version Store Size (KB)'--tempdb大小和增长速率SELECT     name AS FileName,     size*1.0/128 AS FileSizeinMB,    CASE max_size         WHEN 0 THEN 'Autogrowth is off.'        WHEN -1 THEN 'Autogrowth is on.'        ELSE 'Log file will grow to a maximum size of 2 TB.'    END,    growth AS 'GrowthValue',    'GrowthIncrement' =         CASE            WHEN growth = 0 THEN 'Size is fixed and will not grow.'            WHEN growth > 0 AND is_percent_growth = 0                 THEN 'Growth value is in 8-KB pages.'            ELSE 'Growth value is a percentage.'        ENDFROM tempdb.sys.database_files;GO

--tempdb 中 有多少临时表或表变量,有多少在使用中SELECT k.name, k.[type],k.entries_count, k.entries_in_use_countFROM sys.dm_os_memory_cache_counters AS kWHERE k.[type] = N'CACHESTORE_TEMPTABLES';