Please make the world all right again.

I have reported some SQL execution plan whackyness on this newsgroup
( http://shrinkster.com/984 ) where a stored proc ran very slowly when
called as a stored procedure. However, if I just pasted the sproc
source code into Query Analyzer, it ran like a champ on the same set of
data. I finally zeroed in on a particular query that was causing this
and, again, it was fast in Query Analyzer and a dog inside the stored
procedure. The 2 methods would also generate a completely different plan.
Anyway, my co-worker found a solution, which is ugly but works great.
He simply placed the query text into a varchar variable and ran it as
Dynamic SQL using EXEC inside the stored procedure. All of a sudden,
the execution plans were identical and the speed was back to normal.
This begs the question - why in the world is this happening? Running
SQL from an EXEC should slow things down, not speed them up. Afaik, it
goes against everything I've ever learned about databases. Is the
optimizer flawed in SQL Server 2000 SP3? Should we go to SP4?
Thanks.Does your code query a remote server?
PRB: Distributed Queries That Are Wrapped in a Stored Procedure with Input
Parameters May Experience Performance Degradation
http://support.microsoft.com/default.aspx?scid=kb;en-us;320208
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:eTXPZfV6FHA.2192@.TK2MSFTNGP14.phx.gbl...
>I have reported some SQL execution plan whackyness on this newsgroup
> ( http://shrinkster.com/984 ) where a stored proc ran very slowly when
> called as a stored procedure. However, if I just pasted the sproc source
> code into Query Analyzer, it ran like a champ on the same set of data. I
> finally zeroed in on a particular query that was causing this and, again,
> it was fast in Query Analyzer and a dog inside the stored procedure. The
> 2 methods would also generate a completely different plan.
> Anyway, my co-worker found a solution, which is ugly but works great. He
> simply placed the query text into a varchar variable and ran it as Dynamic
> SQL using EXEC inside the stored procedure. All of a sudden, the
> execution plans were identical and the speed was back to normal.
> This begs the question - why in the world is this happening? Running SQL
> from an EXEC should slow things down, not speed them up. Afaik, it goes
> against everything I've ever learned about databases. Is the optimizer
> flawed in SQL Server 2000 SP3? Should we go to SP4?
> Thanks.
>|||> This begs the question - why in the world is this happening? Running SQL
> from an EXEC should slow things down, not speed them up. Afaik, it goes
> against everything I've ever learned about databases. Is the optimizer
> flawed in SQL Server 2000 SP3? Should we go to SP4?
Tibor probably identified the culprit - do you take him up on the
suggestion?|||Geoff N. Hiten wrote:
> Does your code query a remote server?
> PRB: Distributed Queries That Are Wrapped in a Stored Procedure with Input
> Parameters May Experience Performance Degradation
> http://support.microsoft.com/default.aspx?scid=kb;en-us;320208
>
Nope. Everything is on the same server.|||Scott Morris wrote:
>>This begs the question - why in the world is this happening? Running SQL
>>from an EXEC should slow things down, not speed them up. Afaik, it goes
>>against everything I've ever learned about databases. Is the optimizer
>>flawed in SQL Server 2000 SP3? Should we go to SP4?
>
> Tibor probably identified the culprit - do you take him up on the
> suggestion?
Yes, I tried that but it didn't help. There is an article on this topic
which is very informative, but it didn't apply to my situation.
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx|||> Yes, I tried that but it didn't help.
What did you try?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Rizzo" <none@.none.com> wrote in message news:%23FAOPQW6FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Scott Morris wrote:
>>This begs the question - why in the world is this happening? Running SQL
>>from an EXEC should slow things down, not speed them up. Afaik, it goes
>>against everything I've ever learned about databases. Is the optimizer
>>flawed in SQL Server 2000 SP3? Should we go to SP4?
>>
>> Tibor probably identified the culprit - do you take him up on the
>> suggestion?
> Yes, I tried that but it didn't help. There is an article on this topic
> which is very informative, but it didn't apply to my situation.
> http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx|||Tibor Karaszi wrote:
>> Yes, I tried that but it didn't help.
> What did you try?
I tried changing variables to parameters and also variables to hardwired
values.|||Are you saying that when you have the code in a stored procedure, the code is slow compared to when
not? Even if the procedure doesn't have any parameters and you hard-wire the search arguments inside
the procedure code? So basically, you have your TSQL code which is fast, add CREATE PROC on top and
when you execute that proc it is slow? And it doesn't matter if you create the proc or executing the
proc using WITH RECOMPILE? If so, I suggest you open a case with MS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Rizzo" <none@.none.com> wrote in message news:eCBzAlX6FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>> Yes, I tried that but it didn't help.
>> What did you try?
> I tried changing variables to parameters and also variables to hardwired values.|||Tibor Karaszi wrote:
> Are you saying that when you have the code in a stored procedure, the
> code is slow compared to when not? Even if the procedure doesn't have
> any parameters and you hard-wire the search arguments inside the
> procedure code? So basically, you have your TSQL code which is fast, add
> CREATE PROC on top and when you execute that proc it is slow? And it
> doesn't matter if you create the proc or executing the proc using WITH
> RECOMPILE? If so, I suggest you open a case with MS.
Yes, that is precisely what I am saying. I am just a consultant here
and don't have the power to open a case, but I'll see who can take care
of this. Anyway, the problem was solved by executing the query dynamically.