SQLServer表变量对IO及内存影响测试

1. 测试创建表变量对IO的影响

测试创建表变量前后，tempdb的空间大小，目前使用sp_spaceused得到大小，也可以使用视图sys.dm_db_file_space_usage

use 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*创建表变量后

?

declare @tmp_orders table ( list_no int,id from Test.dbo.Orders --查询tempdb库中最后创建的对象 
Select top(1) name,object_id,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,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的空间。在所在批处理结束后，占用会被清除

?

参考文章：

http://www.cnblogs.com/CareySon/archive/2012/06/11/2544835.html

http://www.cnblogs.com/wghao/archive/2011/11/02/2227219.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!