How can I determine the space used by a table variable without using DATALENGTH on all columns?
eg:
DECLARE @T TABLE
(
a bigint,
b bigint,
c int,
d varchar(max)
)
insert into @T select 1,2,3, 'abc123'
exec sp_spaceused @T
Trying to work out how much memory a Table variable consumes when running a stored procedure.
I know in this example I can go:
SELECT DATALENGTH(a) + DATALENGTH(b) + DATALENGTH(c) + DATALENGTH(d)
But is there any other way other than doing DATALENGTH on all table columns?
The metadata for table variables is pretty much the same as for other types of tables so you can determine space used by looking in various system views in tempdb.
The main obstacle is that the table variable will be given an auto generated name such as #3D7E1B63 and I'm not sure if there is a straight forward way of determining its object_id.
The code below uses the undocumented %%physloc%% function (requires SQL Server 2008+) to determine a data page belonging to the table variable then DBCC PAGE to get the associated object_id. It then executes code copied directly from the sp_spaceused procedure to return the results.
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
Returns
name rows reserved data index_size unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
#451F3D2B 1 16 KB 8 KB 8 KB 0 KB