如何在 SQL Server 中检查表的磁盘空间利用率

在这篇文章中，将演示如何在 SQL Server 中检查磁盘空间利用率，重点介绍如何确定各个表所占用的磁盘空间。监控磁盘空间使用情况对数据库管理员至关重要，以确保数据库高效运行并避免与存储相关的问题。此过程将帮助您了解每个表占用多少磁盘空间，从而更好地规划、优化和管理存储资源。

一.如何检查表的磁盘空间利用率？

在 SQL Server 数据库中，有两种方法可以检查表的磁盘空间利用率：

1. 使用 SQL Server Management Studio 的内置报告功能

我们可以通过 SQL Server Management Studio (SSMS) 的报告功能来查看磁盘空间利用情况。在报告菜单下，有许多报告可以查看，但我们这里将探索“按表磁盘使用情况”报告。以下是使用下载的“NorthWind”数据库演示的例子。

按表磁盘使用情况报告

此报告将显示列表中每个表的总分配空间。在下面的例子中，“Orders” 表占用了最多的空间，其次是“OrderDetails” 表、“Employees” 表等。

2. 使用 SQL 存储过程/查询

如果您想使用存储过程计算 SQL Server 数据库中表的磁盘空间利用率并自定义报告，可以创建一个存储过程。以下是我创建的存储过程，用于计算表的磁盘空间利用率。

CREATE PROCEDURE USP_GetDiskSpaceUsedByTables
AS
BEGIN
    SELECT
        t.name AS TableName, -- 表名
        s.name AS SchemaName, -- 模式名
        p.rows, -- 行数
        SUM(a.total_pages) * 8 AS TotalReservedSpaceKB, -- 总保留空间（KB）
        SUM(a.used_pages) * 8 AS UsedSpaceKB, -- 使用空间（KB）
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, -- 未使用空间（KB）
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalReservedSpaceMB, -- 总保留空间（MB）
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, -- 使用空间（MB）
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB -- 未使用空间（MB）
    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
    LEFT OUTER JOIN
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE
        t.name NOT LIKE 'dt%' -- 排除系统表
        AND t.is_ms_shipped = 0 -- 排除微软自带表
        AND i.object_id > 255
    GROUP BY
        t.name, s.name, p.rows
    ORDER BY
        TotalReservedSpaceMB DESC, t.name -- 按总保留空间排序
END

让我们执行此存储过程并查看结果，结果的计算方式与内置 SQL 报告功能相同。

SQL 报告

为什么要检查 SQL Server 数据库中表的磁盘空间利用率？

以下是检查 SQL Server 数据库中表的磁盘空间利用率的几个关键原因：

• 性能优化：通过了解每个表的空间使用情况，可以优化查询性能。
• 数据管理：有效地管理数据存储和生命周期。
• 成本管理：控制存储成本，避免不必要的开支。
• 合规与审计：满足合规要求，支持审计需求。
• 资源分配：合理分配资源，提高存储利用率。
• 数据归档：实现数据归档和清理策略。
• 索引维护后：在进行索引重建或重组后，检查空间使用情况。

二.总结

在这篇文章中，我解释了在 SQL Server 数据库中监控和管理磁盘空间利用率的有效方法。具体来说，我展示了两种检查表级别磁盘空间利用率的方法。通过使用这些策略，您可以深入了解数据库的存储使用情况，从而更有效地规划您的活动。这种主动管理可以提高数据库环境中的性能和资源分配。