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_

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

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

<span><span>use</span><span> tempdb
</span><span>go</span>
<span>Set</span> nocount <span>on</span>
<span>Exec</span> sp_spaceused <span>/*</span><span>插入数据之前</span><span>*/</span>
<span>declare</span> <span>@tmp_orders</span> <span>table</span> ( list_no <span>int</span>,id <span>int</span><span>)
</span><span>insert</span> <span>into</span> <span>@tmp_orders</span><span>(list_no,id)
</span><span>select</span> ROW_NUMBER() <span>over</span>( <span>order</span> <span>by</span><span> Id  ) list_no,id
</span><span>from</span><span> Test.dbo.Orders             
</span><span>Select</span> <span>top</span>(<span>1</span>) name,<span>object_id</span><span>,type,create_date
</span><span>from</span><span> sys.objects 
</span><span>Where</span> type<span>=</span><span>'</span><span>U</span><span>'</span> <span>Order</span> <span>by</span> create_date <span>Desc</span>            
<span>Exec</span> sp_spaceused <span>/*</span><span>插入数据之后</span><span>*/</span>
<span>Go</span>
<span>Exec</span> sp_spaceused <span>/*</span><span>Go之后</span><span>*/</span></span>

执行结果如下：

可以看到：

1） 在表变量创建完毕，同时批处理语句没有结束时，临时库的空间增大了接近9M空间。创建表变量的语句结束后，空间释放

2）在临时库的对象表sys.objects中能够查询到刚刚创建的表变量对象

 

继续验证是否发生IO操作，使用视图sys.dm_io_virtual_file_stats

在创建表变量前后执行如下语句：

<span><span>select</span> <span>db_name</span>(database_id) database_name,<span>*</span>
<span>from</span> sys.dm_io_virtual_file_stats(<span>db_id</span>(<span>'</span><span>tempdb</span><span>'</span>), <span>NULL</span>)</span>

测试结果如下：

1* 创建表变量前

2*创建表变量后

 

可以看到数据文件写入次数以及写入字节发生了明显的变化，比较写入字节数：

select (2921709568-2913058816)*1.0/1024/1024

 

大约为8.3M，与表变量的数据基本一致，可见创建表变量，确实是发生了IO操作

 

2. 测试创建表变量对内存的影响

考虑表变量是否占用内存的数据缓冲区，测试SQL如下：

<span><span>declare</span> <span>@tmp_orders</span> <span>table</span> ( list_no <span>int</span>,id <span>int</span><span>)
</span><span>insert</span> <span>into</span> <span>@tmp_orders</span><span>(list_no,id)
</span><span>select</span> ROW_NUMBER() <span>over</span>( <span>order</span> <span>by</span><span> Id  ) list_no,id
</span><span>from</span><span> Test.dbo.Orders   
</span><span>--</span><span>查询tempdb库中最后创建的对象         </span>
<span>Select</span> <span>top</span>(<span>1</span>) name,<span>object_id</span>,type,create_date <span>from</span> sys.objects <span>Where</span> type<span>=</span><span>'</span><span>U</span><span>'</span> <span>Order</span> <span>by</span> create_date <span>Desc</span>  
<span>--</span><span>查询内存中缓存页数 </span>
<span>SELECT</span> <span>count</span>(<span>*</span>)<span>AS</span><span> cached_pages_count 
    ,name ,index_id 
</span><span>FROM</span> sys.dm_os_buffer_descriptors <span>AS</span><span> bd 
    </span><span>INNER</span> <span>JOIN</span><span> 
    (
        </span><span>SELECT</span> <span>object_name</span>(<span>object_id</span>) <span>AS</span><span> name 
            ,index_id ,allocation_unit_id
        </span><span>FROM</span> sys.allocation_units <span>AS</span><span> au
            </span><span>INNER</span> <span>JOIN</span> sys.partitions <span>AS</span><span> p 
                </span><span>ON</span> au.container_id <span>=</span><span> p.hobt_id 
                    </span><span>AND</span> (au.type <span>=</span> <span>1</span> <span>OR</span> au.type <span>=</span> <span>3</span><span>)
        </span><span>UNION</span> <span>ALL</span>
        <span>SELECT</span> <span>object_name</span>(<span>object_id</span>) <span>AS</span><span> name   
            ,index_id, allocation_unit_id
        </span><span>FROM</span> sys.allocation_units <span>AS</span><span> au
            </span><span>INNER</span> <span>JOIN</span> sys.partitions <span>AS</span><span> p 
                </span><span>ON</span> au.container_id <span>=</span><span> p.partition_id 
                    </span><span>AND</span> au.type <span>=</span> <span>2</span><span>
    ) </span><span>AS</span><span> obj 
        </span><span>ON</span> bd.allocation_unit_id <span>=</span><span> obj.allocation_unit_id
</span><span>WHERE</span> database_id <span>=</span> <span>db_id</span><span>()
</span><span>GROUP</span> <span>BY</span><span> name, index_id 
</span><span>ORDER</span> <span>BY</span> cached_pages_count <span>DESC</span></span>

测试结果如下：

可以看到表变量创建后，数据页面也会缓存在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

如有不对的地方，欢迎拍砖，谢谢！O(∩_∩)O