Plan Cache/Memory Performance

  • Hi Guys

    I am dealing with a system that seems to be under memory pressure. According to task manager, memory is always above 90% used.

    I've used performance counters and noted the following:

    Buffer Manager

    PLE is consistently under 200

    Plan Cache hit ratio never goes above 50%

    Buffer cache hit ratio is above 90%

    Page Reads is +-800

    Plan Cache

    Plan Cache hit ratio consistently below 50%

    Also, I've noticed high amounts for Compilations and Re-compilations Per/Second

    Also, a quick assessment showed me that there is +- 15000 Adhoc plans in my cache, +-13000 have only been used once

    My initial thought was to enable "Optimize for Adhoc workloads", I can't do this because it's a 2005 instance

    Another ideas that I have is to enable Forced Parameterization for the database(s). The problem is that this instance hosts 60 databases.

    I'm trying to figure out how I can narrow down to the database for which the majority of these single-use plans exist.

    I have used the below script to return all single-use plans, from a quick glance through the results, I was able to deduce that there are single use non-parameterized plan shells (From auto-parameterization).

    Any ideas how I can narrow my search to which database I can start looking at for possible Forced Parameterization and also how I can retrieve the results from my script, excluding the non-parameterized plan shells.?

    SELECT [text] ,

    cacheobjtype ,

    objtype ,

    cp.size_in_bytes ,

    cp.usecounts

    FROM sys.dm_exec_cached_plans AS cp

    CROSS APPLY sys.dm_exec_sql_text(plan_handle) st

    WHERE text NOT LIKE '%sys.dm_exec_cached_plans%'

    AND cacheobjtype != 'Parse Tree'

    and usecounts = 1

    and cacheobjtype = 'Compiled Plan'

    and objtype = 'Adhoc'

    Thanks

Viewing 0 posts

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