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 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的空間。在所在批處理結束後，占用會被清除