@steve-2 Jones, @Grant and all,
I have saved the execution plans from enterprise and standard edition. How can I force the stored procedure to use the enterprise plan when I run the stored procedure on standard version? This possibly is far from the answer.But I am curious if this can done. Please advise.
FYI: The SP has many queries as part of the execution plan and only 1 seems to show the big difference as listed underneath.
I do understand the data is pretty vague underneath but any insight will be appreciated.Thanks
FYI: The enterprise uses
index seek(nonclustered view),(Number of rows read 2500,estimated rows to be read 800)
nested loop (actual number of rows for all execution 2500,estimated number of executions 1, estimated number of rows per execution 2400)
table scan(number of rows read - 5;estimated rows to be read - 5)
table scan - 5
Hash Match(Inner join),(actual rows 5000 estimated rows 150000)
Clustered Index scan,(actual rows read and estimated rows read - 1800000)
Non clustered index scan - 350000(number of rows read, estimated rows read)
Merge(Inner) Join(actual rows 200000, estimated 350000)
for the same stored procedure when the same set of parameters are passed.