It's baaack

I've been chasing a performance issue on and off for several ws. A stored
procedure in a custom application will run in under 30 seconds or it may tak
e
as long as 6 hours to run. I've got a stack of execution plans on this
procedure and sometimes the procedure uses a good plan complete with
parallelism and other times the optimizer decides to take the long way
through the data and use a serial plan and just crunch hundreds of millions
of records.
I got some help from this board several ws ago and learned about
parameter sniffing and found that local variables and defaults helped the
optimizer choose the good plan and incorporate parallelism. We thought we ha
d
it solved until last w, when the optimizer began choosing the bad plan
again. So, we reindexed, updated stats on key tables with full scans and tha
t
solved it.
It's now morphed again and doesn' respond to previous remedies. For whatever
reason, the optimizer insists on taking the long way. I really need to
rewrite this procedure but need a quick fix AND I'd like to understand what'
s
going on. The procedure includes views joined to views which include views
(it's ugly) as well as joins to base tables and I know the confusion is the
order in which tables get joined and processed. So, I reordered the join
syntax in the SP and used a force order option. Now I get the good plan agai
n
complete with parallelism... for now until it morphs again.
Do views prevent the optimizer from making the best use of statiscs? Why
doesn't the optimizer always use parallelism..the cost setting is set to 5-
default and I set it down to 1 and it still didn't use parallelism?
Thanks for any insight
MardyWhether the actual query uses parallelism is determined at run time each and
every time the query is run. This is based on several factors such as how
busy the cpu's are, the amount of available memory etc. A complex query
will usually generate a serial and parallel plan but that is only the
initial plan. At run time the engine decides which to run. So running a
query with a single proc or more than 1 proc can be different each time it
is run. With that said a very complex query with lots of views, joins etc
can be tough to crunch. There are times when the optimizer simply gives up
and says this plan will have to do even though there may be a more optimal
plan that can be generated. When you have lots of objects (tables, views,
indexes) to deal with the cost of trying out each and every possibility can
get too prohibitive. The more objects you have the choices become somewhat
exponential. I have seen very complex sp's take a long time to compile and
not even have the most optimal plan. The solution is to rewrite your
queries. Probably breaking it down into multiple individual sp's. That
way each sp can be optimized rather quickly and is usually easier to tune.
Hope that helps.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:37BD8EB7-2457-4742-B250-EE33CCE3DEA7@.microsoft.com...
> I've been chasing a performance issue on and off for several ws. A
> stored
> procedure in a custom application will run in under 30 seconds or it may
> take
> as long as 6 hours to run. I've got a stack of execution plans on this
> procedure and sometimes the procedure uses a good plan complete with
> parallelism and other times the optimizer decides to take the long way
> through the data and use a serial plan and just crunch hundreds of
> millions
> of records.
> I got some help from this board several ws ago and learned about
> parameter sniffing and found that local variables and defaults helped the
> optimizer choose the good plan and incorporate parallelism. We thought we
> had
> it solved until last w, when the optimizer began choosing the bad plan
> again. So, we reindexed, updated stats on key tables with full scans and
> that
> solved it.
> It's now morphed again and doesn' respond to previous remedies. For
> whatever
> reason, the optimizer insists on taking the long way. I really need to
> rewrite this procedure but need a quick fix AND I'd like to understand
> what's
> going on. The procedure includes views joined to views which include views
> (it's ugly) as well as joins to base tables and I know the confusion is
> the
> order in which tables get joined and processed. So, I reordered the join
> syntax in the SP and used a force order option. Now I get the good plan
> again
> complete with parallelism... for now until it morphs again.
> Do views prevent the optimizer from making the best use of statiscs? Why
> doesn't the optimizer always use parallelism..the cost setting is set to
> 5-
> default and I set it down to 1 and it still didn't use parallelism?
> Thanks for any insight
> Mardy|||If the query contains a lot of sub-queries, then consider inserting the
intermediate results into temporary tables.
Queries of the following style can trip up the optimizer at runtime:
select ... from ... where x in (select ... from ...)
Also, calling UDFs can impede the optimizer.
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:37BD8EB7-2457-4742-B250-EE33CCE3DEA7@.microsoft.com...
> I've been chasing a performance issue on and off for several ws. A
stored
> procedure in a custom application will run in under 30 seconds or it may
take
> as long as 6 hours to run. I've got a stack of execution plans on this
> procedure and sometimes the procedure uses a good plan complete with
> parallelism and other times the optimizer decides to take the long way
> through the data and use a serial plan and just crunch hundreds of
millions
> of records.
> I got some help from this board several ws ago and learned about
> parameter sniffing and found that local variables and defaults helped the
> optimizer choose the good plan and incorporate parallelism. We thought we
had
> it solved until last w, when the optimizer began choosing the bad plan
> again. So, we reindexed, updated stats on key tables with full scans and
that
> solved it.
> It's now morphed again and doesn' respond to previous remedies. For
whatever
> reason, the optimizer insists on taking the long way. I really need to
> rewrite this procedure but need a quick fix AND I'd like to understand
what's
> going on. The procedure includes views joined to views which include views
> (it's ugly) as well as joins to base tables and I know the confusion is
the
> order in which tables get joined and processed. So, I reordered the join
> syntax in the SP and used a force order option. Now I get the good plan
again
> complete with parallelism... for now until it morphs again.
> Do views prevent the optimizer from making the best use of statiscs? Why
> doesn't the optimizer always use parallelism..the cost setting is set to
5-
> default and I set it down to 1 and it still didn't use parallelism?
> Thanks for any insight
> Mardy