sp_spaceused - unused

I noticed the SSMS built in disk usage reports were reporting unusually low values for unused space.  sp_spaceused reports the same.  I did a brief experiment:

I ran a maintenance plan to rebuild indexes with the option to use 10% free space.  sp_spaceused reports:

size = 24 GB, unallocated = 5 GB, reserved = 19 GB, data = 10 GB, index size = 9 GB, unused = 0 (values are rounded).

Then the same thing with 50% free space and sp_spaceused reports:

size = 37 GB, unallocated = 4 GB, reserved = 34 GB, data = 18 GB, index size = 16 GB, unused = 0

So the question is, shouldn't unused be showing a big number in the second case to reflect the 50% free space on indexes?
If not, how would one tell that there is actually a bunch of free space?

Sql Server 2005 64-bit standard edition on Server 2008, up to date service packs.  I get the same results on 3 identical servers, and multiple databases so hopefully I'm just lacking some knowledge.

Thanks
LVL 21
mastooAsked:
Who is Participating?
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
So the 2 numbers that should actually make sense in the output are these:

unallocated = the space that is in the file that is not assigned to any object (you have a 20 GB file and 18 GB is allocated in pages to objects, so 2 GB is Unallocated and would show in this column)

unused = the space that has been reserved by objects but does not have data in it.  This includes indexes and data pages.

It should reconcile, and the way you may be able to tell is to use an object on the sp_spaceused.

sp_spaceused 'tablename'
this would should you the rowcount, reserved amount of space, data and index usage and what was unused in the table or index pages.

I am not sure without testing that the index unused space goes in the unused column.  The BOL assures us that the data space unused goes in the unused, but not sure about the indexes.
0
 
chapmandewCommented:
Unused is the amount of space reserved for db objects, but not yet used.

unallocated is the amount of space that has not yet been reserved for db objects

0
 
chapmandewConnect With a Mentor Commented:
so, for yours, this makes sense:

size = 24 GB, unallocated = 5 GB, reserved = 19 GB, data = 10 GB, index size = 9 GB, unused = 0

24 GB total

5 is unallocated (not reserved)

19 is reserved (10 for data, 9 for indexes)

19 + 5 = 24
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
mastooAuthor Commented:
Hi chapmandew, and thanks for the post .  Yes, those numbers all add up.  My question though is, how would one know that after the 50% free space index rebuild there is an additional 15 GB (34 - 19) of reserved space but that space is all "empty"?
0
 
chapmandewCommented:
Sorry...I guess Im not exactly sure what you're asking...
0
 
mastooAuthor Commented:
Sorry, let me try again.

The database is originally 24 GB.  I rebuild the indexes specifying to leave 50% free space instead of 10%and this causes the database to grow to 37 GB.  So there would seem to be an additional 13 GB of empty space.  I thought the "unused" output on sp_spaceused would show me this empty space but it doesn't.

Real world, someone shows me a big database and wonders why it is so big.  How would I recognize that it had indexes built with a high amount of free space and tell them that simply rebuilding the indexes with less free space would make the database much smaller?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
This is due to the fact that the unused space is space that is NOT reserved or allocated to database objects.  The indexes that are at 50% space used on each page, still allocate the pages to that object, so it is literally allocated but may not contain data.  Unused space is that space that does not have any object's data on it.

so you could look at sp_spaceused and see the reserved and the data size, but the main thing you would have to do is to use the dynamic management views like sys.dm_db_index_physical_stats and give it an index.
0
 
mastooAuthor Commented:
Yes, but...  

You're saying this extra 50% is allocated to the indexes and doesn't have anything in it, but doesn't that mean it should be showing up as a big number for "unused" from sp_spaceused?  The documentation for sp_spaceused unused says:

Total amount of space reserved for objects in the database, but not yet used

but I unused remains small in my test.  And I see what you're saying about the dynamic management view but it seems like a lot of work to go from that to get my answer.
0
 
mastooAuthor Commented:
Yes, they reconcile.  You reach the point I'm at where I conclude the index unused space isn't included in the "unused" number, which makes it fairly useless.  Argh.  I'll move on and accept.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.