SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Table size vs disk space
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

daq
Starting Member

4 Posts

Posted - 11/06/2013 :  15:12:36  Show Profile  Reply with Quote
I have a table with 64682087 rows that consists of 8 int and 2 bigint columns. Table size according to above should be:
(4 * 8 + 8 * 2) * 64682087 = 3104740176 bytes = ~3GB

sp_spaceused is reporting that table size is 34695824 KB = ~33GB and index is another ~7GB.

What am I missing?

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 11/07/2013 :  01:33:32  Show Profile  Reply with Quote
fill factor?
cluster index /heap table?

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

daq
Starting Member

4 Posts

Posted - 11/07/2013 :  04:13:28  Show Profile  Reply with Quote
Aren't those reported by sp_spaceused?
Index is reported at 7GB. Still doesn't add up to 33GB.
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 11/07/2013 :  06:40:04  Show Profile  Reply with Quote
sp_spaceused [[ @objname = ] 'objname' ] [,[ @updateusage = ] 'updateusage' ]

from http://technet.microsoft.com/en-us/library/ms188776.aspx

try to run with the updateusage argument.

quote:

There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.



Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 11/07/2013 :  12:29:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
Show us the output of sp_spaceused for the table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

daq
Starting Member

4 Posts

Posted - 11/07/2013 :  14:15:55  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Show us the output of sp_spaceused for the table.




name	rows		reserved	data		index_size	unused
Table	64975867   	42332608 KB	34871376 KB	7455168 KB	6064 KB
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 11/07/2013 :  14:24:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
Does it have a clustered index? I'm thinking it's a heap and has tons of fragmentation. Add a clustered index and maintain the fragmentation via a maintenance job (if storage is a concern, otherwise leave it be).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

daq
Starting Member

4 Posts

Posted - 11/07/2013 :  17:34:31  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Does it have a clustered index?



Yes it does have a clustered index.
Space is an issue because of hourly backups which grow very quickly.

If I run defrag manually once, will it get rid of all blank space or does it need to be in a maintenance plan to keep the db size in check?
Does it make sense to just enable page compression on this table instead (or in combination with defrag?) We have plenty of available CPU on this server. Compression estimates size to shrink to 15GB or about 50% of current size.

Will defrag also speed up the DB or is space the only benefit?

Thank you!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 11/07/2013 :  18:00:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
Defragging really only helps with storage but not performance. While it can help with performance, it would be rare that it does.

Yes I would recommend compression if storage is that tight.

Whether or not defragging it will help with the storage issue is something you'll need to test. Whether or not you should maintain it regularly is something you'll need to decide. We do not maintain our indexes on our most critical system because we have SSDs and index maintenance wears out the disks faster. We don't have storage concerns, so there is no benefit to running index maintenance on that system.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000