SQL server 2005 get slow after rebuild index online on few tables

Question

SQL server 2005 get slow after rebuild index online on few tables

sunny yang

Grasshopper

Points: 11
More actions
December 13, 2013 at 5:41 pm

#286153

We have an customized JD Edward Enterprise One Application runs on Windows SQL 2005 Enterprise server SP3. There is a scheduled invoicing job runs everyday through the day, it normally process around 120 rows per minutes. We noticed that there are some indexes are heavily fragmented, so we rebuild few of the indexes on a table using online option, it seems to be fine, and has not much impact on the invoice process speed. Then we start to rebuild two indexes online on a relatively larger tables which has 30 million records, then the speed of processing invoice drop down to around 5 rows per minutes.
We first though maybe the statistics change may be causing this issue, so we did the statistics update on these indexes, it didn't help. Then we tried to refresh the server cache and buffer hoping to pick up the better execution plans using the following statements:
CHECKPOINT;
GO
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
it didn't help neither. we then randomly run the statements to refresh buffer and caches many times, but still the same result. The speed keeps around 5 rows per minutes, which is 20 times slower. I was just stuck and could not think of a reason for these, is there anyone has experience anything similar? Or anyone can give some advise, that will be very much appreciated.
Thanks In advance.

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

If the job that does the 120 row/minute processing is either inserting or updating the rows on the tables in question AND the indexes have a 100% FILLFACTOR, then the slowdown might be due to massive page splits for the Clustered Index or massive extent splits for the Non Clustered Indexes.

You'll need to study what the indexes are, how the indexes are being affected by the job, and perhaps change the FILLFACTOR of the indexes. In some cases, you might even want to disable the indexes (not drop) and then rebuild them after the task(s) of inserting or updating large numbers of rows.

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

sunny yang Grasshopper Points: 11 More actions · Answer 2

Thanks for your response and advise Jeff. the last biggest table is normally used as the source reference mostly select statement on that table and then will be inserted the destination. when it was rebuild, the fill factor was default to 0. There is not abnormal amount of page splits. I have checked the execution plan and it seems to be decent, using the index seeks on other two indexes other than the ones we rebuild for it. the percentage of CPU for the queries are higher than normal after the slow down. And the rest such as IO, just seems to be normal. We had this problem starting yesterday afternoon at around 1pm, and then the job finished at 9pm, even though very slow. when it started this morning, it is as slow as yesterday never go back to the normal speed. The job is still currently running and server performance is within the baseline and seems to be normal, just the invoicing still at very low speed. There are many other tables are involving in the invoicing process, but nothing has changed on those, I am really running out of ideas on how to narrow down the issue. Any suggestions please?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

There are so many different things that this problem could be. For example, if the index rebuilds made the INDEX SEEKs possible, it may ironically be the problem. Doing (for example) 40,000 seeks is much more time consuming than doing a table scan on (for example) a million rows. Number of executions in the SEEKs might be one thing to look at. The fix for those, of course, is to science out a different index that will allow a single SEEK followed by a forward scan.

My recommendation, at this point, would be to take a look at the 2nd link under "Helpful Links" in my signature line below. With that kind of information, I'm sure that some of the heavy hitters on this forum could help.

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 4

Check for I/O bottleneck if any.

Recompile your stored procedure and try.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SumLasty SSCommitted Points: 1923 More actions · Answer 5

I have checked the execution plan and it seems to be decent, using the index seeks on other two indexes other than the ones we rebuild for it. the percentage of CPU for the queries are higher than normal after the slow down.

Did you review wait_stats and os_waiting_tasks to see if the invoicing process is waiting on SQL or OS?

You said Execution plan is using indexes other than rebuid ones. What are the costs of these index seeks? Are the indexes in Current plan are appropriate and/or are there key look ups for these seeks?

Are the stats upto date on all the indexes and columns of invoice table.

High CPU may also indicate change in plan due to inappropriate index in the plan.