index size

[lardum]

i have a database which is around 20Gb of size. When i run sp_spaceused i get these values:

reserved data index_size unused
20949656 KB 3139024 KB 16369480 KB 1441152 KB

howcome my index size is that huge?

 

[mrdenny]

Depending on the number of indexes and the columns used in the indexes, and the white space reserved within the indexes indexes can take more space than a table.

Say you have a table like this.

CREATE TABLE T1
(C1 int,
C2 int,
C3 varchar(10))

Now if you create 3 indexes on this table like this.

CREATE INDEX T1_I1 ON T1 (C1)
GO
CREATE INDEX T1_I2 ON T1 (C2, C3)
GO
CREATE IDNEX T1_I3 ON T1 (C1, C3)
GO

Your indexes will now take up more space than your data does.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[lardum]

is there a way to view indexes and their sizes in SQL 7?

 

[mrdenny]

All the index information is stored in the sysindexes system table.

The sp_spaceused procedure might be able to help you out.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com