sp_spaceused - 如何在 SQL 中测量所有表中的大小(以 GB 为单位)

2021-09-10 00:00:00 sql size tsql sql-server

按照如何在 SQL 中的表中以 GB 为单位测量表大小 中的讨论，我正在寻找解决方案使用存储过程 sp_spaceused 单独测量 SQL Server 的所有表使用的空间.

Following the discussion in How to measure table size in GB in a table in SQL, I'm looking for a solution to measure the space used by all the tables of a SQL Server individually using the store procedure sp_spaceused.

推荐答案

以下基本查询有效.它使用相同的算法获得与 sp_spaceused 相同的输出，但效率更高.请不要使用CURSOR + sp_spaceused 方法；绝对没有理由这样做.使用 sp_spaceused 的一个潜在问题是它旨在成为一个报告过程，因此输出都是文本，而不是实际数字，并且将其解析回数字可能容易出错.

The following base query works. It gets the same output as sp_spaceused, using the same algorithm, but much more efficiently. Please do not use the CURSOR + sp_spaceused method; there is absolutely no reason to do that. And a potential problem with using sp_spaceused is that it is intended to be a report proc so the output is all text, not actual numbers, and parsing that back into numbers can be error-prone.

最好不要使用 sys.tables 或 sp_msforeachtable，因为它们都排除索引视图.

It is also best to not use either sys.tables or sp_msforeachtable as they both exclude indexed views.

以下和 sp_spaceused 完全一样:

The following is exactly the same as sp_spaceused in terms of:

  • 包括 XML 索引、全文索引、索引视图等.
  • 细分数据与索引空间使用的信息

如果您需要它适用于所有数据库，它也可以轻松适应.

If you need it to work for all databases, it can be easily adapted for that as well.

如果您需要按索引细分此数据，我已调整以下查询以回答 DBA.StackExchange 上的此问题:sys.allocation_units 和 sp_spaceused 上的空间使用情况

If you need this data broken down per index, I have adapted the following query in response to this question on DBA.StackExchange: space usage on sys.allocation_units and sp_spaceused

;WITH extra AS
(   -- Get info for FullText indexes, XML Indexes, etc
    SELECT  sit.[object_id],
            sit.[parent_id],
            ps.[index_id],
            SUM(ps.reserved_page_count) AS [reserved_page_count],
            SUM(ps.used_page_count) AS [used_page_count]
    FROM    sys.dm_db_partition_stats ps
    INNER JOIN  sys.internal_tables sit
            ON  sit.[object_id] = ps.[object_id]
    WHERE   sit.internal_type IN
               (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
    GROUP BY    sit.[object_id],
                sit.[parent_id],
                ps.[index_id]
), agg AS
(   -- Get info for Tables, Indexed Views, etc (including "extra")
    SELECT  ps.[object_id] AS [ObjectID],
            ps.index_id AS [IndexID],
            SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            SUM(ps.row_count) AS [RowCount],
            SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
                    AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    GROUP BY    ps.[object_id],
                ps.[index_id]
    UNION ALL
    SELECT  ex.[parent_id] AS [ObjectID],
            ex.[object_id] AS [IndexID],
            0 AS [InRowDataPageCount],
            SUM(ex.used_page_count) AS [UsedPageCount],
            SUM(ex.reserved_page_count) AS [ReservedPageCount],
            0 AS [RowCount],
            0 AS [LobAndRowOverflowUsedPageCount]
    FROM    extra ex
    GROUP BY    ex.[parent_id],
                ex.[object_id]
), spaceused AS
(
SELECT  agg.[ObjectID],
        OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
        OBJECT_NAME(agg.[ObjectID]) AS [TableName],
        SUM(CASE
                WHEN (agg.IndexID < 2) THEN agg.[RowCount]
                ELSE 0
            END) AS [Rows],
        SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
        SUM(agg.LobAndRowOverflowUsedPageCount +
            CASE
                WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
                ELSE 0
            END) * 8 AS [DataKB],
        SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
            CASE
                WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
                ELSE 0
            END) * 8 AS [IndexKB],
        SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
        SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
            OBJECT_SCHEMA_NAME(agg.[ObjectID]),
            OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
       sp.TableName,
       sp.[Rows],
       sp.ReservedKB,
       (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
       sp.DataKB,
       (sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
       sp.IndexKB,
       (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
       sp.UsedKB AS [UsedKB],
       (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
       sp.UnusedKB,
       (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
       so.[type_desc] AS [ObjectType],
       so.[schema_id] AS [SchemaID],
       sp.ObjectID
FROM   spaceused sp
INNER JOIN sys.all_objects so
        ON so.[object_id] = sp.ObjectID
WHERE so.is_ms_shipped = 0
--AND so.[name] LIKE N''  -- optional name filter
--ORDER BY ??

相关文章