Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Plan Cache/Memory Performance Expand / Collapse
Posted Tuesday, November 20, 2012 7:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 13, 2016 1:22 AM
Points: 1,416, Visits: 2,931
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 ,
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'


Post #1386912
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse