萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> SQL Server表變量對IO及內存影響測試

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之後*/

  執行結果如下:

SQL Server表變量對IO及內存影響測試  三聯

  可以看到:

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

copyright © 萬盛學電腦網 all rights reserved