Non clustered Index taking up more space than data

Question

Post reply

Non clustered Index taking up more space than data

TheComedian

SSCrazy

Points: 2932
More actions
May 27, 2014 at 6:18 am

#291728

Hi all,
I've tried adding a non-clustered index to some tables and while it has improved performance significantly, the index takes up 5-10 times more space than the data in the table. It's a rather simple index:
CREATE NONCLUSTERED INDEX [Product_ArrivalID]
ON [dbo].[Product] ([ArrivalID])
INCLUDE ([Name],[Category],[Data],[Date])
It only has one column and four include columns and yet it's taking up so much space. What is the best practice in this type of scenario? The problem is that I need one such index on every table in order to speed up an ETL load that queries staging tables.

Viewing 8 posts - 1 through 7 (of 7 total)

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

TheComedian SSCrazy Points: 2932 More actions · Answer 1

I've just tried it without the include columns:

CREATE NONCLUSTERED INDEX [Product_ArrivalID]

ON [dbo].[Product] ([ArrivalID])

and the index takes up 50 % of the space that the data does but it still seems excessive. By the way ArrivalID is an int type and the include columns are all nvarchars except for Date which is datetime.

psingla Hall of Fame Points: 3840 More actions · Answer 2

is your table compressed?

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

TheComedian SSCrazy Points: 2932 More actions · Answer 3

Actually my table was compressed but even if I compress my index (both the table and index have page type compression), the index (with the include columns) still takes up almost the same amount of storage as the data itself. I mean is there any solution to this problem or do I have to live with the status quo?

george sibbald SSC Guru Points: 104210 More actions · Answer 4

whats the fill factor on the index?

remember the non-clustered index will also contain either an 8byte RID or the clustered index per row at the leaf level.

---------------------------------------------------------------------

weitzera Hall of Fame Points: 3442 More actions · Answer 5

To expand on what george said, does your primary key for the table include a lot of columns? The entire key for the table will be included in the index.

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

FTdenali SSCertifiable Points: 5362 More actions · Answer 6

Hi,

do you mean if his key is formed like a composite key?

Anyone with an update with regard to this?

george sibbald SSC Guru Points: 104210 More actions · Answer 7

yes, or on a very wide column.

no feedback from op yet

---------------------------------------------------------------------