主页 / dba / 问题 / 249605
Accepted
Cpt.Whale
Asked: 2019-09-26 08:40:20 +0800 CST

SSMS 和 sp_spaceused 中的表属性不匹配

  • 772

我有一个表Table_A，当我在 SSMS 中右键单击该表并转到“属性”>“存储”时，它显示以下内容：

Data space: 5 GB              
Index space: 250 MB
Number of partitions: 400
Partitions using default columnstore compression: 1-400

如果我这样做sp_spaceused Table_A，它会显示完全不同的数字（四舍五入）：

Name    rows    reserved        data            index_size  unused
Table_A 5000000 100000000 KB    100000000 KB    25000 KB    28000 KB

是什么导致了空间消耗的差异？

数据库的属性页面将 100 GB 保留空间显示为“可用”，但在dbcc shrinkdb

在重建聚集列存储索引时，该表突然“增长”，其中索引大小增长到 ~20 GB，但在作业完成后又缩小了。我仍在努力研究列存储压缩和索引，所以我觉得我遗漏了一些简单的东西

sql-server sql-server-2017

1 个回答

  1. Best Answer

    使用的数据空间

    dbo.Employees在选择我的表的存储属性时，用于通过 ssms 计算表上数据空间的查询

    exec sp_executesql N'
        declare @PageSize float
        select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1



SELECT

        case
        when (tbl.is_memory_optimized=0) then
        ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
        FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        where i.object_id = tbl.object_id),0.0)
        else
        isnull((select (tms.[memory_used_by_table_kb])
        from [sys].[dm_db_xtp_table_memory_stats] tms
        where tms.object_id = tbl.object_id), 0.0)
        end
       AS [DataSpaceUsed]
FROM
sys.tables AS tbl
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
OPTION (FORCE ORDER)
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'1',@_msparam_1=N'E',@_msparam_2=N'Employees',@_msparam_3=N'dbo'

    执行这个查询给我：

    DataSpaceUsed
20008

    这是KB的数据，为了计算，继续往下看。

    通过 SSMS 它给了我：

    在此处输入图像描述

    让我们进一步探索这个查询的一部分，针对普通的、非内存优化的表。

    when (tbl.is_memory_optimized=0) then
        ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

    将 pagesize 变量作为 dbo.spt_values / 1024 的值

    select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1

    哪个可以改成

        declare @PageSize float
    select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
  select @PageSize

    = 8

    这是sql server使用的8KB页面大小

    然后下一部分，CASE WHEN a.type <> 1 THEN a.used_pages

    类型 1 是：

    1 = 行内数据（所有数据类型，LOB 数据类型除外）

    资源

    下一部分

    WHEN p.index_id < 2 THEN a.data_pages ELSE 0

    获取聚簇/堆数据页，而不是非聚簇索引页。

    所以简而言之，获取属于堆或聚集索引的lob数据和数据页sys.allocation_units

    这会把我们带到哪里？

    这让我们链接到不同的 Q/A，提问者试图通过使用 DMV 来获得高性能和正确的空间使用查询。

    其他链接的答案也是如此， Solomon Rutzky的两个优秀答案

    以下基本查询有效。它使用相同的算法获得与 sp_spaceused 相同的输出，但效率更高......

    所以是的，sp_spaceused会更正确，因为用于计算大小的算法会提供更多信息，链接的 Q/A 中的查询执行相同但效率更高。

    您可以查看通过使用CTRL+Psp_spaceused启用的实际执行计划运行所触及的 dmv ，或者查看这个，当我运行时sp_spaceuseddbo.Employees

    sys.dm_db_partition_statsinsp_spaceused和查询链接的用法与 via ssms 的用法也sys.allocation_units很明显。

    • 3

相关问题