sql - 如何检查表中正在使用的空间，包括幽灵记录

Question

我在 StackOverflow 的其他地方找到了这个脚本，它告诉我关于给定表使用了多少空间的统计信息：

SELECT 
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages, 
 SUM(a.used_pages) AS UsedPages, 
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM 
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
 t.NAME = 'StmALog' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
 OBJECT_NAME(i.object_id)

这似乎运作良好并给了我很好的结果，但是我不确定这些统计数据是否也考虑了幽灵记录。

有人可以帮我验证一下这个脚本正在计算一个表使用的空间，包括可能挂着的任何幽灵记录吗？

标签： sqlsql-servertsql

Answer 1

文档中没有提及，但有报道称人们想知道为什么要sys.allocation_units返回表的数据，当执行巨大的删除/截断意味着系统视图ghost records也返回时。

无论如何，如果启用了幽灵清理（默认情况下已启用），我认为您不必为幽灵记录担心太多。此外，不建议禁用它：

通常不建议禁用重影清理过程。这样做应该在受控环境中进行彻底测试，然后才能在生产环境中永久实施。

您可以使用以下查询检查特定表的幽灵记录计数：

SELECT [ghost_record_count]
      ,[version_ghost_record_count]
FROM [sys].[dm_db_index_physical_stats] (DB_ID(N'dabase_name'), OBJECT_ID(N'schema_name.table_name'), NULL, NULL , 'DETAILED');
GO

因此，通过上述查询，我​​们可以尝试测试您的查询是否也返回幽灵记录。

CREATE TABLE [dbo].[StackOverflow]
(
    [ID]  BIGINT IDENTITY(100000, 1) PRIMARY KEY
   ,[text] NVARCHAR(MAX)
   ,[date] DATETIME2 CONSTRAINT DF_StackOverflow_date DEFAULT (SYSDATETIME())
);


INSERT INTO [dbo].[StackOverflow] ([text])
SELECT TOP 100000
           REPLICATE(CAST(NEWID() AS NVARCHAR(MAX)), 255)
FROM master..spt_values t1 
CROSS JOIN master..spt_values t2;

EXEC sp_spaceused '[dbo].[StackOverflow]';

-- your script for detecting size

GO

DBCC TRACEOFF(661); -- disable ghost cleaner; DO NOT RUN ON PRODUCTION DATABASE


DELETE FROM [dbo].[StackOverflow];

SELECT [ghost_record_count]
      ,[version_ghost_record_count]
FROM [sys].[dm_db_index_physical_stats] (DB_ID(N'StackOverflow'), OBJECT_ID(N'dbo.StackOverflow'), NULL, NULL , 'DETAILED');

EXEC sp_spaceused '[dbo].[StackOverflow]'

-- your script for detecting size

-- DROP TABLE IF EXISTS [dbo].[StackOverflow];
-- GO

因此，禁用 ghost 清理器后，我们可以看到表没有行，但有 ghost 数据/行：

启用标志后，幽灵读取被清除：

DBCC TRACEON(661);

SELECT [ghost_record_count]
      ,[version_ghost_record_count]
FROM [sys].[dm_db_index_physical_stats] (DB_ID(N'smModel_20180828_gk7'), OBJECT_ID(N'dbo.StackOverflow'), NULL, NULL , 'DETAILED');

EXEC sp_spaceused '[dbo].[StackOverflow]';

如果要测试，请不要禁用生产数据库上的标志，您可能需要增加插入的行。