> We have a problem that we have been wresting with for the last month
> with no success. Any help would be greatly appreciated!
>
> We have a complex stored procedure that has been running very slow from
> within SQL Job but only for a couple of hours every morning, then it
> goes back to normal. The strange thing that the same stored procedure
> is lightning fast even in the morning hours when run from Query
> Analyzer. Running from Query Analyzer can be as much as 30 times
> faster.
>
> Here is what I have so far. I have identified a place inside the stored
> procedure that takes a very long time to execute. The problem is within
> a function that uses regular views. The views themselves are complex as
> well with multiple unions. The spids that are executed by sql job
> generate a huge number of IOs compared to the same query run from Query
> Analyzer (No wonder they run slower). This led me to believe that the
> same stored procedure is using different execution plans. I was able to
> confirm this with the help of profiler. The execution plan that is
> generated for selection from a function is very different for fast and
> slow runs. Problem is that the plan is very complex and I simply
> don't understand what it is doing. The difference that I can spot in
> the execution plans is that fast plan uses a lot of Merge Join(Union)
> while the bad execution plan uses a lot of Nested Loops(Inner Join).
>
>>From my understanding UDFs do no store execution plans and generate
> them on the fly. Why is the execution plan generated from within sql
> job is consistently bad only a couple of hours every morning while the
> same stored procedure executed from Query Analyzer gets a good
> execution plan.
>
> By the way SET nocount on is set in the stored procedure. I can not
> provide any code as there will be simply too much to post to be any
> effective.
>
> Thank you very much in advance.
>

> kshata***@gmail.com wrote:
> > We have a problem that we have been wresting with for the last month
> > with no success. Any help would be greatly appreciated!
> >
> > We have a complex stored procedure that has been running very slow from
> > within SQL Job but only for a couple of hours every morning, then it
> > goes back to normal. The strange thing that the same stored procedure
> > is lightning fast even in the morning hours when run from Query
> > Analyzer. Running from Query Analyzer can be as much as 30 times
> > faster.
> >
> > Here is what I have so far. I have identified a place inside the stored
> > procedure that takes a very long time to execute. The problem is within
> > a function that uses regular views. The views themselves are complex as
> > well with multiple unions. The spids that are executed by sql job
> > generate a huge number of IOs compared to the same query run from Query
> > Analyzer (No wonder they run slower). This led me to believe that the
> > same stored procedure is using different execution plans. I was able to
> > confirm this with the help of profiler. The execution plan that is
> > generated for selection from a function is very different for fast and
> > slow runs. Problem is that the plan is very complex and I simply
> > don't understand what it is doing. The difference that I can spot in
> > the execution plans is that fast plan uses a lot of Merge Join(Union)
> > while the bad execution plan uses a lot of Nested Loops(Inner Join).
> >
> >>From my understanding UDFs do no store execution plans and generate
> > them on the fly. Why is the execution plan generated from within sql
> > job is consistently bad only a couple of hours every morning while the
> > same stored procedure executed from Query Analyzer gets a good
> > execution plan.
> >
> > By the way SET nocount on is set in the stored procedure. I can not
> > provide any code as there will be simply too much to post to be any
> > effective.
> >
> > Thank you very much in advance.
> >
>
> Does this stored procedure accept parameters?  Sounds like you're
> suffering from "parameter sniffing".  Lots of info available online.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com