DevDB (4/23/2013)
Flushing the entire cache, I think is never a good idea. But what I was more interested in to see the execution plan plan for the slow running sql when it is running fast and when it is bad. Do you have anything to compare for good vs bad plan?What I believe that when the query/ SP is running fast, is using parallel plan but in bad mode it is using the serial plan. Which will cause the query to run very- slow.
Multi- statement TVF may cause the query to use serial plan.
So if you could share some more information, that will be helpful.
I have looked at the plans between the two and they are the same. As the MAXDOP = 1 for the server, parallelism isn't really a concern. The MAXDOP has been set to 1 for 12-18 months now, so it isn't a new change to the system.
If needed, I can get you the plans. Maybe you will see something that I am not. However, as TVF don't really show up well in execution plans, not sure how much value there will be.
Fraggle