Index Size greater than Data size?

_{Thursday, August 23, 2012 1:53 AM}

Hi All,

I have found several tables in my application where the overall table size is say, 20GB. Looking at it in finer detail, the data pages are 2GB and the index pages are 18GB!

Now i think that's really odd. I can't think of a reason where the index pages would be or need to be greater than the data itself. I have to think that the index pages would be smaller than the data pages otherwise you might as well do a full table scan.

I have found duplicate and some overlapping indexes...but can anyone think of a reason why a table would have size of index pages > data pages and be ok?

Thanks in advance

_{Thursday, August 23, 2012 2:37 AM ✅Answered}

 

• What kind of columns are you indexing?

For example, on a table of 12 million rows, 10 columns - i have indexes such as the following (in total, there are 9 indexes..):

Index1 - colA, colB

Index2 - colA, colB INCLUDE colc

Index3 - colD INCLUDE colE, colF, colG

Index4 - colD INCLUDE colE, colF, colH

Index5 - colD INCLUDE colF, colE, colG, colI

Now i could say Index1 is redundant, in comparison to Index2

I could also say that if i created an index, say Index6 - colD INCLUDE colE, colF, colG, colH, colI - then i may be able to get rid of indexes #3,4,5 since this new one will cover them all.

By doing those two steps, i would think i could reduce the size of the index pages significantly....thoughts?

As far as approximate space used, this does not help; you need to include the data types including the length specifications for these columns.  You also have not included which index is clustered, which if any of the indexes are unique and which is the primary key.

"Now i could say Index1 is redundant, in comparison to Index2 ..." It looks that way to me; however, beware if an index is the primary key or if colC is very large

"I could also say that if i created an index, say Index6 - colD INCLUDE colE, colF, colG, colH, colI - then i may be able to get rid of indexes #3,4,5 since this new one will cover them all."  It looks that way to me also; what to look out for here is if G, H or I are very large.  Keep in mind that that most likely index3-5 were created to optimize a specific query or set of queries.  You will need to re-test whatever queries these indices were created to optimize if you combine the queries.  Adding a large column to a small index will have an impact on the performance of a query that was using a smaller cover index.  If a query is super-critical, you combine indexes to save space and make the "super-critical" run noticeably slower, you might have some explaining to do.

It is for this reason that you want to document reasons when you create cover indexes. 

"By doing those two steps, i would think i could reduce the size of the index pages significantly....thoughts?"  I think you are on the right track

_{Thursday, August 23, 2012 2:02 AM}

That is possible if you have enough cover indexes or if you have indexes based on large binary or character columns.

• How many indexes do you have?
• How much space does each index use?
• What do the largest two indexes look like?
• What kind of columns are you indexing?

_{Thursday, August 23, 2012 2:07 AM}

That is possible if you have enough cover indexes or if you have indexes based on large binary or character columns.

• How many indexes do you have?
• How much space does each index use?
• What do the largest two indexes look like?
• What kind of columns are you indexing?

For example, on a table of 12 million rows, 10 columns - i have indexes such as the following (in total, there are 9 indexes..):

Index1 - colA, colB

Index2 - colA, colB INCLUDE colc

Index3 - colD INCLUDE colE, colF, colG

Index4 - colD INCLUDE colE, colF, colH

Index5 - colD INCLUDE colF, colE, colG, colI

Now i could say Index1 is redundant, in comparison to Index2

I could also say that if i created an index, say Index6 - colD INCLUDE colE, colF, colG, colH, colI - then i may be able to get rid of indexes #3,4,5 since this new one will cover them all.

By doing those two steps, i would think i could reduce the size of the index pages significantly....thoughts?

_{Thursday, August 23, 2012 2:20 AM}

Hi,

The size of an index depends on the size of the columns and the number of rows in an index. For example, if you have an indexed column which is int (4 bytes) on a table with 100 rows the index volume would be approximately 400 bytes.

Please read http://msdn.microsoft.com/en-us/library/ms178085%28v=sql.105%29.aspxand http://msdn.microsoft.com/en-us/library/ms190620%28v=sql.105%29.aspxto know how you can estimate clustered or non-clustered index size.

Here is another good article about the situation: http://www.simple-talk.com/sql/database-administration/managing-data-growth-in-sql-server/

To solve the problem you can use ALTER INDEX to rebuild indexes. Read more here: http://msdn.microsoft.com/en-us/library/ms188388.aspx

Cheers

Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP

_{Thursday, August 23, 2012 2:43 AM}

 

• What kind of columns are you indexing?

As far as approximate space used, this does not help; you need to include the data types including the length specifications for these columns.  You also have not included which index is clustered, which if any of the indexes are unique and which is the primary key.

Even worse, this table has no clustered index. They are all non-clustered, none defined as unique.

I will need to investigate the kind of columns in the index (small or not).

"Adding a large column to a small index will have an impact on the query that was using the smaller cover index.  If a query is super-critical, you combine indexes to save space and make the "super-critical" run noticeably slower, you might have some explaining to do."

--> Great point. I can see if the "super-critical" query was an INSERT/UPDATE, then adding a large column to the smaller covering index will affect the performance. However if the super-critical query is simply a SELECT, would adding a large column to the smaller covering index hurt?

Once again, thx so much so far, all good information i am going to use tomorrow back in the office..

_{Thursday, August 23, 2012 2:48 AM}

--> Great point. I can see if the "super-critical" query was an INSERT/UPDATE, then adding a large column to the smaller covering index will affect the performance. However if the super-critical query is simply a SELECT, would adding a large column to the smaller covering index hurt?

This is why we take measurements.  Measure the performance before the change and measure the performance after the change.  Present the difference based on the change and evaluate as a team whether or not change is acceptable.