One use case that turned out to be much more common than I expected, is one where the plan cache is bloated with a bunch of single-use query plans. In today's post, I'll provide you with some information about it, and a useful script for troubleshooting it.
The Plan Cache is where SQL Server stores a cache of compiled execution plans as part of its memory buffer. The database engine automatically evicts “outdated” execution plans based on various rules (for example, when relevant database schema objects are changed or rebuilt, or when an execution plan is no longer being used). The important thing to remember, though, is that this plan cache shares the same memory space as the buffer cache (which is where SQL Server saves table data pages for fast retrieval). So, when the plan cache is bloated with too many compiled execution plans, that means less memory space for data.
It would also mean that when this plan cache gets filled up with "throwaway plans", this would come at the expense of other query plans that you may want to stay there for longer (such as stored procedures and such).