The other thing, that Jeff hasn't said yet, check the dynamic code that is being generated as well as how it is being executed. I solved some of our dynamic SQL issues by changing how it was generated and executed. Many of our dynamic SQL code being generated where the same except for variable data that could be passed in as scalar variables or table valued parameters using sp_executesql.
In the process I also found better ways to write the SQL as well.
Hi Lynn,
I did this and guess what I found; hidden in the dynamic code were views referencing views, in one case, three deep.
I reckon this is where I will be learning alot about rewriting SQL as well... 🙂
Regards,
Kev