Post reply

What set options should you avoid that cause recompiles

  • June 4, 2009 at 8:28 am

    #132930

    I've heard and seen mentioned a few times that certain SET commands force a recompile, I even preach it to others, but I've never backed this up with data.

    Do any of you have a list of these SET commands?

    Thanks,


    John Zacharkan

  • June 4, 2009 at 9:03 am

    #1004701

    ANSI_DEFAULTS

    ANSI_NULLS

    ANSI_PADDING

    ANSI_WARNINGS

    CONCAT_NULL_YIELDS_NULL

    From MSDN

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 4, 2009 at 9:14 am

    #1004715

    Look @ BOL and search for "SET Options That Affect Results"

    Its is under Indexes | Designing Indexes | Gernal Index Design Guidelines.

    Some good tips in there.

  • June 4, 2009 at 9:34 am

    #1004730

    Thanks everyone


    John Zacharkan

  • April 26, 2011 at 2:32 pm

    #1316223

    Don't know if anyone cares, but I ran into one that I could not find listed in the BOL under "SET Options That Affect Results" which definitely does cause a recompile (left in from an optimization attempt by mistake):

    SET STATISTICS TIME OFF

    SET STATISTICS IO OFF

    Commented out above them were the ONs.

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

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