Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our SQL Server 2000 database .mdf file is 27Gb large which seems much larger than is plausible. Using the following query we tried to analyse table sizes:

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

The results were as follows:

Name           Rows       Reserved  Data     Index_Size  Unused
NULL           15274279   26645456  5674592  17361464    3609400
BigTable         875966   16789712   471096  13349816    2968800
  1. How can we find out which objects are causing this massive NULL space usage?
  2. It seems that approx 26GB are "reserved" for NULL, 16GB for BigTable - is this basically a waste of space or are real records involved?
share|improve this question

migrated from stackoverflow.com Mar 8 '13 at 13:25

This question came from our site for professional and enthusiast programmers.

1  
when did you last defragment? which service pack? Do you regularly run DBCC CHECKDB? –  Mitch Wheat Mar 8 '13 at 9:54
3  
So what does select id, reserved from sysindexes order by reserved desc; yield? Also BigTable seems to have 13 GB worth of indexes, so I wonder about your definition of "waste of space"... –  Aaron Bertrand Mar 8 '13 at 13:32
    
For a table of 470 MB you have 13.3 GB of indexes? Do you have an index for every combination of the columns? –  Marian Mar 8 '13 at 14:00
    
You mean a disk defrag? Didn't know that would have any influence on the size of a single file. AFAIK our servers are virtual and using SAN technology. –  Marc Mar 8 '13 at 15:20
    
Aaron, I get 1838629593 2028217 for tBigtable –  Marc Mar 8 '13 at 15:22
show 3 more comments

1 Answer

You're not using WITH ROLLUP correctly. WTIH ROLLUP creates a subtotal and/or total based upon what you tell it. You didn't specify what to call the subtotal and/or total so it is calling it NULL.

This query generates a subtotal report:

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

Output:

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             
share|improve this answer
    
This is neither helpful nor "correct". It is a matter of taste whether a rollup of a null value should display 'UKNOWN' or what-have-you (using CASE) and it's irrelevant to this question. –  Marc Mar 26 '13 at 14:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.