Test the effect of SQL Server table variables on IO and memory

1. test the effect of table variable creation on I/O

Test the space size of tempdb before and after table variables are created. Currently, the size is calculated using sp_spaceused. You can also use view sys. dm_db_file_space_usage.

 

14use tempdb

Go

Set nocount on

Exec sp_spaceused/* Before data insertion */

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 data is inserted */

Go

Exec sp_spaceused/* after Go */

The execution result is as follows:

You can see:

1) when the table variable is created and the batch processing statement is not completed, the space of the temporary database increases by nearly 9 MB. Space is released after the statement for creating table variables ends

2) You can query the created table variable object in the object table sys. objects of the temporary database.

Continue to verify whether I/O operations are performed. Use view sys. dm_io_virtual_file_stats.

Run the following statement before and after creating table variables:

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 table Variables

2 * after creating table Variables

We can see that the number of data file writes and the number of written bytes have changed significantly, and the number of written bytes is compared:

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

It is about 8.3 M, which is basically the same as the table variable data. It can be seen that the I/O operation is indeed performed when the table variable is created.

2. test the effect of table variable creation on memory

Consider whether the table variables occupy the memory data buffer. The test SQL is as follows:

 

30 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

-- Query the last object created in the tempdb Database

Select top (1) name, object_id, type, create_date from sys. objects Where type = 'U' Order by create_date Desc

-- Query the number of cached pages in the memory

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:

After the table variables are created, the data page is also cached in the Buffer Pool. However, after the batchcompute statement is completed, the occupied space is released.

3. Conclusion

The table variables created by SQL Server in batch processing produce I/O operations, occupying tempdb space and bufferPool memory space. After the batch processing is completed, the occupied resources are cleared.