Need an advice

  • 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

  • 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.

  • 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

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

  • 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.

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

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