1. 测试创建表变量对io的影响 测试创建表变量前后，tempdb的空间大小，目前使用sp_spaceused得到大小，也可以使用视图sys.dm_db_file_space_usage 14use tempdb go set nocount on exec sp_spaceused /*插入数据之前*/ declare @tmp_orders table ( list_no
1. 测试创建表变量对io的影响
测试创建表变量前后，tempdb的空间大小，目前使用sp_spaceused得到大小，也可以使用视图sys.dm_db_file_space_usage
14use tempdb
go
set nocount on
exec sp_spaceused /*插入数据之前*/
declare @tmp_orders table ( list_no int,id int)
insert into @tmp_orders(list_no,id)
select row_number() over( order by id ) list_no,id
from test.dbo.orders
select top(1) name,object_id,type,create_date
from sys.objects
where type='u' order by create_date desc
exec sp_spaceused /*插入数据之后*/
go
exec sp_spaceused /*go之后*/
执行结果如下：
可以看到：
1) 在表变量创建完毕，同时批处理语句没有结束时，临时库的空间增大了接近9m空间。创建表变量的语句结束后，空间释放
2)在临时库的对象表sys.objects中能够查询到刚刚创建的表变量对象
继续验证是否发生io操作，使用视图sys.dm_io_virtual_file_stats
在创建表变量前后执行如下语句：
select db_name(database_id) database_name,*
from sys.dm_io_virtual_file_stats(db_id('tempdb'), null)
测试结果如下：
1* 创建表变量前
2*创建表变量后
可以看到数据文件写入次数以及写入字节发生了明显的变化，比较写入字节数：
select (2921709568-2913058816)*1.0/1024/1024
大约为8.3m，与表变量的数据基本一致，可见创建表变量，确实是发生了io操作
2. 测试创建表变量对内存的影响
考虑表变量是否占用内存的数据缓冲区，，测试sql如下：
30declare @tmp_orders table ( list_no int,id int)
insert into @tmp_orders(list_no,id)
select row_number() over( order by id ) list_no,id
from test.dbo.orders
--查询tempdb库中最后创建的对象
select top(1) name,object_id,type,create_date from sys.objects where type='u' order by create_date desc
--查询内存中缓存页数
select count(*)as cached_pages_count
,name ,index_id
from sys.dm_os_buffer_descriptors as bd
inner join
(
select object_name(object_id) as name
,index_id ,allocation_unit_id
from sys.allocation_units as au
inner join sys.partitions as p
on au.container_id = p.hobt_id
and (au.type = 1 or au.type = 3)
union all
select object_name(object_id) as name
,index_id, allocation_unit_id
from sys.allocation_units as au
inner join sys.partitions as p
on au.container_id = p.partition_id
and au.type = 2
) as obj
on bd.allocation_unit_id = obj.allocation_unit_id
where database_id = db_id()
group by name, index_id
order by cached_pages_count desc
测试结果如下：
可以看到表变量创建后，数据页面也会缓存在buffer pool中。但所在的批处理语句结束后，占用空间会被释放。
3. 结论
sql server在批处理中创建的表变量会产生io操作，占用tempdb的空间，以及内存bufferpool的空间。在所在批处理结束后，占用会被清除
.syntaxhighlighter{padding-top:20px;padding-bottom:20px;}【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！