SQL Server 2005: Index bigger than data stored

Question

I created 1 database with 2 file groups: 1 primary and 1 index.

• Primary file group includes 1 data file (*.mdf): store all tables
• Index file group includes 1 index file (*.ndf): store all indexes

Most of indexes are non-clustered indexes

After a short time using the database, the data file is 2GB but the index file is 12 GB. I do not know what problem happened in my database.

I have some questions:

1. How do I reduce the size of the index file?
2. How do I know what is stored in the index file?
3. How do I trace all impacts to the index file?
4. How do I limit size growing of index file?

Answer 1

How do I reduce size of index file ?

Drop some unneeded indexes or reduce the number of columns in existing ones. Remember that the clustered index column(s) is a "hidden" included column in all non clustered indexes.

If you have an index on a,b,c,d and an index on a,b,c you might consider dropping the second one as the first one covers the second one.

You may also be able to find potential unused indexes by looking at sys.dm_db_index_usage_stats

How to know what is stored in index file?

It will store whatever you defined it to store! The following query will help you tell which indexes are using the most space and for what reason (in row data, lob data)

SELECT  convert(char(8),object_name(i.object_id)) AS table_name, i.name AS index_name, 
    i.index_id, i.type_desc as index_type,
    partition_id, partition_number AS pnum,  rows, 
    allocation_unit_id AS au_id, a.type_desc as page_type_desc, total_pages AS pages
FROM sys.indexes i JOIN sys.partitions p  
      ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.allocation_units a
      ON p.partition_id = a.container_id
      order by pages desc

Answer 2

My guess (which I think is where marc_s is also headed) is that you've declared your clustered indexes for at least some of your tables to be on the index file group. The clustered index determines how (and where) the actual data for your table is stored.

Posting some of your code would certainly help others pinpoint the problem though.

I think that Martin Smith answered your other questions pretty well. I'll just add this... If you want to limit index sizes you need to evaluate your indexes. Don't add indexes just because you think that you might need them. Do testing with realistic (or ideally real-world) loads on the database to see which indexes will actually give you needed boosts to performance. Indexes have costs to them. In addition to the space cost which you're seeing, they also add to the overhead of inserts and updates, which have to keep the indexes in sync. Because of these costs, you should always have a good reason to add an index and you should consciously think about the trade-offs.

Answer 3

Consider that it is actually quite common for the total storage required for Indexes to be greater than the storage required for the table data within a given database.

Your particular scenario however would appear to quite excessive. As others have pointed out, if you have assigned the Clustered Index for a given table to reside in a separate data file (Your Index data file) then the entire physical table itself will reside in this file also, because in a manner of speak the Clustered Index is the table.

Providing details of your Table Schema and Index Structures will enable us to provide you with more specific guidance.

Other posters have mentioned that:

• You should review your index definitions for duplicate indexes. Take a look at Identifying Overlapping Indexes by Brent Ozar.
• You should look to identify unused indexes. Take a look as SQL Server Pedia Article: Finding Unused Indexes

Other avenues to explore include reviewing the fragmentation of your indexes, as this can increase the storage requirements.

Heavy fragmentation, particularly in the Clustered Index of a table containing LOB data, can result in a significant increase in storage needs. Reorganizing the Clustered Index on tables that contain LOB data will compact the LOB data.

See Reorganizing and Rebuilding Indexes