我继承了一个相当大的SQL Server数据库。考虑到它包含的数据,它似乎比我预期的要占用更多的空间。

是否有一种简单的方法来确定每个表占用的磁盘空间?


当前回答

扩展到@xav答案,处理表分区以获得MB和GB大小。在SQL Server 2008/2012上测试(注释了一行,其中is_memory_optized=1)

SELECT
    a2.name AS TableName,
    a1.rows as [RowCount],
    --(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB,
    --a1.data * 8 AS DataSize_KB,
    --(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB,
    --(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB,
    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
    CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB,
    --'| |' Separator_MB_GB,
    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB,
    CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB,
    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB,
    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GB
FROM
    (SELECT 
        ps.object_id,
        SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (CASE
                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
        SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
        --===Remove the following comment for SQL Server 2014+
        --WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
    GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
     WHERE it.internal_type IN (202,204)
     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
--AND a2.name = 'MyTable'       --Filter for specific table
--ORDER BY a3.name, a2.name
ORDER BY ReservedSize_MB DESC

其他回答

对于Azure,我使用了:

您应该有SSMS v17.x+

我使用;

正如用户Sparrow所提到的:

打开数据库>并选择表,然后按F7键你应该看到行数作为:

此处的SSMS连接到Azure数据库

下面是另一种方法:使用SQLServerManagementStudio,在对象资源管理器中,转到数据库并选择表

然后打开“对象浏览器详细信息”(按F7或转到“查看”->“对象浏览器详情”)。在对象资源管理器详细信息页面中,右键单击列标题并启用您希望在页面中看到的列。您也可以按任何列对数据进行排序。

如果需要精确计算SSMS中“表财产-存储”页面上的相同数字,则需要使用与SSMS中相同的方法进行计数(适用于sql server 2005及更高版本……也适用于具有LOB字段的表,因为仅计算“used_pages”不足以显示准确的索引大小):

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.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) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName, 
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

我们使用表分区,由于重复记录,上面提供的查询有一些问题。

对于需要此功能的人,您可以在下面找到SQL Server 2014在生成“磁盘使用情况(按表)”报告时运行的查询。我假设它也适用于以前版本的SQL Server。

它就像一个符咒。

SELECT
    a2.name AS [tablename],
    a1.rows as row_count,
    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
    a1.data * 8 AS data,
    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
    (SELECT 
        ps.object_id,
        SUM (
            CASE
                WHEN (ps.index_id < 2) THEN row_count
                ELSE 0
            END
            ) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (
            CASE
                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
        SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
        WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
    GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
     WHERE it.internal_type IN (202,204)
     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name

也许表格有更多的分区文件,必须显示文件顺序

SELECT
  T1.Name                                       AS TableName,
  T5.Name                                       AS SchemaName,
  T3.partition_number                           AS PartionNumber,   
  T3.Rows                                       AS RowsCount,
  SUM(T4.total_pages) * 8                       AS TotalSpaceKB,
  SUM(T4.used_pages) * 8                        AS UsedSpaceKB,
  (SUM(T4.total_pages) - SUM(T4.used_pages)) * 8 AS UnusedSpaceKB
FROM
  sys.objects T1  INNER JOIN 
  sys.indexes T2 ON T1.object_id = T2.object_id  INNER JOIN 
  sys.partitions T3 ON T2.object_id = T3.object_id AND T2.index_id = T3.index_id  INNER JOIN
  sys.allocation_units T4 ON T3.partition_id = T4.container_id LEFT JOIN
  sys.schemas T5 ON T1.schema_id = T5.schema_id
WHERE
  T1.type='U'
GROUP BY
  T1.Name, T5.Name, T3.Rows,T3.partition_number
ORDER BY
  T1.Name,T3.partition_number;