Post reply

Index Fragmentation

  • May 30, 2008 at 3:32 pm

    #191288

    Comments posted to this topic are about the item Index Fragmentation

  • July 24, 2008 at 3:22 am

    #847593

    Hi all,

    I´ve tried to create the procedure but I got an error during the parsing process.

    Msg 170, Level 15, State 1, Procedure USP_ExecReorgRebuildIndex, Line 48

    Line 48: Incorrect syntax near '('.

    I have no idea where the error can be, maybe I´m blind.

    If someone has an idea, please give me a hint.

    Best regards,

    Dirk

    /EDIT

    Forget it, it was all my fault. I´ve connected to the wrong database engine and parsed the command against SQL 2000

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • July 24, 2008 at 3:30 am

    #847596

    Can you post the sample code. so dat it will be more easy to tell you the error.

    the error is because of braces u are using. R u creating the dynamic SP or simple SP

    Abhijit - http://abhijitmore.wordpress.com

  • July 24, 2008 at 3:41 am

    #847601

    Hi,

    thanks for the reply.

    I just took the code from here, as it is.

    http://www.sqlservercentral.com/scripts/SQL+Server+2005/63287/

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • July 24, 2008 at 9:03 am

    #847841

    Hi Dirk,

    which version of SQL Server are you using ?

    This script[/url] uses the DMV sys.dm_db_index_physical_stats usable only by the 2005 version of SQL Server.

    To defragment indexes with SQL Server 2000 you can use this script (for the moment in Italian language, also released a brief translation in English).

    Thanks.

    Sergio

  • July 25, 2008 at 12:10 am

    #848221

    Hello Sergio,

    thanks for the reply.

    In our company environment we´re using SQL 2000 and 2005 servers.

    So both scripts will be useful.

    I think, I will try both after my 2 week holidays. 🙂

    Best regards

    Dirk

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • July 25, 2008 at 12:50 am

    #848233

    OK, and happy holidays 🙂 .

    Sergio

  • July 31, 2008 at 2:40 pm

    #851543

    I've created this sp but when I run it with the print option I get no returns. I've already separately identified an index or 2 that are fragmented past 20% but don't show up, even though I've set my threshold at 10. Any ideas?

  • August 1, 2008 at 1:15 am

    #851672

    Hello wthigo,

    before executing the stored procedures you've connected to the database ?

    For example:

    [font="Comic Sans MS"]USE TestDB

    EXEC USP_ExecReorgRebuildIndex 'TestDB', 0, -1, 10[/font]

    Bye

    Sergio

  • October 21, 2011 at 4:39 am

    #1397842

    Hello,

    Do you really need UPDATE STATISTICS when executing REBUILD?

    I thought that this will be executed automatically by the rebuild.

    Best regards,

    Alex

  • October 21, 2011 at 5:01 am

    #1397854

    You've fallen into the classic trap of not filtering by allocation unit type - any LOB_DATA or ROW_OVERFLOW_DATA allocation units with fragmentation will trigger index rebuilds/reorganizes even if the IN_ROW_DATA allocation unit has no fragmentation. You need to update the script to handle that.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • October 21, 2011 at 2:17 pm

    #1398232

    I also think you can skip the update statistics after a rebuild based on BOL topic Using Statistics to Improve Query Performance, "Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC REINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. The query optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however; this statistics update is a byproduct of re-creating the index. The query optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations. "

    Typical MS. First we are told you do not need to update the statistics after a rebuild since the data distribution has not changed but then we are told the rebuild causes the statistics to be updated.

    HTH -- Mark D Powell --

  • May 12, 2016 at 6:57 am

    #1878542

    Thanks for the script.

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

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