Server 2000下的表大小分析

Question

我们的Server 2000数据库.mdf文件是27 is大，这似乎比可信的要大得多。使用以下查询，我们试图分析表的大小：

select cast(object_name(id) as varchar(50)) AS name,
    sum(CASE WHEN indid<2 THEN rows END) AS rows,
    sum(reserved)*8 AS reserved,
    sum(dpages)*8 AS data,
    sum(used-dpages)*8 AS index_size,
    sum(reserved-used)*8 AS unused
from sysindexes with (nolock)
    where indid in(0,1,255) and id>100 
    GROUP BY id with rollup
    ORDER BY sum(reserved)*8 desc

结果如下：

Name           Rows       Reserved  Data     Index_Size  Unused
NULL           15274279   26645456  5674592  17361464    3609400
BigTable         875966   16789712   471096  13349816    2968800

1. 我们如何才能找出是哪些对象导致了大规模NULL空间的使用？
2. 看起来大约26 or是为NULL“预留”的，16 or是为BigTable预留的--这基本上是浪费空间还是涉及真正的记录？

Answer 1

您没有正确地使用WITH ROLLUP。WTIH ROLLUP根据您告诉它的内容创建一个小计和/或总计。您没有指定调用小计和/或总计的名称，因此它将其命名为NULL。

此查询生成一个小计报告：

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

输出：

Item                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00