SQL Server表變量對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*創建表變量後

可以看到數據文件寫入次數以及寫入字節發生了明顯的變化，比較寫入字節數：

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

大約為8.3M，與表變量的數據基本一致，可見創建表變量，確實是發生了IO操作