SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sergio Govoni / Index Fragmentation / Latest Posts

RE: Index Fragmentation

Mark D Powell — Fri, 21 Oct 2011 14:17:03 GMT

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

RE: Index Fragmentation

Paul Randal — Fri, 21 Oct 2011 05:01:57 GMT

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.

RE: Index Fragmentation

alexander.oechsle — Fri, 21 Oct 2011 04:39:00 GMT

Hello,Do you really need UPDATE STATISTICS when executing REBUILD?I thought that this will be executed automatically by the rebuild.Best regards,Alex

RE: Index Fragmentation

sgovoni — Fri, 01 Aug 2008 01:15:37 GMT

Hello wthigo,before executing the stored procedures you've connected to the database ?For example:[font="Comic Sans MS"]USE TestDBEXEC USP_ExecReorgRebuildIndex 'TestDB', 0, -1, 10[/font]ByeSergio

RE: Index Fragmentation

Mark-82375 — Thu, 31 Jul 2008 14:40:07 GMT

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?

RE: Index Fragmentation

sgovoni — Fri, 25 Jul 2008 00:50:34 GMT

OK, and happy holidays :) .Sergio

RE: Index Fragmentation

Dirk.Hondong — Fri, 25 Jul 2008 00:10:25 GMT

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 regardsDirk

RE: Index Fragmentation

sgovoni — Thu, 24 Jul 2008 09:03:34 GMT

Hi Dirk,which version of SQL Server are you using ?[url=http://www.sqlservercentral.com/scripts/SQL+Server+2005/63287/]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 [url=http://www.ugiss.org/Content/Article/Deframmentazione-indici.aspx]this script[/url] (for the moment in Italian language, also released a brief translation in English).Thanks.Sergio

RE: Index Fragmentation

Dirk.Hondong — Thu, 24 Jul 2008 03:41:27 GMT

Hi,thanks for the reply.I just took the code from here, as it is.http://www.sqlservercentral.com/scripts/SQL+Server+2005/63287/

RE: Index Fragmentation

Abhijit More — Thu, 24 Jul 2008 03:30:39 GMT

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

RE: Index Fragmentation

Dirk.Hondong — Thu, 24 Jul 2008 03:22:12 GMT

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 48Line 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

Index Fragmentation

sgovoni — Fri, 30 May 2008 15:32:51 GMT

Comments posted to this topic are about the item [B]Index Fragmentation[/B]