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

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

distributed transaction MSQL<->oracle

I'm trying to execute this:
create table #tt (nro_parte_erp varchar(50))
declare @.query as varchar(4000)
declare @.estado as char
set @.estado=3D'1'
set @.query =3D'
select
nro_parte_erp=3D nro_parte
from openquery(desarrollo_baan,''
select
nro_parte,
from BAAN.ORDENES_102 a,baan.set_of_cim_102 b where
a=2Eorden_fab_1=3Db.orden_fab and estado_orden=3D'+@.estado+''') a
order by orden_fab_1,operacion'
insert into #tt
exec (@.query)
where desarrollo_baan is a linked server to oracle
but i receive this error:
No se puede realizar la operaci=F3n. El proveedor OLE DB 'MSDAORA' no
pudo iniciar una transacci=F3n distribuida.
Traza de error de OLE DB [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8000d048].
in english something like
The operation could not be performed because the OLE DB provider
'OraOLEDB.Oracle' was unable to begin a distributed transaction.
Error Trace of OLE DB [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8000d048].
And i'm sure that MSDTC is running and i tried re-creating my server.
Some body can help me'Can you run a simple select statement via link server?
"jreategui@.gmail.com" wrote:
> I'm trying to execute this:
> create table #tt (nro_parte_erp varchar(50))
> declare @.query as varchar(4000)
> declare @.estado as char
> set @.estado='1'
> set @.query ='
> select
> nro_parte_erp= nro_parte
> from openquery(desarrollo_baan,''
> select
> nro_parte,
> from BAAN.ORDENES_102 a,baan.set_of_cim_102 b where
> a.orden_fab_1=b.orden_fab and estado_orden='+@.estado+''') a
> order by orden_fab_1,operacion'
> insert into #tt
> exec (@.query)
>
> where desarrollo_baan is a linked server to oracle
> but i receive this error:
> No se puede realizar la operación. El proveedor OLE DB 'MSDAORA' no
> pudo iniciar una transacción distribuida.
> Traza de error de OLE DB [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8000d048].
> in english something like
> The operation could not be performed because the OLE DB provider
> 'OraOLEDB.Oracle' was unable to begin a distributed transaction.
> Error Trace of OLE DB [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8000d048].
>
> And i'm sure that MSDTC is running and i tried re-creating my server.
>
> Some body can help me'
>|||Yes, i tested the same code without the insert statment and it's run.
I mean, this code run perfect:
create table #tt (nro_parte_erp varchar(50))
declare @.query as varchar(4000)
declare @.estado as char
set @.estado='1'
set @.query ='
select
nro_parte_erp= nro_parte
from openquery(desarrollo_baan,''
select
nro_parte,
from BAAN.ORDENES_102 a,baan.set_of_cim_102 b where
a.orden_fab_1=b.orden_fab and estado_orden='+@.estado+''') a
order by orden_fab_1,operacion'
/*insert into #tt */
exec (@.query)|||Looks like your problem is not with the link Server, b/c it is letting you
execute the command. Is the output less than varchar(50)? I have noticed that
sometimes when you use different software the output from exec(@.SQL) will not
get into the temp table. Try insert into a temp table and see if that works?
"jreategui@.gmail.com" wrote:
insert statment and it's run.
> I mean, this code run perfect:
> Yes, i tested the same code without the
> create table #tt (nro_parte_erp varchar(50))
> declare @.query as varchar(4000)
> declare @.estado as char
> set @.estado='1'
> set @.query ='
> select
> nro_parte_erp= nro_parte
>
> from openquery(desarrollo_baan,''
> select
> nro_parte,
> from BAAN.ORDENES_102 a,baan.set_of_cim_102 b where
> a.orden_fab_1=b.orden_fab and estado_orden='+@.estado+''') a
>
> order by orden_fab_1,operacion'
>
> /*insert into #tt */
> exec (@.query)
>|||That's correct, the problem is not the linked server, the query run
perfect. The really problem is making the insert in the temp table with
the data that return the query to the linked server.
I tried making some manuals insert to the temp table and that work, but
with the linked server don't