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

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

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

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

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

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

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

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

参考文章：

http://HdhCmsTestcnblogs测试数据/CareySon/archive/2012/06/11/2544835.html

http://HdhCmsTestcnblogs测试数据/wghao/archive/2011/11/02/2227219.html

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

查看更多关于SQLServer表变量对IO及内存影响测试的详细内容...