• george sibbald (7/22/2013)


    GilaMonster (7/22/2013)


    george sibbald (7/19/2013)


    as the message says it is an advanced setting

    sp_CONFIGURE 'show advanced options',1

    RECONFIGURE

    GO

    sp_CONFIGURE ‘optimize for ad hoc workloads’,1

    RECONFIGURE

    GO

    Optimise for ad-hoc was only added in SQL 2008. It's not there in SQL 2005.

    I wonder then if alter database set parameterization forced would be of assistance here? (would need thorough testing)

    I think a far better option would be to move to SP2 (or SP4) which reduced the max size that the plan cache could reach, move to 64 bit and add memory.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass