index size qn

Question

Post reply

index size qn

aruram

SSCrazy

Points: 2379
More actions
April 22, 2008 at 9:37 pm

#121156

Hi,
One of our production tables shows a much bigger size for index than the data.
(The table was recently reindexed.)
1) Why is this difference?
2) Is the index size accurate ?
3) If not, is there a way to fix this ?
Thanks
Data size -12070072 KB
Index size -52430104 KB
Table structure:
[testPlanName] [nvarchar](90) NOT NULL,
[testId] [nvarchar](60) NOT NULL,
[subGroup] [nvarchar](25) NOT NULL,
[detail] [char](20) NOT NULL,
[priority] [int] NULL,
[tester] [nvarchar](50) NULL,
[lstUpdUser] [nvarchar](50) NOT NULL,
[lstUpdDate] [datetime] NOT NULL,
CONSTRAINT [PK_TEST_CASE_LINK] PRIMARY KEY CLUSTERED
([testPlanName] ASC,
[testId] ASC,
[subGroup] ASC,
[detail] ASC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It might be. How many other indexes do you have on the table?

Run this and see what it regarding page count for the clustered index (index_id 1) and the other .

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID('<Table Name>') ,NULL,NULL, DEFAULT )

As an aside, a composite index of multiple large nvarchars and chars is not a good candidate for the clustered index. Ideally, a clustered index key should be narrow. Yours is anything from 26 to 376 bytes in size. This will greatly increase the size of any nonclustered index, since the clustering key is used as the row identifier.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aruram SSCrazy Points: 2379 More actions · Answer 2

Here are the page_count values:

CLUSTERED INDEX 1487953

NONCLUSTERED INDEX1187790

NONCLUSTERED INDEX1545382

NONCLUSTERED INDEX1042577

NONCLUSTERED INDEX1031366

NONCLUSTERED INDEX1037250

NONCLUSTERED INDEX1217052

This does not indicate the clustered index size to be 60G + as shownby the space_used.

I am still confused, why the index sizes are so large????

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's not what spaceused is saying.

Spaceused lists your data size (the clustered index) as 12 GB. The page count confirms that.

What spaceused lists as index size is the total space consumed by all of the nonclustered indexes you have.

From the page counts you gave, there's a total of 7061417 used by the 6 nonclustered indexes. That works out (at 8k per page) at 53.24 GB.

As for why the indexes are so large, without seeing their definitions and having an idea of the amount of data in the table, I can't sy. However, the very large clustering key is certainly a contributing factor.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aruram SSCrazy Points: 2379 More actions · Answer 4

Thanks!

That clears my confusion. I was also looking at the sysindexes output which put the size for data + NC indexes against the clustered key .. but now the numbers add up.

And yes, I am aware of the large clustered key contributing the increasing in space. That is something the application team is working on in redesigning the appl.

However, I have a qn:

Since we have a composite PK and clustered index on testPlanName, testId, subGroup, and detail cols

Is it really necessary to have individual non-clustered indexes on each of the following cols?

testId

lstUpdDate

tester

testPlanName

detail

subGroup

Would dropping them hurt query performance or will searches on the individual cols be able to use the clustered index to effectively get the data they need?

Thanks again

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You should be able to safely drop the index on testplan. Not the others.

An index is redundant if the columns in it are a left-based subset of another index.

If you want an easy way to visualise it, think of a telephone book as an index on surname, name, address

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass