Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
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 o f the query ( with different parameter value ). I found in syscacheobjects onl y 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.
Post Follow-up to this message
Re: how to made adhoc query cached?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@microso ft.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. >
Post Follow-up to this message Re: how to made adhoc query cached?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@microso ft.com... > of > only > time > somebody > > >
Post Follow-up to this message Re: how to made adhoc query cached?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@microso ft.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: > kinds syscacheobjects[colo r=darkred]
Post Follow-up to this message Re: how to made adhoc query cached?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@microso ft.com... > satisfy me. > in > kinds > syscacheobjects > > >
Post Follow-up to this message Re: how to made adhoc query cached?You can create an autostart procedure from which you execute this stored pro cedure. 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@microso ft.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 cach e, > 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: >
Post Follow-up to this message Re: how to made adhoc query cached?Thanks "Tibor Karaszi" wrote: > You can create an autostart procedure from which you execute this stored p rocedure. 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@microso ft.com... > > >
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
