alt Hacker NewsTo add to sibling comment. This sounds like a parameter sniffing. Read it up. It should help you understand the problem. It is a known issue with SQL Server. Or rather a tradeoff. You don't want to compile a new execution plan for every query. This would eat up CPU by itself.
Yeah seems likely but haven't had time to really dig into it. For now our workaround works.
So my point was that if they want to keep parameter sniffing as default then short cache expiration can at the very least mitigate the performance cliffs that can occur. Better to have a slow query for 10-15 minutes than for hours. Recompiling once in a while shouldn't have a significant performance impact.
In general though I think in many cases Carmack's approach when he made Quake's renderer is a good one.
That is, it's often better to use an algorithm which is on average slightly slower but has much lower variance in execution time, than one which is on average slightly faster but has much greater variance. Parameter sniffing seems to fall into the latter category.