Disk Used by Tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Disk Used by Tables

Disk Used by Tables

Rate Topic

Display Mode

Topic Options

Author

Message

surma.sql

Posted Monday, September 24, 2012 7:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 19, 2012 6:56 PM
Points: 104, Visits: 364

Hi all
Please help me to understand why there is space difference in tables.
please find an attachment.

In tblUsrActivityLog table has 24.5 Millions rows and
tblUserActivityLogArchive table has 25 millions rowa
then why tblUsrActivityLog had disk space 56 GB and tblUserActivityLogArchive had 19 GB.

The no of rows are same, then why its differant in space.

Please Help me to understand.

Post Attachments

Disk_USED_by_Top_tables.png (9 views, 43.76 KB)

Post #1363495

anthony.green

Posted Monday, September 24, 2012 8:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

compression, sparse columns, different columns have data populated (e.g 9 out of 10 columns are null in archive, but 0 out of 10 columns are null in the main table)

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1363529

surma.sql

Posted Monday, September 24, 2012 9:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 19, 2012 6:56 PM
Points: 104, Visits: 364

anthony.green (9/24/2012)

compression, sparse columns, different columns have data populated (e.g 9 out of 10 columns are null in archive, but 0 out of 10 columns are null in the main table)

Ok
that means Archive table has null or 0 values so the data size is less
and main table has values so its more in size

right?

Post #1363565

anthony.green

Posted Monday, September 24, 2012 11:40 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

in theory yea but you will need to check the data yourself, otherwise your looking likely that the table is compressed

Post #1363655

Lowell

Posted Monday, September 24, 2012 11:43 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

also, I believe deleted data does not release space until the clustered index is rebuilt...so if you had a 100 meg table, and deleted half of it, it will still be 100 meg until the index rebuild; also don't forget that indexes take up space also, it's possible that lots of indexes could be larger than the data itself.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1363657

« Prev Topic | Next Topic »

Permissions