CODEWITHSUNDEEP
sql-serverindexingdatabase-performancesqlperformance
JOHN
JOHN

Reputation: 891

SQL Index Space Does Not Decrease After Deleting Data From Table

I recently try to clean a table. The table was 40GB large, the index space is 400MB. After delete a lot of records from the table, it decreased to 4GB, but the index space is still about 400MB. I thought it might because of the index is fragmented when I deleting the record, then I rebuild the index, but the index space is still 398MB. I do not understand, if the table size is decreased 10 times, why the index space doesn't decrease proportionally?

The build of index

ALTER TABLE [CADIS_PROC].[DC_UPPREBBO_INFO_VALUE] 
ADD PRIMARY KEY CLUSTERED ( 
[CADIS_ID] ASC, 
[SOURCE] ASC, 
[SOURCE_KEY] ASC, 
[FILE_DATE] ASC )WITH 
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
GO

Script to rebuild the index 

ALTER INDEX IndexName ON Schema.Table
REBUILD;
GO

How I checked index fragmentation, I also right right clicked on the table and see it's property, it shows the size of table and index space. 

SELECT index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(), NULL, NULL, NULL)

That index is the only index on the table, and it is 0.8 percent of fragmentation.

Upvotes: 1

Views: 6317

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

Clustered index is the table. The data of the table is physically stored in the order defined by the clustered index.

If the table has only clustered index and no non-clustered indexes I would expect the index space to be almost zero.

I have a table with just a clustered index. SSMS shows:

Data space  = 3,215.539 MB
Index space = 5.211 MB       (not zero, but pretty close)

Each night the system rebuilds indexes using this command:

ALTER INDEX ALL ON <table name> REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON )

Try it instead of specifying the index name.

To measure the disk space used by a table try to use the sp_spaceused. It has a parameter @updateusage, which may be useful.

Try to run this procedure like this:

EXEC sp_spaceused @objname = N'[dbo].[TableName]', @updateusage = N'TRUE'

MSDN article about this stored procedure has this note:

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available. For more information about deferred allocations, see Dropping and Rebuilding Large Objects.

Have a look at the related article Dropping and Rebuilding Large Objects as well.

Upvotes: 1

Ken Clement
Ken Clement

Reputation: 778

Realize that a clustered index is built with the actual data pages unlike a non-clustered index. It may be worth trying to drop and then recreate the index instead of using REBUILD to see what effect that has.

Quoted from MSDN here

Reducing Fragmentation in an Index

When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:

  1. Drop and re-create the clustered index.

Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. The drawbacks in this method are that the index is offline during the drop and re-create cycle, and that the operation is atomic. If the index creation is interrupted, the index is not re-created. For more information, see CREATE INDEX (Transact-SQL).

  1. Use ALTER INDEX REORGANIZE, the replacement for DBCC INDEXDEFRAG, to reorder the leaf level pages of the index in a logical order. Because this is an online operation, the index is available while the statement is running. The operation can also be interrupted without losing work already completed. The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation, and it does not update statistics.reorder the leaf level pages of the index in a logical order.

  2. Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. For more information, see ALTER INDEX (Transact-SQL).

You have already tried #3, if practical you may want to consider 1 or 2 to guage their effect. If you are only trying to understand the observed behavior, use a copy of the database (or at least the relevant tables) on a development/test server whose database can be rebuilt. Also you may want to experiment with a non-clustered index just for comparison.

Personally I've always been suspicious of any reporting tools do on index performance and space utilization with clustered indices as they must distinguish between the index and the data which share the same allocation space.

Upvotes: 0

Related Questions