CODEWITHSUNDEEP
sqlsql-serversql-server-2005
RPS
RPS

Reputation: 1401

SQL Server 2005: Index bigger than data stored

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

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?

Upvotes: 13

Views: 16005

Answers (4)

Andrei Sura
Andrei Sura

Reputation: 2604

@martin-smith's answer is almost what I needed...

Here is how you sort by index size in GB (mssql uses 8KB pages == 128 pages/MB)

SELECT
  object_name(p.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
  -- , allocation_unit_id AS au_id
  , rows
  , a.type_desc as page_type_desc
  , total_pages/(1024 * 128.0) AS sizeGB
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
    JOIN sys.all_objects ao ON (ao.object_id = i.object_id)
    WHERE ao.type_desc = 'USER_TABLE'
ORDER BY
    -- table_name 
    sizeGB DESC

Upvotes: 0

John Sansom
John Sansom

Reputation: 41889

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:

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

Upvotes: 5

Tom H
Tom H

Reputation: 47402

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.

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453807

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

Upvotes: 10

Related Questions