Index rebuild issues

Question

Index rebuild issues

bennehoff.bradley

Valued Member

Points: 52
More actions
December 4, 2018 at 12:17 pm

#416532

I have just started at a company that has an existing ERP system and they are some pretty bad performance issues. I have traced it down to a number of tables that have indexes that are very fragmented and will not defrag with everything I have tried. Yes all the obvious steps have been tried. I have been working with the vendor and now they are saying that we have to talk to their programming department, which i do not buy. Why would an index not rebuild and defrag?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

bennehoff.bradley - Tuesday, December 4, 2018 12:17 PM
I have just started at a company that has an existing ERP system and they are some pretty bad performance issues. I have traced it down to a number of tables that have indexes that are very fragmented and will not defrag with everything I have tried. Yes all the obvious steps have been tried. I have been working with the vendor and now they are saying that we have to talk to their programming department, which i do not buy. Why would an index not rebuild and defrag?

Can't you just drop it and recreate it?

bennehoff.bradley Valued Member Points: 52 More actions · Answer 2

Well that was what I was thinking of doing, but what are the ramifications as far as production is concerned? I do have some maintenance time scheduled tonight.

crow1969 Hall of Fame Points: 3001 More actions · Answer 3

What is the problem in rebuilding them? Are you running into blocking issues? Or does the process error out? Are these very large indexes?

bennehoff.bradley Valued Member Points: 52 More actions · Answer 4

They rebuild but still remain fragmented. I have been working on a script to work on them individually tonight and will post results soon. My problem is that I have over 200 tables with multiple indexes on each and there are 3 divisions with the ERP system, so multiply that times 3. I am trying to take small steps to not impact production but the responsiveness is getting to a point of being completely unacceptable and starting to cost the company money. We have been able to improve it somewhat through the rebuilding steps, but we seem to have hit a wall with this rebuild/reindex issue.

I appreciate all the help!

bennehoff.bradley Valued Member Points: 52 More actions · Answer 5

Update. I am running the following command, on a test db, for all 600+ indexes and the command completes successfully but the fragmentation stays the same on all tables. I am at a loss...

USE [THENRY_App]; ALTER INDEX [PK_ActiveBGTasks_mst] ON [dbo].[ActiveBGTasks_mst] REBUILD WITH (MAXDOP = 1);

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

what is the size in pages of the indexes where fragmentation doesn't change?

bennehoff.bradley Valued Member Points: 52 More actions · Answer 7

You want them all? There are over 600 indexes for 1 database.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

bennehoff.bradley - Tuesday, December 4, 2018 1:54 PM
They rebuild but still remain fragmented. I have been working on a script to work on them individually tonight and will post results soon. My problem is that I have over 200 tables with multiple indexes on each and there are 3 divisions with the ERP system, so multiply that times 3. I am trying to take small steps to not impact production but the responsiveness is getting to a point of being completely unacceptable and starting to cost the company money. We have been able to improve it somewhat through the rebuilding steps, but we seem to have hit a wall with this rebuild/reindex issue.
I appreciate all the help!

If the indexes are small enough, they won't defrag. This is usually because of "mixed extents". If they're larger, they could be fragmenting immediately because of the like of out of order mid index INSERTs, which are not limited by nor maintain the Fill Factor, or, possibly, "ExpAnsive Updates", which also have a high disregard for the Fill Factor. The Fill Factor can help mitigate fragmentation for INSERTs and UPDATEs simply by providing some extra room for new or updated items but only until that room runs out which will result in page splits or other creation of out of order pages.

So, how big are your indexes by page count, what are your INSERT/UPDATE patterns, and do you have any LOBs involved?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)