| Author |
Topic |
|
daq
Starting Member
4 Posts |
 Posted - 2013-11-06 : 15:12:36
|
| 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 = ~3GBsp_spaceused is reporting that table size is 34695824 KB = ~33GB and index is another ~7GB.What am I missing? |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2013-11-07 : 01:33:32
|
| fill factor?cluster index /heap table?Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
 |
|
|
daq
Starting Member
4 Posts |
Posted - 2013-11-07 : 04:13:28
|
| Aren't those reported by sp_spaceused?Index is reported at 7GB. Still doesn't add up to 33GB. |
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2013-11-07 : 06:40:04
|
sp_spaceused [[ @objname = ] 'objname' ] [,[ @updateusage = ] 'updateusage' ]from http://technet.microsoft.com/en-us/library/ms188776.aspxtry 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 mutsabinWeb |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-07 : 12:29:11
|
| Show us the output of sp_spaceused for the table.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
daq
Starting Member
4 Posts |
Posted - 2013-11-07 : 14:15:55
|
quote:
Originally posted by tkizer
Show us the output of sp_spaceused for the table.
name rows reserved data index_size unusedTable 64975867 42332608 KB 34871376 KB 7455168 KB 6064 KB |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-07 : 14:24:53
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
daq
Starting Member
4 Posts |
Posted - 2013-11-07 : 17:34:31
|
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! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-07 : 18:00:17
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Table size vs disk space