SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

Index Fragmentation:
When a page of data fills to 100 percent and more data must be added to it, a page split occurs. To make room for the new data, SQL Server must move half of the data from the full page to a new page. The new page that is created is created after all the pages in database. Therefore, instead of going right from one page to the next when looking for data, SQL Server has to go one page to another page around the database looking for the next page it needs. This is Index Fragmentation. Severity of the Index fragmentation can be determined by querying sys.DM_DB_INDEX_PHYSICAL_STATS.

SQL SERVER 2000:
DBCC SHOWCONTIG was used to find index fragmentation. In SQL SERVER 2005 it is deprecated and replaced by query to sys.DM_DB_INDEX_PHYSICAL_STATS.

SQL SERVER 2005:
SELECT query to sys.DM_DB_INDEX_PHYSICAL_STATS displays all the Index Fragmentation related information.

Examples to determine Index Fragmentation in SQL SERVER 2005:
To return the Index Information for only Sales.SalesOrderDetail Table:
USE AdventureWorks;
SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'Sales.SalesOrderDetail'), NULL, NULL, 'DETAILED')

To return all the information for all the Indexes in Database:
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

Index De-Fragmentation and Index Rebuilding:
Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. I prefer Index Rebuilding is required if Index is fragmented more than 10%. Reorganizing an index de-fragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance.

SQL SERVER 2000:
The DBCC INDEXDEFRAG and DBCC DBREINDEX statements are used to reduce table fragmentation.

SQL SERVER 2005:
ALTER INDEX with the REBUILD clause. This statement replaces the DBCC DBREINDEX statement of SQL SERVER 2000. (DBCC DBREINDEX can still be used in SQL SERVER 2005 but will be sure deprecated in future version of SQL SERVER. I do not recommend to continue use of this in SQL SERVER 2005)
CREATE INDEX with the DROP_EXISTING clause.
Both the above method perform the same function.

Examples to rebuild Index in SQL SERVER 2005:
To Rebuild only one Index:
USE AdventureWorks
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID
ON Production.ProductPhoto
REORGANIZE
GO

TO Rebuild all the Indexes on Table with Specifying options:
USE AdventureWorks
GO
ALTER INDEX ALL ON Production.Product
REBUILD
WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO

I have been asked following two questions many times:
1) What is difference between FILLFACTOR = 0 AND FILLFACTOR = 100?
They are SAME.
2) What FILLFACTOR my own Database Servers have for Index? Why?
90. It works great for me.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

About these ads

33 thoughts on “SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

  1. Hi!

    could you please describe why index fragmentation job fail to record info about a database at a particular point of time(every week on same day).Its a SQL serever 7.0. when I reran the transaction it successfully execute.Didn’t get ne clue?

    please reply on my mail.

    Thanks

    Like

    • Hi Pinal,

      I think this is confusing please get it corrected from second line where it says rebuilding.. It conrtadicts

      Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. I prefer Index Rebuilding is required if Index is fragmented more than 10%. Reorganizing an index de-fragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes.

      Like

  2. Pingback: SQL SERVER - 2005 - Explanation and Script for Online Index Operations - Create, Rebuild, Drop Journey to SQL Authority with Pinal Dave

  3. After running
    ALTER INDEX ALL ON Production.Product
    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON)
    GO

    checking the AVG_FRAGMENTATION_IN_PERCENT and find it didn’t change at all. If using DBCC and find that the FRAGMENTATION is 0%. Could you help on why? Thanks

    Like

  4. Hi Dave

    How r u doing?

    I am Vijay from Hyderabad, working for satyam.

    Thanks for all the service you provide to our DBA Team.

    I need a help from you to resolve fragmentation issue on Full Text Index.

    I have a table which is in size of 30GB and having Full Text Index.

    Now I need to reindex the same. I need the steps how to perform the same.

    Appreciate if you can help in making me understand and resolve the problem.

    Thanks & Regards
    Vijay

    Like

  5. sir

    i have a frgmantation of 42 % on one table and i have re created index for that table using LTER INDEX ALL command . but still after recreating index i am getting a freagmantation of 42 %. what should i do to make it to 0 %

    Like

  6. MSSQL 2005

    ALTER INDEX ALL ON dbo.Mail_Messages
    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON)

    I executed the above statement but my fragmentation is still over 85%. I have used the rebuild and reorg in the maintenance plan also and I cannot get my fragmentation to reduce.

    Any suggestions?

    Thanks

    Like

  7. I’m a fan of your work.

    Whats the difference between rebuild and reorganize? Do they just run at different speeds or is there some other difference between them.

    Cheers,
    Aaron

    Like

  8. @ TODD

    Well it worked for me …

    I created a table with a primary key, which create a clustered index by default and then I checked the fragmentation level using the command given by Dave, I saw the level was 0 (Zero)

    Then I inserted 12000 rows in the table, then I saw the index fragmentation level was 20 % , and then I executed the second query,

    Instantly my index fragmentation level became 0 %.

    Worked for me … !

    Like

  9. Pinal, stupid question:

    The value returned for avg_fragmentation_in_percent from
    sys.dm_db_index_physical_stats is Percent or .%

    i.e. if .01 is returned is that .01% or 10% ?

    Thanks,
    Paul

    Like

  10. Hi Pinal,
    i am working on a banking project for a financial bank.it deals with end of day and start of day…i have created some indexes in it. i m getting some error while end of day of any branch.

    Error messages:
    The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active. shud i try to defragment it, how can i defragment it.plz give me a long term solution bcoz branches are going to increase like anything… do u have any contact no. its urgent

    Like

  11. I’m just wondering and frustrated about what is wrong with the code below. It executes perfectly on many DBs we have but fails on some throwing the error “Incorrect synatx near the keyword ‘with’.

    DECLARE @Database VARCHAR(100)
    DECLARE @Table VARCHAR(100)
    DECLARE @cmd NVARCHAR(300)
    DECLARE @fillfactor INT

    SET @fillfactor = 70
    SET @Database = ‘TestOnly’

    SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName
    FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’

    EXEC (@cmd)
    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @Table
    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
    EXEC (@cmd)

    FETCH NEXT FROM TableCursor INTO @Table
    END

    CLOSE TableCursor
    DEALLOCATE TableCursor

    Like

  12. Dave,

    After reading your post I wanted to point something out. You said:

    “I have been asked following two questions many times:
    1) What is difference between FILLFACTOR = 0 AND FILLFACTOR = 100?
    They are SAME.”

    I cannot speak on behalf on SQL 2005 but with SQL 2k this is not an entirely a true statement as there is a difference between the two.

    FILLFACTOR = 100 is 100% fill rate and is generally a bad idea.

    FILLFACTOR = 0 is a wildcard for whatever the existing fill factor on the index was set to previously.

    If the index previously had a FILLFACTOR of 100 then this statement would be true as the fill rate would be 100%.

    However if the index had a FILLFACTOR of 90 and a FILLFACTOR = 0 was used, the fill rate of 90% would be retained.

    I use a FILLFACTOR of 0 almost exclusively when reindexing a database, as FILLFACTOR’s on index should be individually tuned based on types of activity in that particular table. I would not want to change those values when doing a bulk reindex of a database.

    Like

  13. Pingback: Michael's personal blog

  14. Dear Pinal Dave

    i heared alot abt u. impressive….
    i am working in dubai. we are havinf problem related to Database Performance. Few days before, we upgraded the sql server. we moved our database from sql server 2000 to sql server 2005 by restoring the backup of SQL server 2000 on SQL Server 2005. we are having a web site which is using this Database. The database was working normally on sql server 200 but after we moved, its dead slow…i mean the over all performance of DB is very slow..we reindex the Database again in SQL Server 2005 but no use…its still same…

    Just wanna know, is there any setting in SQL Server 2005 after restoring database from SQL Server 2000???
    i mean can u help me which step i am missing ???

    hope to have your reply soon

    Regards

    BASHARIT

    Like

  15. Pinal Dave,

    What are your thoughts on this post from Kalen Delaney?

    http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx

    Excerpts:
    “Fragmentation is really only an issue if there are LOTS of pages. I usually say that if there are less than 100 pages, you should never worry about fragmentation. However, this this whitepaper suggests that a better cutoff would actually be 1000 pages: (link)” See orig post for actual link.

    “REBUILD doesn’t seem to make a difference in the avg_fragmentation_in_percent value is when there are too few pages in the table.”

    Thanks,
    WCM

    Like

  16. MODERATOR: HOW ARE YOU ABLE TO POST SCRIPT AND RETAIN COLORS AND INDENTATION?

    This is a the latest version of this script as of 2008-11-12:

    /*
    Script originally authored by Microsoft but had an error - It was retrieving data without prefixing database schema.

    -Added a @ViewOnly variable that will allow you to run this script as a test only and review proposed actions. Simply change the value to 0 to execute the proposed actions.
    -Also enhanced to perform either a reorg or rebuild, based on range of fragmentation value.
    -Also included pages value from DBCC SHOWCONTIG for analysis, to support theory that defragging indexes have few pages is not productive.
    ~Paul DeBrino : infinityrd.com : Nov 2008

    USEFUL INFO -- INSPECT INDEXES' FILL FACTOR VALUES:
    ============================================
    use YourDBName
    select * from sys.indexes order by name

    USEFUL INFO -- VIEW THE DEFAULT FILL FACTOR OF YOUR SERVER:
    ============================================
    use YourDBname
    exec sp_configure 'show advanced options', 1
    go
    reconfigure
    go
    exec sp_configure 'fill factor'
    go
    */

    -- Specify your Database Name:
    USE YourDBName

    -- Declare variables:
    SET NOCOUNT ON
    DECLARE @tablename VARCHAR(128)
    DECLARE @execstr VARCHAR(255)
    DECLARE @objectid INT
    DECLARE @indexid INT
    DECLARE @frag decimal
    DECLARE @maxreorg decimal
    DECLARE @maxrebuild decimal
    DECLARE @IdxName varchar(128)
    DECLARE @pages INT
    DECLARE @maxpages INT
    DECLARE @ViewOnly bit
    DECLARE @ReorgOptions varchar(255)
    DECLARE @RebuildOptions varchar(255)

    -- Set to 1 to view proposed actions, set to 0 to Execute proposed actions:
    SET @ViewOnly=1

    -- Decide on the maximum number of pages to consider index for reorg or rebuild:
    -- NOTE: Set to 0 if you wish not to consider this value for analysis.
    -- Reference http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx
    SET @maxpages = 100
    -- Decide on the maximum fragmentation to allow for a reorganize:
    -- AVAILABLE OPTIONS: http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx
    SET @maxreorg = 20.0
    SET @ReorgOptions = 'LOB_COMPACTION=ON'
    -- Decide on the maximum fragmentation to allow for a rebuild:
    SET @maxrebuild = 30.0
    -- NOTE: only specifiy FILLFACTOR=x if x is a value other than 0 (otherwise do not include the fillfactor option at all):
    SET @RebuildOptions = 'PAD_INDEX=OFF, FILLFACTOR=90, SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON'

    -- Declare a cursor to process list of tables:
    DECLARE tables CURSOR FOR
    SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
    +'.'+CAST(TABLE_NAME AS VARCHAR(100))
    AS Table_Name
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

    -- Create the temporary table:
    if exists (select name from tempdb.dbo.sysobjects where name like '#fraglist%')
    drop table #fraglist

    CREATE TABLE #fraglist (
    ObjectName CHAR(255),
    ObjectId INT,
    IndexName CHAR(255),
    IndexId INT,
    Lvl INT,
    CountPages INT,
    CountRows INT,
    MinRecSize INT,
    MaxRecSize INT,
    AvgRecSize INT,
    ForRecCount INT,
    Extents INT,
    ExtentSwitches INT,
    AvgFreeBytes INT,
    AvgPageDensity INT,
    ScanDensity decimal,
    BestCount INT,
    ActualCount INT,
    LogicalFrag decimal,
    ExtentFrag decimal)

    -- Open the cursor:
    OPEN tables

    -- Loop through all the tables in the database:
    FETCH NEXT
    FROM tables
    INTO @tablename

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Perform showcontig on all indexes for the current table:
    INSERT INTO #fraglist
    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
    FETCH NEXT
    FROM tables
    INTO @tablename
    END

    -- Close and deallocate the cursor:
    CLOSE tables
    DEALLOCATE tables

    -- Declare the cursor for the list of indexes to be defragged:
    DECLARE indexes CURSOR FOR
    SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName, CountPages
    FROM #fraglist
    --WHERE ((LogicalFrag >= @maxreorg) OR (LogicalFrag >= @maxrebuild))
    WHERE INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

    -- Open the cursor:
    OPEN indexes

    -- Loop through the indexes:
    FETCH NEXT
    FROM indexes
    INTO @tablename, @objectid, @indexid, @frag, @IdxName, @pages

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Determine if index page count surpasses the defined threshold for needed maintenance:
    IF (@pages >= @maxpages)
    BEGIN
    -- Determine if fragmentation surpasses the defined threshold for rebuilding:
    IF (@frag >= @maxrebuild)
    BEGIN
    IF (@ViewOnly=1)
    BEGIN
    PRINT 'WOULD be executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% and Pages were ' + RTRIM(CONVERT(VARCHAR(15),@pages))
    END
    ELSE
    BEGIN
    PRINT 'Now executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% and Pages were ' + RTRIM(CONVERT(VARCHAR(15),@pages))
    SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )'
    EXEC (@execstr)
    END
    END
    -- Determine if fragmentation surpasses the defined threshold for reorganizing:
    ELSE IF (@frag >= @maxreorg)
    BEGIN
    IF (@ViewOnly=1)
    BEGIN
    PRINT 'WOULD be executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% and Pages were ' + RTRIM(CONVERT(VARCHAR(15),@pages))
    END
    ELSE
    BEGIN
    PRINT 'Now executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' ) -- Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% and Pages were ' + RTRIM(CONVERT(VARCHAR(15),@pages))
    SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )'
    EXEC (@execstr)
    END
    END
    ELSE
    BEGIN
    PRINT '--BYPASSING INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' since fragmentation is currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '% which is less than the Reorg and Rebuild thresholds.'
    END
    END
    ELSE
    BEGIN
    PRINT '--BYPASSING INDEX ' + RTRIM(@IdxName) + ' ON ' + RTRIM(@tablename) + ' since Page Count was ' + RTRIM(CONVERT(VARCHAR(15),@pages)) + ' which is less than threshold. Fragmentation is currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
    END

    FETCH NEXT
    FROM indexes
    INTO @tablename, @objectid, @indexid, @frag, @IdxName, @pages
    END

    -- Close and deallocate the cursor:
    CLOSE indexes
    DEALLOCATE indexes

    -- Delete the temporary table:
    DROP TABLE #fraglist
    GO

    Like

  17. Dear Pinal Dave,

    I am facing a problem while rebuilding or reorganising a few of my DB inedexes. My Environment is SQL Server 2005 and I am using “ALTER INDEX ….” command.
    These indexes remain “Fragmented” even aftre rebuilding or reorganising severl time.
    Can you please tell me what could be the reasons?

    With Regards,

    M Azim

    Like

  18. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  19. Dear Dave,

    I have huge database around 2 tb all most of the tables are partioned.

    1. Rebuilding an index at partition level is better or the whole table.

    2. If I have to rebuild all partition

    is it better by giving partition # like below

    ALTER INDEX IX_TransactionHistory_TransactionDate
    ON Production.TransactionHistory
    REBUILD Partition = 5;
    GO

    or

    ALTER INDEX IX_TransactionHistory_TransactionDate
    ON Production.TransactionHistory
    PARTITION = ALL

    Is the second option is same as rebuild index on the whole table or it goes one partition at a time.

    Like

  20. Pingback: SQL SERVER – Non-Clustered Index and Automatic Rebuild – Quiz – Puzzle – 11 of 31 « SQL Server Journey with SQL Authority

  21. Hi,

    I am using SQL Server 2005 and index frag is 66.67% all the time. I tired drop/create and reorganize and rebuild the indexes. but still the total fragmentation is 66.67%. How to reduce it.

    Thanks!

    Amit Kumar

    Like

  22. Pingback: SQL SERVER – Correct Value for Fillfactor – Quiz – Puzzle – 24 of 31 « SQL Server Journey with SQL Authority

  23. Hi sir

    i have read one your article What is the best value for the Fill Factor? – Index, Fill Factor and Performance – Part 2
    where you have explained one approx fill factor criteria. I have a query regarding cluster index. I have a table where my cluster index is on a unique column but all other columns in this table are updated through out the process.(Appx 15-20 times or more a day) Should I keep fill factor to 100 as per the unique index or should i make it somewhere in between 70-90 as data is contineously updating in this table.

    Basicly I want to know what we need to do with Fill factor if my unique queue where we have the clustered index is not changing but all the rest columns are updating contioneously.

    Like

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

  25. Pingback: SQL SERVER – Change Fill Factor – SQL in Sixty Seconds #069 | Journey to SQL Authority with Pinal Dave

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