I'm troubleshooting the case when a stored procedure runs great from the management studio at any time, but the same stored procedure can run terribly badly in one of the webservers, even for the same parameters. There could be a few things causing this, including blocking, etc.
I would like to leave out the possibility that 2 different plans were created with different SET options, and at least one of those plans was optimized using a combination of parameters that produced a bad plan.
Quoting Benjamin Nevarez:
"in general, query optimization is an expensive operation, and in order to avoid this optimization cost, the plan cache will try to keep the generated execution plans in memory so they can be reused. However, if a new connection running the same stored procedure has different SET options, it may generate a new plan instead...."
In order to tackle this I would like a T-SQL query that would show me all the values that have been set for the session.
Is this possible?
the following SET options will affect the reuse of execution plans:
(some of them)