Non clustered Index taking up more space than data

  • 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.

  • 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.

  • is your table compressed?

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • 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?

  • 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.

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

  • 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.'

  • Hi,

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

    Anyone with an update with regard to this?

  • yes, or on a very wide column.

    no feedback from op yet

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

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

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