SQL Server table variables test for IO and memory impact

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

14use tempdb

Go

Set Nocount ON

Exec sp_spaceused/* Before inserting data * *

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/* After inserting data * *

Go

After 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

2. Test CREATE table variables on memory impact

Consider whether the table variable consumes memory data buffers, and test SQL is as follows:

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

--Querying the last object created in the tempdb library

Select Top (1) name,object_id,type,create_date from sys.objects Where type= ' U ' ORDER by Create_date Desc

--Query in-memory cache pages

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

The test results are as follows:

You can see that when the table variable is created, the data page is also cached in the buffer pool. However, when the batch statement is in place, the footprint is freed.

3. Conclusion

The table variables created by SQL Server in the batch produce IO operations, occupy the space of tempdb, and bufferpool memory space. When the batch is finished, the occupancy is cleared