Home Dashboard Directory Help
Search

Microsoft SQL Server Management Studio Standard Reports - Disk Usage by Top Tables - Inaccuracy by Chris Howarth


Status: 

Closed


1
0
Sign in
to vote
Type: Bug
ID: 778181
Opened: 2/1/2013 2:32:46 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

The SSMS 'Disk Usage by Top Tables' report contains an inaccurate calculation for the 'Unused (KB)', specifically when the table contains a column of XML datatype where at least one index is defined on the column.

Under this circumstance the value in the Unused (KB) column returned by the report differs to the equivalent column returned by the sp_spaceused stored procedure.

Looking at the SQL generated by the execution of the report it appears that the 'used' value generated within the derived table (aliased as a4) that uses sys.internal_tables is not being included in the calculation of the UnusedKB column, whereas the equivalent logic in the sp_spaceused stored procedure does include the 'used' value derived from sys.internal_tables.

I would like to propose a modification of the SELECT list of the main SQL query generated by the report, from:

(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

...to the following:

(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > (a1.used + ISNULL(a4.used,0)) THEN (a1.reserved + ISNULL(a4.reserved,0)) - (a1.used + ISNULL(a4.used,0)) ELSE 0 END) * 8 AS unused

After amending the calculation in this way the figures returned by the report match those returned by sp_spaceused, and also (in the report): Reserved(KB) = (Unused(KB) + Data(KB) + Indexes(KB))


Details
Sign in to post a comment.
Posted by Microsoft on 6/11/2013 at 3:17 PM
Hello, we took a look at this bug along with several others recently.
Unfortunately, triaging it against other critical bugs, I do not think we would get to investigating this in the near future.
However, we have taken note of this internally, and when we revisit this functionality in the future, we will try and get this resolved.

Thanks for writing in to Microsoft.
Alex Grach[MSFT]
Sign in to post a workaround.