SQL Largest tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SQL Largest tables

SQL Largest tables

Rate Topic

Display Mode

Topic Options

Author

Message

SQLSACT

Posted Saturday, October 20, 2012 2:39 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:44 AM
Points: 1,185, Visits: 2,099

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

Post #1375127

Jeff Moden

Posted Sunday, October 21, 2012 12:19 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 32,890, Visits: 26,760

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1375192

SQLSACT

Posted Sunday, October 21, 2012 7:43 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:44 AM
Points: 1,185, Visits: 2,099

Jeff Moden (10/21/2012)

Thanks

Post #1375233

mahesh.dasoni

Posted Tuesday, October 23, 2012 1:08 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 04, 2013 1:55 AM
Points: 81, Visits: 858

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

Post #1375863

« Prev Topic | Next Topic »

Permissions