• I didn't say it's harmless, I said you are highly unlikely to be able to measure an impact.

    Now, what if you have something like Sharepoint that is known to deadlock (and it's not a problem), that traceflag was enabled by default leading to many, many MB of error log, potentially filling the drive?

    Optimise for ad-hoc is not enabled by default for the same reason that any new option is not enabled by default, minimal surprises when upgrading versions. In scenarios where there's minimal ad-hoc query usage, or when I know that ad-hoc queries will always have plans able to be reused, I wouldn't enable it.

    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