Post reply

Index Fragmentation...

  • July 21, 2009 at 12:44 pm

    #134451

    Hi,

    I am new to SQL Server. In DEV only, but I think the issue is for administration so putting it here.

    We had some client facing issue with slow SQL Sevrer DB, and after a while they confirmed that the issue was with their env where their indexes (for perticular table) were fragmented heavily [95-99%]

    They rebuild all indexed and it resolved the performance issue.

    Would you please confirm whats the way to find out a Fragmentation for a perticular table.

    And how to rebuild all indexes?

    This is my first question on site... So apologies if this is not the correct place to put such questions.

    Thanks,

    Jaimin Soni

  • July 21, 2009 at 12:46 pm

    #1027857

    You could use SQL Profiler to generate a trace file and use that within the Database Index Tuning Wizard.

    Not sure if this is exactly what you are looking for but hopefully points you to the right direction.

    Index Tuning tips article

    --
    :hehe:

  • July 21, 2009 at 12:55 pm

    #1027861

    Here is an article about index fragmentation

    http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx

    And here is the script that I am currently using to defrag/rebuild all indexes.

    http://blogs.digineer.com/blogs/larar/archive/2007/07/30/smart-index-defragmentation-for-an-online-world.aspx

    There are several others available as well.

  • July 21, 2009 at 5:48 pm

    #1028031

    Kill two birds with one stone... lookup "DBCC SHOWCONTIG" in Books Online and read all about it. Then, find section "E." in that same BOL article to see the code that will do this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 22, 2009 at 11:33 am

    #1028547

    Ken Simmons (7/21/2009)

    Here is an article about index fragmentation

    http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx

    And here is the script that I am currently using to defrag/rebuild all indexes.

    http://blogs.digineer.com/blogs/larar/archive/2007/07/30/smart-index-defragmentation-for-an-online-world.aspx

    There are several others available as well.

    Ken,

    I tried your second link and it took me to a login page.

  • July 22, 2009 at 11:46 am

    #1028559

    nathan.holtrop (7/22/2009)

    Ken Simmons (7/21/2009)

    Here is an article about index fragmentation

    http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx

    And here is the script that I am currently using to defrag/rebuild all indexes.

    http://blogs.digineer.com/blogs/larar/archive/2007/07/30/smart-index-defragmentation-for-an-online-world.aspx

    There are several others available as well.

    Ken,

    I tried your second link and it took me to a login page.

    Check out the below article. It has some great useful information and somewhere I pointed the original OP to.

    Thanks,

    S

    http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx

    --
    :hehe:

  • July 22, 2009 at 12:00 pm

    #1028570

    Hi Jaimin,

    AFAIK, the index has to be reorganized if the the amount of fragmentation is less than 30% and the index has to be rebuilt if the the amount of fragmentation is greater than 30% .

    To find the amount of fragmentation, you can use the DMV

    sys.dm_db_index_physical_stats

    Thank You,

    Best Regards,

    SQLBuddy.

  • July 22, 2009 at 12:33 pm

    #1028599

    sqlbuddy123 (7/22/2009)

    Hi Jaimin,

    AFAIK, the index has to be reorganized if the the amount of fragmentation is less than 30% and the index has to be rebuilt if the the amount of fragmentation is greater than 30% .

    To find the amount of fragmentation, you can use the DMV

    sys.dm_db_index_physical_stats

    Thank You,

    Best Regards,

    SQLBuddy.

    Jaimin,

    Some people will tell you 30% others will tell you 60%. Whatever you do make you do your rebuilds after hours when no one is using the system. Rebuild drops and recreates the indexes whereas reorg keeps them in place.

    Nate

  • July 22, 2009 at 2:13 pm

    #1028662

    Here is an article about index fragmentation

    http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx

    You can use DBCC CHECKTABLE command to check index of that tables...

    Here is an script to reindex all tables in Database

    Script to automatically reindex all tables in a database

    USE DatabaseName --Enter the name of the database you want to reindex

    DECLARE @TableName varchar(255)

    DECLARE TableCursor CURSOR FOR

    SELECT table_name FROM information_schema.tables

    WHERE table_type = 'base table'

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @TableName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    DBCC DBREINDEX(@TableName,' ',90)

    FETCH NEXT FROM TableCursor INTO @TableName

    END

    CLOSE TableCursor

    DEALLOCATE TableCursor

  • July 22, 2009 at 10:31 pm

    #1028758

    Why not just use DBCC SHOWCONTIG and save it in a table?

    Also, most folks might be able to live with 30% fragmentation, but I can't... the tables I work with have millions and tens of millions of rows. 30% fragmentation on a million row table basically means that I'll have more "out of place rows" than most people have in a table. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 23, 2009 at 1:38 am

    #1028785

    As Jeff already told, using DBCC SHOWCONTIG can give you a good idea about the status of fragmentation.

    You can also use the "WITH TABLERESULTS" and "ALL_INDEXES" parameters.

    DBCC SHOWCONTIG('TABLE') WITH TABLERESULTS, ALL_IDEXES

    The "with tableresults" will give you some more details that can help you.

    But, before conclude anything about the results that you will gave, it's a good idea to check about the index, if they are clustered or not.

    Regards,

    Shin-Iti.

  • July 23, 2009 at 5:18 pm

    #1029376

    Hi,

    For your information only

    Microsoft discourages the use of DBCC SHOWCONTIG . This is the official statement from the Microsoft in BOL 2008

    "This feature will be removed in the next version of Microsoft SQL Server . Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_db_index_physical_stats instead."

    http://msdn.microsoft.com/en-us/library/ms175008.aspx

    So refrain from using DBCC SHOWCONTIG.If you have very large table you needdn't use 30%. You can use a lower value.

    Thank You,

    Yours

    SQLBuddy.

  • July 23, 2009 at 5:40 pm

    #1029383

    sqlbuddy123 (7/23/2009)

    Hi,

    For your information only

    Microsoft discourages the use of DBCC SHOWCONTIG . This is the official statement from the Microsoft in BOL 2008

    "This feature will be removed in the next version of Microsoft SQL Server . Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_db_index_physical_stats instead."

    http://msdn.microsoft.com/en-us/library/ms175008.aspx

    So refrain from using DBCC SHOWCONTIG.If you have very large table you needdn't use 30%. You can use a lower value.

    Thank You,

    Yours

    SQLBuddy.

    That's nice... I wish Microsoft would stop deprecating stuff... folks stuck in the 2k world would sure appreciate it.

    On the 30% stuff on a very large table... heck, I know I can use a lower value and do. I just wanted folks to know that the 30% recommendation certainly isn't for everyone. In fact, I'd make the recommendation that the 30% recommndation is way too high for most folks. THAT was the point I was really trying to make and obviously fell a bit short on.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply