Query Compilation Plans

Hi all,
We have a third party application that makes extensive use of
sp_executesql which allows it to reuse compilation plans for the sql
queries. Now, I am trying to monitor what the application is doing using the
Profiler. Since the application reuses compilation plans, often I am not
able to see the actual queries in the profile. So I would like to
temporarilly force recompilation of all queries executed by the application.
I don't know how to do it. So please help me and tell me how to do this in
Query Analyzer; I don't have access to Enterprise Manager.
Thanks in advance.> Since the application reuses compilation plans, often I am not able to see
the
> actual queries in the profile.
You should be able to see statement sent to sql server, unless the client
app is using sp_prepare / sp_execute, in that case you will see sp
_execute and parameters.
AMB
"helpful sql" wrote:
> Hi all,
> We have a third party application that makes extensive use of
> sp_executesql which allows it to reuse compilation plans for the sql
> queries. Now, I am trying to monitor what the application is doing using the
> Profiler. Since the application reuses compilation plans, often I am not
> able to see the actual queries in the profile. So I would like to
> temporarilly force recompilation of all queries executed by the application.
> I don't know how to do it. So please help me and tell me how to do this in
> Query Analyzer; I don't have access to Enterprise Manager.
> Thanks in advance.
>
>|||try
dbcc freeproccache
for removing all the execution plans from the cache|||Yes, the application is using sp_prepare/sp_execute. Is there a way to see
the actual queries used in sp_prepare? I did not capture them in the
Profiler.
Thanks.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:10C6CECD-CD3B-458F-8171-1337EBC78B0A@.microsoft.com...
>> Since the application reuses compilation plans, often I am not able to
>> see
> the
>> actual queries in the profile.
> You should be able to see statement sent to sql server, unless the client
> app is using sp_prepare / sp_execute, in that case you will see sp
> _execute and parameters.
>
> AMB
> "helpful sql" wrote:
>> Hi all,
>> We have a third party application that makes extensive use of
>> sp_executesql which allows it to reuse compilation plans for the sql
>> queries. Now, I am trying to monitor what the application is doing using
>> the
>> Profiler. Since the application reuses compilation plans, often I am not
>> able to see the actual queries in the profile. So I would like to
>> temporarilly force recompilation of all queries executed by the
>> application.
>> I don't know how to do it. So please help me and tell me how to do this
>> in
>> Query Analyzer; I don't have access to Enterprise Manager.
>> Thanks in advance.
>>|||helpful sql,
Try looking inside the system table syscacheobjects.
select
objtype,
sql
from
master.dbo.syscacheobjects
where
objtype = 'Prepared'
go
syscacheobjects
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-c_3dyr.asp
AMB
"helpful sql" wrote:
> Yes, the application is using sp_prepare/sp_execute. Is there a way to see
> the actual queries used in sp_prepare? I did not capture them in the
> Profiler.
> Thanks.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:10C6CECD-CD3B-458F-8171-1337EBC78B0A@.microsoft.com...
> >> Since the application reuses compilation plans, often I am not able to
> >> see
> > the
> >> actual queries in the profile.
> >
> > You should be able to see statement sent to sql server, unless the client
> > app is using sp_prepare / sp_execute, in that case you will see sp
> > _execute and parameters.
> >
> >
> > AMB
> >
> > "helpful sql" wrote:
> >
> >> Hi all,
> >> We have a third party application that makes extensive use of
> >> sp_executesql which allows it to reuse compilation plans for the sql
> >> queries. Now, I am trying to monitor what the application is doing using
> >> the
> >> Profiler. Since the application reuses compilation plans, often I am not
> >> able to see the actual queries in the profile. So I would like to
> >> temporarilly force recompilation of all queries executed by the
> >> application.
> >> I don't know how to do it. So please help me and tell me how to do this
> >> in
> >> Query Analyzer; I don't have access to Enterprise Manager.
> >>
> >> Thanks in advance.
> >>
> >>
> >>
>
>|||Lookup fn_get_sql() in BOL.
"helpful sql" wrote:
> Hi all,
> We have a third party application that makes extensive use of
> sp_executesql which allows it to reuse compilation plans for the sql
> queries. Now, I am trying to monitor what the application is doing using the
> Profiler. Since the application reuses compilation plans, often I am not
> able to see the actual queries in the profile. So I would like to
> temporarilly force recompilation of all queries executed by the application.
> I don't know how to do it. So please help me and tell me how to do this in
> Query Analyzer; I don't have access to Enterprise Manager.
> Thanks in advance.
>
>