Query execution time using an ORMapper

Hello there!
I have a strange problem regarding the query execution time of a
parameterised query executed over sp_executesql. We use an ORMapper
(NHibernate) which generates the query, so it is not necessary to discuss the
use of sp_executesql. If I run the query in SQL Server Management Studio it
is much faster than executing it over the application. Looking at Profiler
the same query needs in MMS 6000 reads and 500 ms and over the application
nearly 200.000 reads and 30.000 ms.
I have no idea why this happens because the database doesnâ't change at all
and I profile on database level. So Iâ'm confused that it depends on the
execution source. The count of database sites in the data cache is constant
and the query plan cache is hit also in both executions.
Any ideas? Thanks
Rob"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:EB7FF480-3C92-48AA-8867-FF2C30E55767@.microsoft.com...
> Hello there!
> I have a strange problem regarding the query execution time of a
> parameterised query executed over sp_executesql. We use an ORMapper
> (NHibernate) which generates the query, so it is not necessary to discuss
> the
> use of sp_executesql. If I run the query in SQL Server Management Studio
> it
> is much faster than executing it over the application. Looking at Profiler
> the same query needs in MMS 6000 reads and 500 ms and over the application
> nearly 200.000 reads and 30.000 ms.
> I have no idea why this happens because the database doesn't change at all
> and I profile on database level. So I'm confused that it depends on the
> execution source. The count of database sites in the data cache is
> constant
> and the query plan cache is hit also in both executions.
>
sp_executesql is fine, the issue is that a parameterized query is just
different than a non-parameterized one. You should expect a different plan
for expensive queries.
Test, analyze and optimize the query in Management Studio using
sp_executesql.
David

Query execution time using an ORMapper

Hello there!
I have a strange problem regarding the query execution time of a
parameterised query executed over sp_executesql. We use an ORMapper
(NHibernate) which generates the query, so it is not necessary to discuss th
e
use of sp_executesql. If I run the query in SQL Server Management Studio it
is much faster than executing it over the application. Looking at Profiler
the same query needs in MMS 6000 reads and 500 ms and over the application
nearly 200.000 reads and 30.000 ms.
I have no idea why this happens because the database doesn’t change at all
and I profile on database level. So I’m confused that it depends on the
execution source. The count of database sites in the data cache is constant
and the query plan cache is hit also in both executions.
Any ideas? Thanks
Rob"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:EB7FF480-3C92-48AA-8867-FF2C30E55767@.microsoft.com...
> Hello there!
> I have a strange problem regarding the query execution time of a
> parameterised query executed over sp_executesql. We use an ORMapper
> (NHibernate) which generates the query, so it is not necessary to discuss
> the
> use of sp_executesql. If I run the query in SQL Server Management Studio
> it
> is much faster than executing it over the application. Looking at Profiler
> the same query needs in MMS 6000 reads and 500 ms and over the application
> nearly 200.000 reads and 30.000 ms.
> I have no idea why this happens because the database doesn't change at all
> and I profile on database level. So I'm confused that it depends on the
> execution source. The count of database sites in the data cache is
> constant
> and the query plan cache is hit also in both executions.
>
sp_executesql is fine, the issue is that a parameterized query is just
different than a non-parameterized one. You should expect a different plan
for expensive queries.
Test, analyze and optimize the query in Management Studio using
sp_executesql.
David

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