• The Learner (4/15/2013)


    Thanks for the follow-up. Having analysts drop temp tables at the end of their stored procedures did seem to help, but I would still see sporadic out of control growth. I just reviewed some of the more often run stored procedures and several of them do have Distinct statements, which I will have the analysts review. Thanks again for the input.

    The problem with dropping Temp Tables at the end is if the proc is executed in the near future while their plan is still cached, the code could actually run a bit slower. SQL Server keeps the "skeleton" of the Temp Tables in some form of cache unless you do an explicit drop.

    Shifting gears, the sproadic out of control growth you speak of might be easy to find. Using a server side profiler run, set it up to look for anything that has more than, say, 10 million reads for both RPC and Batch events. It may take a while to rear its ugly head but the bugger will show up.

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