Need an advice

Question

Need an advice

CDBA

Hall of Fame

Points: 3266
More actions
August 13, 2007 at 4:16 pm

#69327

Hi
I am running update statistics on one of my Prod DB once in a week. Now the SP Execution is becoming slower every day. The scenario is as below:
1. DB Size is >1 TB
2. Data is loaded once in a week via many SP/ETL jobs. (One of this SP is slowing down)
3. Only update statistics is scheduled to be executed.
Do u suggest to use INDEXDEFRAG also to improve the performance? Please advice if you have any idea.
(Rebuilding index may be a very costly operation as tables are pretty big)
Regards
Utsab

CDBA

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

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

I would suggest a defrag of the indexes. Rebuild them if you have a maintenance period. Statistics are great, but good statistics on a bad index is only going to help so much.

CDBA Hall of Fame Points: 3266 More actions · Answer 2

Hi Aaron,

Thanks for the same. Even I beleive so...

Do u have any other guess why the SP performance is decreasing gradually?

Regards

Utsab

CDBA

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

Where's the performance hit taking place? On SELECT's???

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 4

Jeff brings a good point (I was in a meeting while responding the first time so I'm sorry if I was brief). The issue is that if you're noticing a slowdown on SELECT statements there's a reasonable chance that you have increasing fragmentation on your indexes (which can be aided by doing a defrag/rebuild). If you're noticing increasing slowness on your insert/update/delete work then that's likely something that index cleanup operations aren't going to help with.

There's a lot of other potential issues that could be at stake. You might be better off for the time being isolating what types of operations you're experiencing the slowdown on.