July 14, 2008 at 12:10 am
Hello All,
In one of my Production server, fragmentation of some indexes is as high as 98% with pages more than 18000. Is there any way I can defragment these indexes without blocking the online operation.
Thanks
July 14, 2008 at 3:59 am
BOL:
ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.
July 14, 2008 at 4:08 am
If you're using SQL 2005 enterprise edition, you can do an online index rebuild
ALTER INDEX <Index Name> ON <Table Name>
REBUILD WITH (ONLINE = ON)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 4:43 am
And then implement a routine to defrag the indexes regularly
July 15, 2008 at 7:32 am
Did you cross check, that those are not HEAP Tables.?
There is no Direct way to take care of fragmentation on a HEAP Table.
but before going ahead, make sure they are not HEAP.
Maninder
www.dbanation.com
July 30, 2008 at 11:30 pm
ALTER INDEX
REBUILD WITH (ONLINE = ON)
actually helped to defragment most of indexes in my databases, but when I tried this query in one of my database, I saw a large number of blocking. This DB has a table that contains three indexes with fragmentation of 98%, and datapages of more than million. The table is getting accessed very heavily in production environment. So, just wondering if there is any alternative to rebuild these indexes without any blocking and contention.
July 31, 2008 at 12:05 am
I saw a large number of blocking.
Is it self blocking? If yes, ignore it.
August 6, 2008 at 9:21 am
G'day,
After having studied Andrew J. Kelly's [rebuild_indexes_by_db] from sql mag and from experience, I figure you should put the online rebuild within a try/catch as well as
SET DEADLOCK_PRIORITY LOW ;
Idera's quick reindex analyser (admin toolset) can tell you which ones to redo if you want to prepare the scripts also. Or you can check on your big tables manually for the level of fragmentation before doing the online rebuild - sorry for the redundance, I guess you already know the specific index you want to rebuild/reorg. Since your index is in the 90s fragmentation, rebuild online is the best, since just about all reindexing scripts I have seen have a reorganisation range from 15-30%.
Happy rebuilding during off-peak hours 🙂
[font="Verdana"]Town of Mount Royal, QC
SQL Server DBA since '99
MCDBA, MCITP, PMP, MVP '10, Azure Data Platform Data Engineer
hugo@intellabase.com [/font]
https://drive.google.com/file/d/1qnyiGWyGvDz6Q2VtLPGEsRufy9CUqw-t/view (MCDBA 2001, data eng associate coming asap)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply