Home Forums SQL Server 2008 SQL Server 2008 Administration Plan Cache - Adhoc - Usecounts 1 RE: Plan Cache - Adhoc - Usecounts 1

  • October 26, 2012 at 10:28 am

    #1553289

    Grant Fritchey (10/26/2012)

    SQLSACT (10/25/2012)

    Grant Fritchey (10/23/2012)

    There is a setting called 'Optimize for Ad Hoc Workload' that could help you out. Instead of storing a plan each time a query is run, the first time it's run a plan stub is stored. This radically reduces the amount of wasted space in the cache.

    Thanks

    What do you think about the database setting "parameterization forced"?

    Thanks

    Completely missed this until Kevin answered it.

    I agree with Kevin. I've seldom seen it help. I haven't seen it hurt though, yet. Again, I'm with Kevin, test it and see.

    If I go the route of changing the setting to Forced Parameterization, how can I get an idea of which database all these single use plans come from?

    Thanks