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 +-800Plan 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] ,
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'