/*
This query calculates the Plan Cache Hit Ratio as a percentage 

in SQL Server. The Plan Cache Hit Ratio measures how often SQL 

Server is able to reuse execution  plans from the plan cache, 

rather than having to compile new ones. 

A higher ratio indicates better performance, as reusing plans saves 

CPU and memory resources.

How: 
1. The query pulls two values from the dynamic management 

   view sys.dm_os_performance_counters:

- Cache hit ratio: The number of times a cached plan was found and reused.
- Cache hit ratio base: The total number of cache lookups

2. It divides the hit count by the total lookups and multiplies by 

   100 to get a percentage.

3. The RTRIM(object_name) is used to ensure trailing spaces 

   in the object_name column do not affect the filter.

 Output:
    - Returns a single value: the plan cache hit ratio as a percentage.
    - A high value (close to 100%) means most query plans are being reused.
    - A low value may indicate frequent query recompilations, 

      which can impact performance.

*/

SELECT
    100 * 
    (
        -- Numerator: Get the current value of the 'Cache hit ratio' counter
        SELECT cntr_value
        FROM sys.dm_os_performance_counters
        WHERE 
            RTRIM(object_name) LIKE '%:Plan Cache'  -- Filter for the Plan Cache object
            AND counter_name = 'Cache hit ratio'    -- Select the 'Cache hit ratio' counter
            AND instance_name = '_Total'            
    ) 
    /
    (
        -- Denominator: Get the current value of the 'Cache hit ratio base' counter
        SELECT cntr_value
        FROM sys.dm_os_performance_counters
        WHERE 
            RTRIM(object_name) LIKE '%:Plan Cache'      
            AND counter_name = 'Cache hit ratio base'   -- Select the base counter for the ratio
            AND instance_name = '_Total'                
    ) AS [Plan cache hit ratio %];

• Use Stored Procedures as much as possible.  Their precompiled nature promotes plan reuse and reduces compilation overhead

• Avoid writing Ad-Hoc queries and minimize dynamic SQL. If unavoidable, use sp_executesql with parameters instead of EXEC to enable parameterization. Require or at least suggest application developers to utilize query parameterization feature in application code

• Standardize query formatting for consistent casing, spacing, and punctuation. Even trivial variations (e.g., extra spaces) generate new plan hashes.  SQL Server performs a binary comparison of the incoming query's text with cached queries. Any difference in whitespace, casing, comments, or literals will result in a different plan hash and prevent reuse

• Consider using a plan guide or a query hint to force parameterization for a specific ad hoc query that generates too many cached execution plans due to variations in the literal values each time it is executed.

Here are the primary triggers or reasons for plan recompilations:

When you modify, drop, or rebuild an index on a table, while SQL Server does not immediately remove related execution plans from the cache, it does mark them as invalid. The next time a query that references the changed table is executed, SQL Server detects the schema change, recompiles the query, and generates a new execution plan, which is then stored in the cache. So unless you have to, scheduled your index maintenance jobs during off peak hours.

In fact, Creating or dropping indexes can indirectly affect plan cache efficiency. If queries are frequently recompiled due to index changes or if many single-use plans are generated, this can lead to plan cache bloat, reducing cache efficiency and potentially degrading performance.

Same goes for the index statistics. SQL Server query optimizer, being 'cost-based', fundamentally relies on cardinality estimation, using per-attribute summary statistics (histograms) to generate efficient execution plans. When statistics are updated—especially if AUTO_UPDATE_STATISTICS is enabled—SQL Server typically invalidates existing cached plans that depend on those statistics. This triggers recompilation the next time the query runs, ensuring the new plan reflects the updated data distribution. If statistics are stale or missing, SQL Server may generate suboptimal plans, and these can remain in cache until statistics are refreshed.

You should also review the overall system configuration - including the database, instance, and server - for optimization. While application design typically falls outside the DBA's direct control (even though many DBAs have development experience), if you have thoroughly optimized everything within your scope and documented your efforts, it should not be difficult to make a strong case for an application redesign.