不使用DATALENGTH的sql表变量的大小

Question

在不对所有列使用DATALENGTH的情况下，如何确定表变量使用的空间？

例如：

DECLARE @T TABLE
(
a bigint,
b bigint,
c int,
d varchar(max)
)

insert into @T select 1,2,3, 'abc123'

exec sp_spaceused @T

试图计算出在运行存储过程时一个表变量消耗了多少内存。

我知道在这个例子中我可以这样做：

SELECT DATALENGTH(a) + DATALENGTH(b) + DATALENGTH(c) + DATALENGTH(d)

但是，除了对所有表列执行DATALENGTH之外，还有其他方法吗？

Answer 1

表变量的元数据与其他类型的表基本相同，因此您可以通过查看tempdb中的各种系统视图来确定所使用的空间。

主要的障碍是表变量将被赋予一个自动生成的名称，比如#3D7E1B63，我不确定是否有一种直接的方法来确定它的object_id。

下面的代码使用未公开的%%physloc%%函数(需要SQL Server 2008+)来确定属于表变量的数据页，然后使用DBCC PAGE来获取相关的object_id。然后，它执行直接从sp_spaceused过程复制的代码以返回结果。

DECLARE @T TABLE
(
a bigint,
b bigint,
c int,
d varchar(max)
)

insert into @T select 1,2,3, 'abc123'
DECLARE @DynSQL nvarchar(100)

SELECT TOP (1) @DynSQL = 'DBCC PAGE(2,' + 
                       CAST(file_id AS VARCHAR) + ',' + 
                       CAST(page_id AS VARCHAR) + ',1) WITH TABLERESULTS' 
FROM @T
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)


DECLARE @DBCCPage TABLE (
    [ParentObject] [varchar](100) NULL,
    [Object] [varchar](100) NULL,
    [Field] [varchar](100) NULL,
    [VALUE] [varchar](100) NULL
) 
INSERT INTO @DBCCPage
EXEC (@DynSQL)

DECLARE @id int

SELECT @id = VALUE
FROM @DBCCPage 
WHERE Field = 'Metadata: ObjectId'



EXEC sp_executesql N'
USE tempdb

declare @type character(2) -- The object type.  
  ,@pages bigint   -- Working variable for size calc.  
  ,@dbname sysname  
  ,@dbsize bigint  
  ,@logsize bigint  
  ,@reservedpages  bigint  
  ,@usedpages  bigint  
  ,@rowCount bigint  
/*  
 ** Now calculate the summary data.   
 *  Note that LOB Data and Row-overflow Data are counted as Data Pages.  
 */  
 SELECT   
  @reservedpages = SUM (reserved_page_count),  
  @usedpages = SUM (used_page_count),  
  @pages = SUM (  
   CASE  
    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
    ELSE lob_used_page_count + row_overflow_used_page_count  
   END  
   ),  
  @rowCount = SUM (  
   CASE  
    WHEN (index_id < 2) THEN row_count  
    ELSE 0  
   END  
   )  
 FROM sys.dm_db_partition_stats  
 WHERE object_id = @id;  

 /*  
 ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table  
 */  
 IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0   
 BEGIN  
  /*  
  **  Now calculate the summary data. Row counts in these internal tables don''t   
  **  contribute towards row count of original table.  
  */  
  SELECT   
   @reservedpages = @reservedpages + sum(reserved_page_count),  
   @usedpages = @usedpages + sum(used_page_count)  
  FROM sys.dm_db_partition_stats p, sys.internal_tables it  
  WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;  
 END  

 SELECT   
  name = OBJECT_NAME (@id),  
  rows = convert (char(11), @rowCount),  
  reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + '' KB''),  
  data = LTRIM (STR (@pages * 8, 15, 0) + '' KB''),  
  index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + '' KB''),  
  unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + '' KB'')  


', N'@id int',@id=@id

返回

name                           rows        reserved           data               index_size         unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
#451F3D2B                      1           16 KB              8 KB               8 KB               0 KB