Plan Cache - Adhoc - Usecounts 1

  • Hi All

    On my SQL Server instance, I have +- 26000 Adhoc plans and +-23000 of those plans have only been used one.

    This insance hosts 8 databases, where can I start looking to see which database(s) these plans are being generated for?

    Any other troubleshooting steps I can take for this?

    Thanks

  • 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.

    "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

  • 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

  • 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

    That is a sledge hammer approach that can make some things faster/better but can totally screw the pooch on others. Test and see if it is right for your workloads.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • 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.

    "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

  • Thanks Guys

  • 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

  • sys.dm_exec_query_plan ( plan_handle ) has a dbid field.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (10/26/2012)


    sys.dm_exec_query_plan ( plan_handle ) has a dbid field.

    That column shows null for adhoc and prepared statements

    Guess I'll have to do some serious digging..

    Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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