Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2010-03-17 : 01:11:01
|
| Hi ,Why is it so always the index size is doubled or more than the actual table data size?What could be reason for this ???I used sp_spaceused 'tname'.Thanks in Advance. |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2010-03-17 : 02:02:23
|
| column index_size, while querying sp_spaceused'tablename' is the size of all indexes for that table. Query it for tables that have only one clustered index and also for tables which also have multiple non clustered indexes and you will find that the value for index_size is never always double the size of table data size. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 05:25:58
|
| Fragmented indexes Frank? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-17 : 10:09:41
|
| Also realize that a non-clustered index is simply a "mini-table", clustered on the index key, containing all of the data in the columns used for that index.So if you have multiple indexes, they can quickly outgrow the size of the base table.And as Kristen said, check the fragmentation |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 11:47:00
|
| Following on from what Russell said:Secondary index entries will include the PK [columns]. Maybe your table has a large number of key fields and/or very wide key field(s) in the Clustered Index? |
|
|
|
|
|
|
Index size