Rebuilding indexes doesnt work

Question

Rebuilding indexes doesnt work

Trooper09

SSCrazy

Points: 2047
More actions
November 12, 2009 at 11:38 am

#213958

Hi,
I am trying to rebuild / reorganize the indexes on my prod database and it does not seems to work..
I use the code provided on MSDN to perform the opeation :
http://msdn.microsoft.com/en-us/library/ms188917.aspx
The attached file shows the avg_fragmentation before and after I run the script. I am not sure if I am missing anything...

Viewing 6 posts - 1 through 5 (of 5 total)

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

Richard M. SSCertifiable Points: 7401 More actions · Answer 1

The indexes have to be "big" enough to be actually rebuilt. I can't recall now what the threshold is, but I believe it was something about 8 pages or so...

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Trooper09 - Do you believe that the index fragmentation is effecting your performance? Which method of rebuild/defragging did you use?

From the same link that you posted: (Emphasis added)

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

Reducing Fragmentation in an Index
When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:
Drop and re-create the clustered index.
Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. The drawbacks in this method are that the index is offline during the drop and re-create cycle, and that the operation is atomic. If the index creation is interrupted, the index is not re-created. For more information, see CREATE INDEX (Transact-SQL).
Use ALTER INDEX REORGANIZE, the replacement for DBCC INDEXDEFRAG, to reorder the leaf level pages of the index in a logical order. Because this is an online operation, the index is available while the statement is running. The operation can also be interrupted without losing work already completed. The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation, and it does not update statistics.
Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. For more information, see ALTER INDEX (Transact-SQL).
Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. This causes slower response times. If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect. For more information, see this Microsoft Web site

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Trooper09 SSCrazy Points: 2047 More actions · Answer 3

The column avg_fragmentation seems to have pretty high values on few of the indexes on the databases more than 80 and I beleive I should be rebuilding the indexes for this reason. I have a weekly job which has the query taken from MSDN site which performs the necessary operation but when the values of avg_fragementaion is compared before and after the job I see no change .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Trooper09 (11/12/2009)
The column avg_fragmentation seems to have pretty high values on few of the indexes on the databases more than 80 and I beleive I should be rebuilding the indexes for this reason.

How big are those indexes? How many pages?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Looking at your spreadsheet, it looks like many of the items you see that have excessive fragmentation are actually Heaps. The heap designates that the table is just piled together (picture tearing all the pages out of a book and throwing them in a pile - leaving out the table of contents and the Index at the back of the book).

To defrag the heaps, you need a clustered index on the table. Create a clustered index and then you should be able to defrag just fine. As for the clustered indexes that are sitting at about 50%, there are probably very few pages involved in the index and so a defrag won't help them.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events