SQL Largest tables

Question

SQL Largest tables

SQLSACT

SSC-Insane

Points: 21838
More actions
October 20, 2012 at 2:39 pm

#283870

Hi All
I'm using the built in "Disk Usage by Top Tables Report" in SQL Management Studio to check the tables on my database.
How is the unused space calculated? Is this the amount of space that was created by page splits?
My database has a significant amount of tables with an unused space amount of 4GB+.
According to http://msdn.microsoft.com/en-us/library/cc280506(v=sql.100).aspx, the unused space is:The amount of disk space allocated to one or more objects, but not yet used.
From what I understand, you can't explicitly allocate space to a table.
Any help?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

The unused space is calculated simply by finding the total space allocated and subtracting the space used by data and indexes. If you want to do a deep dive on the subject, see the sp_spaceused stored procedure in the master database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Jeff Moden (10/21/2012)
The unused space is calculated simply by finding the total space allocated and subtracting the space used by data and indexes. If you want to do a deep dive on the subject, see the sp_spaceused stored procedure in the master database.

Thanks

mahesh.dasoni SSCommitted Points: 1653 More actions · Answer 3

This query might help

select

a1.object_id

,a2.name

, a1.used_page_count * 8 as total_used_pages

, cast(a1.used_page_count * 8 /1000 as varchar(10)) + 'MB' as SIZE_total_used_pages

, a1.reserved_page_count * 8 as total_reserved_pages

, a1.row_count

from sys.dm_db_partition_stats a1

inner join sys.all_objects a2 on ( a1.object_id = a2.object_id )

left outer join sys.indexes a3 on ( (a1.object_id = a3.object_id) and (a1.index_id = a3.index_id) )

where (select count(distinct partition_number)

from sys.dm_db_partition_stats a4

where (a4.object_id = a1.object_id)) >= 1 and a2.type <>'S'

--and (a1.used_page_count * 8 /1000) > 1 --Uncomment this line to list tables that occupy >1MB space

order by a1.used_page_count desc, a2.name asc, a1.index_id, a1.partition_number