There Are Days When I Feel Like Giving Up on the Plan Cache and Query Store.

In theory, SQL Server performance monitoring is pretty simple: 1. Review the server’s top wait types, 2. Find the queries causing those wait types, 3. Fix those queries, or improve the way the server reacts to them (indexes, settings, etc.). But in practice, step 2 is awful because:

Parameter Sensitive Plan Optimization in SQL Server 2022

Learn about a new feature of SQL Server 2022 - Parameter Sensitive Plan Optimization(PSPO)

SQL Server Plan Cache Bloat Performance Impacts

Learn about the SQL Server plan cache and how SQL Server caches query plans, and the potential of plan cache bloat which could impact performance.

Checking the Plan Cache Warnings for a SQL Server Database

How often do you check your query plans to see if they contain any warnings? If you're missing them, it means that you're not getting all those hints about missing indexes, join predicates or statistics. Is the query optimiser trying to tell you about implicit conversions? Dennes shows how to view the warnings in plan cache for a particular database using SQL.

Find Single use plans in Plan Cache

This script will return all the Adhoc Single use Plans currently in your Plan Cache.

It also excludes Plan Shells created from auto/forced parameterization. Works for 2005/2008