September 18, 2017 at 4:39 am
My SP's plan generation differs on two sets of databases having same schema.
One of the db is load tested db with only difference being in data load. However the SP run shows different plan generation in DMV, varies 8 times.
The SP contains many option recompile hint can this be a problem? but this should also make plan generation similar for same input parameters
Trace Flag are also similar. Any leads?
September 18, 2017 at 5:34 am
Lots of things can affect the plan.
Are the two servers exactly the same hardware specification? Same CPU, RAM, Disk, IO.
Are you using the old cardinality estimator on one server and not the other.
Data distribution, non updated statistics, index usage
Max DOP, Cost for Para, optimize for ad hoc work loads, different trace flags could cause an issue depending on the flag.
Recompile could be the problem, if any of the above are true.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply