Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL server 2005 get slow after rebuild index online on few tables Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 5:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 12:16 PM
Points: 2, Visits: 53
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.
Post #1522903
Posted Friday, December 13, 2013 6:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1522914
Posted Friday, December 13, 2013 7:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 12:16 PM
Points: 2, Visits: 53
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?
Post #1522920
Posted Friday, December 13, 2013 8:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1522922
Posted Monday, December 16, 2013 12:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1523104
Posted Monday, December 16, 2013 1:13 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 27, 2014 9:05 AM
Points: 504, Visits: 364
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.

Post #1523411
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse