SQL SERVER – Cleaning Up SQL Server Indexes – Defragmentation, Fillfactor – Video

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.

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 quick video which covers many of the above mentioned topics.

While Vinod and I were planning about Indexing course, we had plenty of fun and learning. We often recording few of our statement and just left it aside. Afterwords we thought it will be really funny Here is funny video shot by Vinod and Myself on the same subject:

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

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

2 thoughts on “SQL SERVER – Cleaning Up SQL Server Indexes – Defragmentation, Fillfactor – Video

  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s