|
|
Posted by Gert-Jan Strik on 07/17/07 21:38
In addition to the other responses: when using stored procedures,
parameter sniffing can influence the query plan. So if two execution
plans are cached (because of differing connection settings), then the
parameter values of the first call will determine the query plan. If
these differ, the plan can differ.
Gert-Jan
sqlgirl wrote:
>
> Hi,
>
> We are trying to solve a real puzzle. We have a stored procedure that
> exhibits *drastically* different execution times depending on how its
> executed.
>
> When run from QA, it can take as little as 3 seconds. When it is
> called from an Excel vba application, it can take up to 180 seconds.
> Although, at other times, it can take as little as 20 seconds from
> Excel.
>
> Here's a little background. The 180 second response time *usually*
> occurs after a data load into a table that is referenced by the stored
> procedure.
>
> A check of DBCC show_statistics shows that the statistics DO get
> updated after a large amount of data is loaded into the table.
>
> *** So, my first question is, does the updated statistics force a
> recompile of the stored procedure?
>
> Next, we checked syscacheobjects to see what was going on with the
> execution plan for this stored procedure. What I expected to see was
> ONE execution plan for the stored procedure.
>
> This is not the case at all. What is happening is that TWO separate
> COMPILED PLANs are being created, depending on whether the sp is run
> from QA or from Excel.
>
> In addition, there are several EXECUTABLE PLANs that correspond to the
> two COMPILED PLANs. Depending on *where* the sp is run, the usecount
> increases for the various EXECUTABLE PLANS.
>
> To me, this does not make any sense! Why are there *multiple* compile
> and executable plans for the SAME sp?
>
> One theory we have is, that we need to call the sp with the dbo
> qualifier, ie) EXEC dbo.sp
>
> Has anyone seen this? I just want to get to the bottom of this and
> find out why sometimes the query takes 180 seconds and other times
> only takes 3 seconds!!
>
> Please help.
>
> Thanks much
Navigation:
[Reply to this message]
|