Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Non clustered Index taking up more space than data Expand / Collapse
Author
Message
Posted Tuesday, May 27, 2014 6:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 4:09 AM
Points: 33, Visits: 242
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.
Post #1574747
Posted Tuesday, May 27, 2014 6:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 4:09 AM
Points: 33, Visits: 242
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.
Post #1574752
Posted Tuesday, May 27, 2014 6:33 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:46 AM
Points: 608, Visits: 1,056
is your table compressed?

Pramod
SQL Server DBA | MCSA SQL Server 2012

in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Post #1574755
Posted Tuesday, May 27, 2014 6:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 4:09 AM
Points: 33, Visits: 242
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?
Post #1574765
Posted Tuesday, May 27, 2014 7:13 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:57 AM
Points: 5,888, Visits: 13,062
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.


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

Post #1574770
Posted Wednesday, May 28, 2014 12:04 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:26 PM
Points: 880, Visits: 608
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.'
Post #1575304
Posted Tuesday, June 3, 2014 7:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:18 PM
Points: 181, Visits: 531
Hi,
do you mean if his key is formed like a composite key?
Anyone with an update with regard to this?
Post #1576961
Posted Tuesday, June 3, 2014 7:57 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:57 AM
Points: 5,888, Visits: 13,062
yes, or on a very wide column.

no feedback from op yet


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

Post #1576986
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse