Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Table Variables and performance RE: Table Variables and performance

  • October 9, 2008 at 4:26 pm

    #883167

    G Bryant McClellan (10/8/2008)

    Michael,

    Thanks for the reply. I can see that testing is the only sure-fire method to compare the performance, but there must be some indication that the switch from memory to tempdb has been made. I've inherited support for this application, notorious for its poor performance, and I'm trying to get my arms around all the targets of opportunity to determine where to strike next. That is why I want to determine if the switch to tempdb is happening before dedicating time that I can use in other tuning methods.

    Ummm.... both table variables and temp tables start out in memory and "jump" to TempDB when they overcome some point in memory. If your table variables are large enough where you are concerned that they are spooling out to TempDB, then you should probably be using temp tables instead.

    For many more reasons than that (especially ease of troubleshooting), the only place where I'll use a table variable is in a UDF... and then only because I can't use a temp table there.

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