September 10, 2012 at 9:03 am
We are using SQL2005. two weeks back , we run script for fragmentation, and found major tables were showing high fragementation and needed rebuilt. Last monday , we brough the system down to do clean rebuilt on indexes and percentage of fragementation was under 25 %.
Within one week today fragementation level for other tables show back in above 50%. Can anyone advise or help us diaognsis the issue and what we need to do keep our fragementation level low after rebuilt.
Thank you in advance.
September 10, 2012 at 9:08 am
Fragmentatioin is not something that can be prevented, it has to be managed.
I would start by reading the following articles:
http://qa.sqlservercentral.com/articles/Indexing/68439/
September 10, 2012 at 9:22 am
Thank you for sharing the information. The indexes we rebuilt were created by oracle/peoplesoft. We use CRM module of Peoplesoft.
September 10, 2012 at 9:39 am
kshatriya24 (9/10/2012)
Thank you for sharing the information. The indexes we rebuilt were created by oracle/peoplesoft. We use CRM module of Peoplesoft.
I helped support PeopleSoft HR and Finance systems for 5 years. I only provided support for SQL Server, didn't do any work directly with PeopleSoft code. I had automated processes that maintained our indexes based on the level of fragmentation. These routines ran nightly as part of the backup processes. I did nothing if the indexes were less than 10%, between 10% and 30% I would reorg, and over 30% then I would rebuild the indexes.
September 10, 2012 at 10:04 am
great !! Thank you.
September 10, 2012 at 10:08 am
One more thing, if you need to rebuild a clustered index and a nonclustered index on the same table be sure to do the clustered index first. Otherwise the nonclustered index ends up being rebuilt twice.
September 10, 2012 at 10:13 am
Lynn Pettis (9/10/2012)
One more thing, if you need to rebuild a clustered index and a nonclustered index on the same table be sure to do the clustered index first. Otherwise the nonclustered index ends up being rebuilt twice.
Rebuilding a clustered index does not rebuild the nonclustered indexes on that table. Not in SQL 2005.
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
September 10, 2012 at 10:17 am
GilaMonster (9/10/2012)
Lynn Pettis (9/10/2012)
One more thing, if you need to rebuild a clustered index and a nonclustered index on the same table be sure to do the clustered index first. Otherwise the nonclustered index ends up being rebuilt twice.Rebuilding a clustered index does not rebuild the nonclustered indexes on that table. Not in SQL 2005.
Geez, why do I keep going back to when I had to deal with this on a regular basis with SQL Server 2000? I know I had to be sure I did these in a specific order there or it took longer than needed. I just can't get that out of my mind. You have corrected me on this several times. If we ever meet in person, please smack me lightly upside the head (actually, don't, but you know what I mean).
Old habits die hard, I guess.
September 10, 2012 at 10:22 am
So, is it wrong to code these processes to do the clustrered index first if it needs to be rebuilt as well as any nonclustered indexes?
September 10, 2012 at 10:24 am
In SLQ 2005 and above, you just rebuild the indexes that need rebuilding, order does not matter.
On SQL 2000, if the clustered index was not unique, rebuilding it rebuilt all the nonclusters as well, so in that case on that version, you'd just rebuild the cluster, not the nonclustered indexes
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply