Storing data non-contiguously on disk is known as fragmentation. Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation. When records are stored non-contiguously inside the page, then it is called internal fragmentation. When on disk, the physical storage of pages and extents is not contiguous. We can get both types of fragmentation using the DMV: sys.dm_db_index_physical_stats. The video on cleaning up SQL Server indexes also covers fillfactor.
Here is the generic advice for reducing the fragmentation. If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEX REORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running. If avg_fragmentation_in_percent > 30%, then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. In such case, we can also use the drop and re-create index method.(Ref: MSDN)
Here is the link to the SQL Server Performance: Indexing Basics.
Here is the additional reading material on the same subject:
SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation
SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table
SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
3 Comments. Leave new
Thanks Sir,It is good know for performance tuning.
Hi Pinal,
Your blog has been very helpful to me.
I am having a very serious issue with my MSSQL Server database. I am using MS SQL 2008 R2
My database is taking up 1.5 TB of space.
I am giving you details of 1 of the 20 tables that I have in my database.
Table_Name
ONTIME_DELIVERY_HISTORY_F
rows
149348218
reserved_KB data_ KB index_size_KB unused_KB
286507304 109628760 176843160 35384
The indes size is seems to be too high. I have used following command for reducing the index size.
ALTER INDEX ALL
ON ONTIME_DELIVERY_HISTORY_F
REBUILD WITH (DATA_COMPRESSION = ROW)
I have observed that the index size reduces by using this command, but the dsk space remains the same.
It would be great help if you could guide me and let me know what steps should be followed to reduce the index size and ultimately the reduce the disk space.
Hi guys,
Great quick steps on the rebuild.
We have a very interesting situation. We have a large table in our main DB that only gets updated once a month. Right after the dataload we run the rebuild index process for the table and everything seems good.
Then the users start running SPs that heavily read the data from that table and the index starts being fragmented again, all the way to 99.9%. Is that something ‘normal’? I always thought fragmentation occurred only when inserting and editing data.
Thanks