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 |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2005-02-23 : 08:29:52
|
| Hi,Running a stored procedure on both the live and the Dev sql server machine.The tables in both servers have the same indexesWhen analysing the execution plan for on each machine, they look different?What could be the possible reasons for this please?It's just that the SP is identical on both machines.for instace on one plan, there is an inner join for the nested loop whereas on the other plan, there is an outer join instead Thanks |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-02-23 : 13:01:56
|
Update the table statistics and see if one changes. Maybe one is out of step with the tables data. You can do anything at www.zombo.com |
 |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-02-23 : 14:07:31
|
| do you mean running sp_updatestats TableName ?if so, then I already have.Thanks |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-02-23 : 17:15:20
|
| How about rebuilding the indexes?You can do anything at www.zombo.com |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-02-24 : 07:58:52
|
| I do that anyway.Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-24 : 08:04:57
|
| Try flushing the procedure and data caches on both machines using DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, respectively.The tables have the same structure and indexes...do they have the same data though? This will affect the statistics. For that matter, if you only have auto-created statistics then it's possible that a set was not generated and would not be utilized by the optimizer. Run sp_statistics on both servers and check that they are indeed completely identical. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-02-24 : 08:19:26
|
| The data is NOT exactly the same. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-02-24 : 08:23:55
|
| should I run both the dbcc commands you suggested on the live sql server?wouldn't this slow down the queries that are running now?Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-24 : 13:19:52
|
| It will but it shouldn't last too long. You really only need to run FREEPROCCACHE and run the procedure with the WITH RECOMPILE option. You want to force the old plan out of cache and guarantee the procedure recompiles. If you still get different plans then the issue is probably in the statistics. |
|
|
|
|
|
|
optimization - exec plan