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

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的空间。在所在批处理结束后，占用会被清除