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
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 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
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 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
SSC Eights!
SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)SSC Eights! (882 reputation)

Group: General Forum Members
Points: 882 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
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10326 Visits: 13687
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,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 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
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10326 Visits: 13687
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