Home Forums SQL Server 2008 SQL Server 2008 - General High value of work files and Work table RE: High value of work files and Work table

  • August 19, 2014 at 8:41 pm

    #1739449

    WhiteLotus (8/19/2014)

    GilaMonster (8/19/2014)

    Firstly, is that value a problem? Is it higher than usual? Is it causing problems?

    Thanks for your response! appreciate it !

    Yes I use SOLARWIND to monitor SQL Performance and It shows that Workfiles Created/Sec and Worktables created /sec are in CRITICAL status.

    There is some information from SOLARWIND aboout workfiles :

    "Work files could be used to store temporary results for hash joins and hash aggregates. The returned value should be less than 20. Tempdb work files are used in processing hash operations when the amount of data being processed is too large to fit into the available memory."

    Possible problems: High values can indicate thrash in the tempdb file as well as poorly coded queries.

    and This is some information from SOLARWIND aboout worktables (at the moment, the value is : 739/sec)

    "Work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors. The returned value should be less than 20. Worktables are used for queries that use various spools (table spool, index spool, and so on."

    Possible problems: High values could cause general slowdown.

    I wonder what should I do ?

    Start looking for "crap code" and fix it. 😉

    To be honest, though, if you have a lot of batch jobs or reporting requests on a busy machine, 20 seems like a low number especially in this day and age with all the bloody XML flying around.

    I'd still check for performance challenged code, though.

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