Test for IO and memory impact of SQL Server table variables

1. Test creation table variables impact on IO

Before and after the test creates a table variable, the space size of tempdb is currently sized using sp_spaceused, or you can use the view Sys.dm_db_file_space_usage

sp_spaceused
/Declare @tmp_orders table (list_no int,id int) before inserting data by use of tempdb go Set nocount on Exec/*
INSERT INTO @tmp_orders (list_no,id)
Select Row_number () over (the 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 ' Create_date Desc          
exec sp_spaceused/* After inserting data/go
Exec sp_spaceused/*go * *

The results of the implementation are as follows:

You can see:

1 when the table variable is created and the batch statement does not end, the space of the temporary library increases by nearly 9M space. When the statement that creates the table variable ends, the space is freed

2 The table variable object that you just created can be queried in the object table sys.objects of the temporary library

Continue to verify that IO operation occurs, using view Sys.dm_io_virtual_file_stats

Execute the following statement before and after creating the table variable:

Select Db_name (database_id) database_name,*
From Sys.dm_io_virtual_file_stats (db_id (' tempdb '), NULL)

The test results are as follows:

1* before creating a table variable

2* After creating a table variable

You can see a significant change in the number of data file writes and write bytes, compared to the number of bytes written:

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

Approximately 8.3M, which is basically consistent with the data of the table variable, which creates a table variable, and indeed an IO operation occurs