Re: SP runs slower than same query in Query analyzer. Why??

• Previous message: Guillermo: "NUEVA APLICACION PARA SQLSERVER Y MSDE"
• Next in thread: hwadhwa: "Re: SP runs slower than same query in Query analyzer. Why??"
• Maybe reply: hwadhwa: "Re: SP runs slower than same query in Query analyzer. Why??"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 1 Jun 2004 13:21:23 -0500

Thanks for this info. You just solved a hot production problem for me!

Ray Higdon wrote:
> *Good info Andrew
>
> --
> Ray Higdon MCSE, MCDBA, CCNA
> ---
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:uYZ5k8c9DHA.3200@TK2MSFTNGP09.phx.gbl...
> > I agree with Ray in that you are most likely running two different
> cached
> > plans. If the syntax is not exact (even spaces) they will get 2
> different
> > cached plans. The slow one is most likely using a scan while the
> other is
> > using a seek. You can compare the two plans to be sure. The text I
> will
> > post below is a very good description of something called
> parameter
> sniffing
> > from Bart at MS that you should be aware of. Just a suggestion.
> Since
> you
> > know where the data is depending on the date (estputime) why not
> query
> only
> > the relevant table(s). Maybe have several stored procs where they
> only
> > select from the tables that pertain to the date range in question.
> Your
> opt
> > to get better performance overall. All it takes is a little simple
> logic
> to
> > determine which sp to call.
> >
> >
> >
> > The reason for the performance difference stems from a feature
> called
> > "parameter sniffing". Consider a stored proc defined as follows:
> >
> > CREATE PROC proc1 @p1 int AS
> > SELECT * FROM table1 WHERE c1 = @p1
> > GO
> >
> > Keep in mind that the server has to compile a complete execution
> plan for
> > the proc before the proc begins to execute. In 6.5, at compile
> time SQL
> > didn't know what the value of @p1 was, so it had to make a lot of
> guesses
> > when compiling a plan. Suppose all of the actual parameter values
> for
> > "@p1 int" that a user ever passed into this stored proc were
> unique
> > integers that were greater than 0, but suppose 40% of the [c1]
> values in
> > [table1] were, in fact, 0. SQL would use the average density of
> the
> > column to estimate the number of rows that this predicate would
> return;
> > this would be an overestimate, and SQL would might choose a table
> scan
> > over an index seek based on the rowcount estimates. A table scan
> would
> > be the best plan if the parameter value was 0, but unfortunately
> it
> > happens that users will never or rarely pass @p1=0, so performance
> of the
> > stored proc for more typical parameters suffers.
> >
> > In SQL 7.0 or 2000, suppose you executed this proc for the first
> time
> > (when the sp plan is not in cache) with the command "EXEC proc1 @p1
> =
> > 10". Parameter sniffing allows SQL to insert the known value of
> > parameter @p1 into the query at compile time before a plan for the
> query
> > is generated. Because SQL knows that the value of @p1 is not 0, it
> can
> > compile a plan that is tailored to the class of parameters that is
> > actually passed into the proc, so for example it might select an
> index
> > seek instead of a table scan based on the smaller estimated
> rowcount --
> > this is a good thing if most of the time 0 is not the value passed
> as
> > @p1. Generally speaking, this feature allows more efficient stored
> proc
> > execution plans, but a key requirement for everything to work as
> expected
> > is that the parameter values used for compilation be "typical".
> >
> > In your case, the problem is that you have default NULL values for
> your
> > parameters ("@Today DATETIME = NULL, ...") that are not typical
> because
> > the parameter values are changed inside the stored proc before they
> are
> > used -- as a result NULL will never actually be used to search the
> > column. If the first execution of this stored proc doesn't pass in
> an
> > explicit value for the @Today parameter, SQL believes that its
> value will
> > be NULL. When SQL compiles the plan for this sp it substitutes
> NULL for
> > each occurrence of @Today that is embedded within a query.
> > Unfortunately, after execution begins the first thing the stored
> proc
> > does is change @Today to a non-NULL value if it is found to be
> NULL, but
> > unfortunately SQL doesn't know about this at compile time. Because
> NULL
> > is a very atypical parameter value, the plan that SQL generates may
> not
> > be a good one for the new value of the parameter that is assigned
> at
> > execution time.
> >
> > So, the bottom line is that if you assign defaults to your sp
> parameters
> > and later use those same parameters in a query, the defaults should
> be
> > "typical" because they will be used during plan generation. If you
> must
> > use defaults and business logic dictates that they be atypical (as
> may be
> > the case here if app modifications are not an option), there are
> two
> > possible solutions if you determine that the substitution of
> atypical
> > parameter values is causing bad plans:
> >
> > 1. "Disable" parameter sniffing by using local DECLARE'd variables
> that
> > you SET equal to the parameters inside the stored proc, and use the
> local
> > variables instead of the offending parameters in the queries. This
> is the
> > solution that you found yourself. SQL can't use parameter sniffing
> in
> > this case so it must make some guesses, but in this case the guess
> based
> > on average column density is better than the plan based on a
> specific but
> > "wrong" parameter value (NULL).
> >
> > 2. Nest the affected queries somehow so that they run within a
> different
> > context that will require a distinct execution plan. There are
> several
> > possibilities here. for example:
> > a. Put the affected queries in a different "child" stored proc.
> If
> > you execute that stored proc within this one *after* the parameter
> @Today
> > has been changed to its final value, parameter sniffing will
> suddenly
> > become your friend because the value SQL uses to compile the
> queries
> > inside the child stored proc is the actual value that will be used
> in the
> > query.
> > b. Use sp_executesql to execute the affected queries. The plan
> won't
> > be generated until the sp_executesql stmt actually runs, which is
> of
> > course after the parameter values have been changed.
> > c. Use dynamic SQL ("EXEC (@sql)") to execute the affected
> queries.
> > An equivalent approach would be to put the query in a child stored
> proc
> > just like 2.a, but execute it within the parent proc with EXEC
> WITH
> > RECOMPILE.
> >
> > Option #1 seems to have worked well for you in this case, although
> > sometimes one of the options in #2 is a preferable choice. Here
> are some
> > guidelines, although when you're dealing with something as
> complicated as
> > the query optimizer experimentation is often the best approach
> <g>:
> >
> > - If you have only one "class" (defined as values that have
> similar
> > density in the table) of actual parameter value that is used within
> a
> > query (even if there are other classes of data in the base table
> that are
> > never or rarely searched on), 2.a. or 2.b is probably the best
> option.
> > This is because these options permit the actual parameter values to
> be
> > used during compilation which should result in the most efficient
> query
> > plan for that class of parameter.
> > - If you have multiple "classes" of parameter value (for
> example, for
> > the column being searched, half the table data is NULL, the other
> half
> > are unique integers, and you may do searches on either class), 2.c
> can be
> > effective. The downside is that a new plan for the query must be
> > compiled on each execution, but the upside is that the plan will
> always
> > be tailored to the parameter value being used for that particular
> > execution. This is best when there is no single execution plan
> that
> > provides acceptable execution time for all classes of parameters.
> >
> > HTH -
> > Bart
> > ------------
> > Bart Duncan
> > Microsoft SQL Server Support
> >
> > Please reply to the newsgroup only - thanks.
> >
> > This posting is provided "AS IS" with no warranties, and confers
> no
> > rights.
> >
> >
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "jokes54321" <jokes54321@nospam.com> wrote in message
> > news:Od$0FTb9DHA.2672@TK2MSFTNGP10.phx.gbl...
> This[color=blue]
> > is
> against[color=blue]
> moves[color=blue]
> > By
> > make
> data[color=blue]
> > 5-10
> >
> > *

--
eknapp
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message402647.html
 

• Previous message: Guillermo: "NUEVA APLICACION PARA SQLSERVER Y MSDE"
• Next in thread: hwadhwa: "Re: SP runs slower than same query in Query analyzer. Why??"
• Maybe reply: hwadhwa: "Re: SP runs slower than same query in Query analyzer. Why??"
• Messages sorted by: [ date ] [ thread ]