Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jraha
Starting Member
16 Posts |
 Posted - 2002-08-21 : 11:20:07
|
| I ran into a strange problem recently:When I execute a stored procedure, it uses one execution plan, but when I run the contents of the stored procedure in query analyser, it uses a different execution plan.I ran this bit of code to remove any cached plans, but the same thing occurred.DECLARE @dbid int SET @dbid=DB_ID('db2k') DBCC FLUSHPROCINDB(@dbid) What could cause this?Thanks,Joel |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-21 : 12:11:19
|
| Caused by a stored procedure not optimising in the same way as a query.It's rare but it happens.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jraha
Starting Member
16 Posts |
Posted - 2002-08-21 : 12:29:07
|
| What would cause a stored proc to optimize differently than a query? Statistics on columns? |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-21 : 12:38:00
|
| When you run it as a query do you hardcode the values or use local variables (assuming the stored procedure has input parameters).Can you post your DML.HTHJasper Smith |
|
|
|
jraha
Starting Member
16 Posts |
Posted - 2002-08-21 : 13:01:24
|
| The query is kind of pointless to post without sending all of the relative information about indexes, foreign keys ect but I'm afraid that that would be very difficult (not to mention embarassing) to send you. The indexes are alright, but the foreign keys are no where near in place.So here's what I can give you in the hopes that you'd be able to shed some light:The stored procedure call: declare @proposal select @proposal =1234 exec WhyNancy @proposal = @proposalThe execution in query analyser: declare @proposal select @proposal = 1234 select ...The stored procedure create statement: Create WhyNancy @proposal AS /* Note that this select statement is identical to the one run in query analyser*/ select ...I realise this has become a theoretical question since I'm unable to post table designs at this time, but any ideas are welcomed. If I get a few extra moments I'll try and post the table design later on.Thanks,Joel |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-21 : 15:13:58
|
| How are the plans different ?Does the stored procedure input parameter have a default value or allow NULL ?HTHJasper Smith |
|
|
|
|
|
|
Execution Plan discrepancy