how to made adhoc query cached?

Hi,
I have a problem with performance of ADHOC query like "exec sp_Name
par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds of
the query ( with different parameter value ). I found in syscacheobjects only
20% of cases. And most heavy query is not cached at all. The execution time
of this particular query is 10 times more than if it was cached. Do somebody
know how to made sqlserver to cache particular adhoc query?
Thanks.Leo
Did you see an execution plan of the query? Did the optimizer
use any indexes defined on the tables?
How big are your tables?
How many rows do you want to retrieve?
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> Hi,
> I have a problem with performance of ADHOC query like "exec sp_Name
> par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds
of
> the query ( with different parameter value ). I found in syscacheobjects
only
> 20% of cases. And most heavy query is not cached at all. The execution
time
> of this particular query is 10 times more than if it was cached. Do
somebody
> know how to made sqlserver to cache particular adhoc query?
> Thanks.
>|||Of cource, I saw the plan under query analizer, and this plan quite satisfy me.
The problem is that the application sometimes use another plan. The query in
plan I see in QA uses tables indexes. The size is not very big - less than
100K records,
but query uses aggregations.
"Uri Dimant" wrote:
> Leo
> Did you see an execution plan of the query? Did the optimizer
> use any indexes defined on the tables?
> How big are your tables?
> How many rows do you want to retrieve?
>
>
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> > Hi,
> > I have a problem with performance of ADHOC query like "exec sp_Name
> > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different kinds
> of
> > the query ( with different parameter value ). I found in syscacheobjects
> only
> > 20% of cases. And most heavy query is not cached at all. The execution
> time
> > of this particular query is 10 times more than if it was cached. Do
> somebody
> > know how to made sqlserver to cache particular adhoc query?
> > Thanks.
> >
>
>|||Leo
So , does it meant that query optimizer was available to use indexes?
Can you post your query and how you call it ?
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> Of cource, I saw the plan under query analizer, and this plan quite
satisfy me.
> The problem is that the application sometimes use another plan. The query
in
> plan I see in QA uses tables indexes. The size is not very big - less than
> 100K records,
> but query uses aggregations.
>
> "Uri Dimant" wrote:
> > Leo
> > Did you see an execution plan of the query? Did the optimizer
> > use any indexes defined on the tables?
> > How big are your tables?
> > How many rows do you want to retrieve?
> >
> >
> >
> >
> >
> >
> > "Leo" <Leo@.discussions.microsoft.com> wrote in message
> > news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> > > Hi,
> > > I have a problem with performance of ADHOC query like "exec sp_Name
> > > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different
kinds
> > of
> > > the query ( with different parameter value ). I found in
syscacheobjects
> > only
> > > 20% of cases. And most heavy query is not cached at all. The execution
> > time
> > > of this particular query is 10 times more than if it was cached. Do
> > somebody
> > > know how to made sqlserver to cache particular adhoc query?
> > > Thanks.
> > >
> >
> >
> >|||It is nice of you , Uri to prompt me to find out the problems from source.
But I afraid it is impossible - the size of procedure is 17 kB and it is
really complicated :)
My question is how make sqlserver put the plan of this procedure to cache.
When I call dbcc freeproccache then call my procedure , it is goes to cache,
but after server restart and cache clearing it is disappiared. I need some
predictable behavior and want it always be in cache.
Thank you once more for assistance.
Leo
"Uri Dimant" wrote:
> Leo
> So , does it meant that query optimizer was available to use indexes?
> Can you post your query and how you call it ?
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> > Of cource, I saw the plan under query analizer, and this plan quite
> satisfy me.
> > The problem is that the application sometimes use another plan. The query
> in
> > plan I see in QA uses tables indexes. The size is not very big - less than
> > 100K records,
> > but query uses aggregations.
> >
> >
> > "Uri Dimant" wrote:
> >
> > > Leo
> > > Did you see an execution plan of the query? Did the optimizer
> > > use any indexes defined on the tables?
> > > How big are your tables?
> > > How many rows do you want to retrieve?
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Leo" <Leo@.discussions.microsoft.com> wrote in message
> > > news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> > > > Hi,
> > > > I have a problem with performance of ADHOC query like "exec sp_Name
> > > > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different
> kinds
> > > of
> > > > the query ( with different parameter value ). I found in
> syscacheobjects
> > > only
> > > > 20% of cases. And most heavy query is not cached at all. The execution
> > > time
> > > > of this particular query is 10 times more than if it was cached. Do
> > > somebody
> > > > know how to made sqlserver to cache particular adhoc query?
> > > > Thanks.
> > > >
> > >
> > >
> > >
>
>|||You can create an autostart procedure from which you execute this stored procedure. See
sp_procoption.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leo" <Leo@.discussions.microsoft.com> wrote in message
news:63F79347-2F0F-4F76-B318-77C3AEC167B2@.microsoft.com...
> It is nice of you , Uri to prompt me to find out the problems from source.
> But I afraid it is impossible - the size of procedure is 17 kB and it is
> really complicated :)
> My question is how make sqlserver put the plan of this procedure to cache.
> When I call dbcc freeproccache then call my procedure , it is goes to cache,
> but after server restart and cache clearing it is disappiared. I need some
> predictable behavior and want it always be in cache.
> Thank you once more for assistance.
> Leo
> "Uri Dimant" wrote:
>> Leo
>> So , does it meant that query optimizer was available to use indexes?
>> Can you post your query and how you call it ?
>>
>> "Leo" <Leo@.discussions.microsoft.com> wrote in message
>> news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
>> > Of cource, I saw the plan under query analizer, and this plan quite
>> satisfy me.
>> > The problem is that the application sometimes use another plan. The query
>> in
>> > plan I see in QA uses tables indexes. The size is not very big - less than
>> > 100K records,
>> > but query uses aggregations.
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> > > Leo
>> > > Did you see an execution plan of the query? Did the optimizer
>> > > use any indexes defined on the tables?
>> > > How big are your tables?
>> > > How many rows do you want to retrieve?
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > "Leo" <Leo@.discussions.microsoft.com> wrote in message
>> > > news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
>> > > > Hi,
>> > > > I have a problem with performance of ADHOC query like "exec sp_Name
>> > > > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different
>> kinds
>> > > of
>> > > > the query ( with different parameter value ). I found in
>> syscacheobjects
>> > > only
>> > > > 20% of cases. And most heavy query is not cached at all. The execution
>> > > time
>> > > > of this particular query is 10 times more than if it was cached. Do
>> > > somebody
>> > > > know how to made sqlserver to cache particular adhoc query?
>> > > > Thanks.
>> > > >
>> > >
>> > >
>> > >
>>|||Thanks
"Tibor Karaszi" wrote:
> You can create an autostart procedure from which you execute this stored procedure. See
> sp_procoption.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> news:63F79347-2F0F-4F76-B318-77C3AEC167B2@.microsoft.com...
> > It is nice of you , Uri to prompt me to find out the problems from source.
> > But I afraid it is impossible - the size of procedure is 17 kB and it is
> > really complicated :)
> > My question is how make sqlserver put the plan of this procedure to cache.
> > When I call dbcc freeproccache then call my procedure , it is goes to cache,
> > but after server restart and cache clearing it is disappiared. I need some
> > predictable behavior and want it always be in cache.
> >
> > Thank you once more for assistance.
> >
> > Leo
> >
> > "Uri Dimant" wrote:
> >
> >> Leo
> >> So , does it meant that query optimizer was available to use indexes?
> >> Can you post your query and how you call it ?
> >>
> >>
> >>
> >> "Leo" <Leo@.discussions.microsoft.com> wrote in message
> >> news:B453A794-56BE-49AC-82E7-C117DDABC73E@.microsoft.com...
> >> > Of cource, I saw the plan under query analizer, and this plan quite
> >> satisfy me.
> >> > The problem is that the application sometimes use another plan. The query
> >> in
> >> > plan I see in QA uses tables indexes. The size is not very big - less than
> >> > 100K records,
> >> > but query uses aggregations.
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> > > Leo
> >> > > Did you see an execution plan of the query? Did the optimizer
> >> > > use any indexes defined on the tables?
> >> > > How big are your tables?
> >> > > How many rows do you want to retrieve?
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > >
> >> > > "Leo" <Leo@.discussions.microsoft.com> wrote in message
> >> > > news:80BD14F0-FF7A-43C7-BBE6-F7C241889597@.microsoft.com...
> >> > > > Hi,
> >> > > > I have a problem with performance of ADHOC query like "exec sp_Name
> >> > > > par1=@.par1 ... parN=@.parN". I know that there are prox. 50 different
> >> kinds
> >> > > of
> >> > > > the query ( with different parameter value ). I found in
> >> syscacheobjects
> >> > > only
> >> > > > 20% of cases. And most heavy query is not cached at all. The execution
> >> > > time
> >> > > > of this particular query is 10 times more than if it was cached. Do
> >> > > somebody
> >> > > > know how to made sqlserver to cache particular adhoc query?
> >> > > > Thanks.
> >> > > >
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>