Lowell (7/5/2012)
Yes this is all true, but I am not running it via the stored procedure- i am just running SQL code with the parameters passed directly , as in the above code in my last post. Why would changing it from synonyms to directly pointing at the view give different execution plans?
exactly my point: you are not testing the procedure itself, and building a completely different adhoc execution plan for yourself when you run it directly.
the issue is with a bad execution plan that is stored for the procedure. when you pass specific values to the ad hoc code, a plan for those values gets built., which is not how the proc is running. the issue is with a bad execution plan for the procedure, not the synonym.
Ignoring the fact that its a stored procedure for a minute and testing the SQL code... Why do the 2 SQL code examples above give 2 different execution plans when run, even though they are the same apart from one is using synonyms? Surely the execution plans should be the same?