dm_exec_query_stats/dm_exec_sql_text and plan cache

We are using the following query:
SELECT usecounts, size_in_bytes, cacheobjtype, objtype,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype = 'Adhoc' AND cacheobjtype = 'Compiled Plan'
to figure out some of our worst offenders in terms of un-parameterized
queries. The problem is when we run this query in production (we are using
x64 sql server 2005 sp1 w/ 16 gigs of RAM), we see an immediate drop in the
plan cache to near zero and an increase in the free buffer pool which in
turns causes our database to become unresponsive and sometimes causes the
CPUs to be pegged for an extended period (on an 8 core box). We might expect
the query to use some memory, but certain not that much and we would expect
the plan cache to come right back up but it does not.
Anyone on this board have any ideas? We have engaged PSS but haven't gotten
very far. We would like to be able to use the management views real-time in
production (one of the reasons we decided to upgrade to 2005).Hmm, I just did some testing on our machine ( x32 sql server 2005 sp1 w/ 4
gigs of RAM)
It had no problem. Any other actvities on the server at the same time?
"jeffbrewer" <jeffbrewer@.discussions.microsoft.com> wrote in message
news:99CFB494-3DC8-4FBA-8AAB-6A254FC74AA4@.microsoft.com...
> We are using the following query:
> SELECT usecounts, size_in_bytes, cacheobjtype, objtype,
> REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
> FROM sys.dm_exec_cached_plans AS p
> CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
> WHERE p.objtype = 'Adhoc' AND cacheobjtype = 'Compiled Plan'
> to figure out some of our worst offenders in terms of un-parameterized
> queries. The problem is when we run this query in production (we are using
> x64 sql server 2005 sp1 w/ 16 gigs of RAM), we see an immediate drop in
> the
> plan cache to near zero and an increase in the free buffer pool which in
> turns causes our database to become unresponsive and sometimes causes the
> CPUs to be pegged for an extended period (on an 8 core box). We might
> expect
> the query to use some memory, but certain not that much and we would
> expect
> the plan cache to come right back up but it does not.
> Anyone on this board have any ideas? We have engaged PSS but haven't
> gotten
> very far. We would like to be able to use the management views real-time
> in
> production (one of the reasons we decided to upgrade to 2005).|||There are a lot of activities going on at the same time. We are running this
in the middle of the day in some of our heaviest activity times. Our free
data buffer pool is at 0 and our plan cache is at 2-3 gigs (we do need to do
some parameterization but that's why we want to run this - to know where to
start). We are running at about 1,000 batch requests per second or sometimes
higher. I am trying to replicate this in a test environment by creating load
which will fill up the buffer pool and create a decent amount of plan cache
(run a lot of ad-hoc queries). We wish there was some way which we could set
a minimum plan cache size no matter what the internal memory pressure is -
that would at least probably work around this problem.
Jeff
"Uri Dimant" wrote:

> Hmm, I just did some testing on our machine ( x32 sql server 2005 sp1 w/
4
> gigs of RAM)
> It had no problem. Any other actvities on the server at the same time?
>
> "jeffbrewer" <jeffbrewer@.discussions.microsoft.com> wrote in message
> news:99CFB494-3DC8-4FBA-8AAB-6A254FC74AA4@.microsoft.com...
>
>