SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Non clustered Index taking up more space than data


Non clustered Index taking up more space than data

Author
Message
TheComedian
TheComedian
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 510
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.
TheComedian
TheComedian
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 510
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
psingla
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 1249
is your table compressed?

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
TheComedian
TheComedian
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 510
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
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25506 Visits: 13701
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
weitzera
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 629
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
FTdenali
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1303 Visits: 572
Hi,
do you mean if his key is formed like a composite key?
Anyone with an update with regard to this?
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25506 Visits: 13701
yes, or on a very wide column.

no feedback from op yet

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search