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 |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2003-05-06 : 19:51:54
|
Hey All! I've got a weird one. If I run this specific query in QA, I get a Subtree cost of 0.172. If I take that query and put it in a stored proc and execute it, I get a WAY different execution plan and a subtree cost of 1.06.I'd love to give examples of the queries in question, but there are jsut too many tables involved.I'm guessing I need to update statistics or maybe DBCC REINDEX? Any ideas?EDIT: I executed both the Stored Proc and the query in QA, and I had the following at the top of the window:DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE Thanks all!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>Edited by - michaelp on 05/06/2003 19:53:08 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-05-07 : 15:43:59
|
| BumpAny ideas on this guys?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-07 : 16:45:05
|
| I've noticed a few times that qa gets a different query plan to the same query in an sp.The worst was when it scanned a table in an sp rather than using an index - I had to give it a variable instead of a join to get round it.Think it's just something you have to live with.==========================================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. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-05-07 : 18:56:03
|
| Michael,You must remember that the execution plan is based on the first time you run the SP. Specifically, the values passed into the proc are vital to the plan. This is especially important when you have default parameters that use NULLs...DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
Same query Different Execution plan