A server can waste CPU compiling the same query shape with a stream of different literals. Forced parameterization can reuse plans quickly, but a shared plan can also fit one value badly.
Prove Compilation Is a Problem
Look for many cached ad hoc statements with the same query_hash and small use counts. Pair that with SQL Compilations/sec over a measured interval and instance CPU. A high compilation counter alone can be normal on a busy server; the question is whether near-identical literal queries consume meaningful CPU. I inspect representative text before grouping. Different queries can share a hash, and the cache only holds surviving plans. Query Store can add history when enabled. Do not enable a database-wide setting because one dashboard card is red.
SELECT TOP (20) qs.query_hash, COUNT(*) AS cached_statement_count,
SUM(qs.execution_count) AS executions,
SUM(qs.total_worker_time) AS worker_microseconds
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.query_hash
ORDER BY cached_statement_count DESC;Read the Compilation Counter Correctly
SQL Compilations/sec in sys.dm_os_performance_counters is a cumulative counter value that needs two observations to calculate a rate. Capture timestamps and values, then subtract and divide by elapsed seconds. Compare with Batch Requests/sec and CPU during the same window. I do not publish a rate from one raw cntr_value. Also note that plan cache churn and server restart can reset the picture. Keep the counter and query-hash evidence together.
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (N'SQL Compilations/sec', N'Batch Requests/sec')
AND object_name LIKE N'%:SQL Statistics%';Turn On Forced Parameterization in One Database
ALTER DATABASE … SET PARAMETERIZATION FORCED changes parameterization for eligible statements in that database. It does not rewrite every statement; stored procedure bodies and other forms have exceptions. The setting change flushes that database's cached plans, so measure after the new cache stabilizes. I test in a restored copy or a controlled production window and keep the rollback command ready. CURRENT means the database your session is using, so switch to the target database before running the block. Confirm the new state in sys.databases.
ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED;
SELECT name, is_parameterization_forced
FROM sys.databases WHERE name = DB_NAME();Compare CPU and Plan Reuse
Repeat the same application workload and capture compile rate, CPU, query-hash grouping, cache use counts, Query Store runtime, and user latency. Compare equal windows and similar traffic. A drop in compilations with rising query duration is not a victory. I look for the literal families that consolidated and those that remained separate. Some queries already parameterize well; others are ineligible or have different structure. The setting is broad, so review more than the one query that prompted it.
Use actual plan XML and text to confirm parameterization. In a quick test, three literal versions of one query shared a single Prepared plan in sys.dm_exec_cached_plans, with @0, @1 and @2 in place of the literals. Do not infer parameterization solely from a lower number of cached plans after a cache flush.
Watch Parameter Sensitivity Under Forced Parameterization
One reused plan can be excellent for a rare value and poor for a common one. Test both selective and broad parameter values, compare actual rows and reads, and watch for new Query Store plan regressions. SQL Server's parameter-sensitive plan features can help eligible queries, but do not assume they solve every pattern. I keep a small list of problem statements and their parameter distribution during the experiment. If one query suffers, a targeted plan guide or application parameterization strategy can be better than the database-wide switch.
What does the worst user-facing request do after the change? That answer matters more than the average compilation counter.
Identify the Query Families That Consolidate
Forced parameterization does not apply equally to every SQL construct. Group cached statements by query_hash, then inspect examples from the largest families. Look for literal-only differences and compare plan use counts after the setting change. I do not call a hash group "one query" without reading its text and plan context. A hash is a useful grouping aid, not a business identity. Query Store can show whether query texts and plans decreased for the target database after the new capture window stabilizes.
Compile rate should be compared with batch rate and CPU under similar traffic. A drop in SQL Compilations/sec is helpful when user requests become faster or the server gains CPU headroom. If the workload is quiet after the switch, the lower counter proves little. I keep the same application nodes and request mix for the before and after test.
Test the Common and Rare Values
A shared parameterized plan can be shaped by one compiled value and reused for another. Choose a selective and a broad value from real workload distributions, with sensitive literals redacted in notes. Compare actual rows, logical reads, CPU, and duration for each. I inspect Query Store for new regressions after enabling the database setting. If one family benefits and another suffers, a targeted template plan guide or application code change can be more precise than FORCED for the whole database.
What happens when statistics change or a plan recompiles? Repeat the two-value test after a representative recompile in a restored copy. A plan that looked stable immediately after the switch can change later. I keep the SIMPLE rollback command and the observation period in the change record. The decision should balance saved compilation work against the worst important request, not only the average.
Keep the before and after cache warmup policy the same. The setting change clears database plans, so the first requests after enabling it can spend more time compiling even if later reuse improves. I exclude that transitional period from the steady-state comparison and still report its user impact. An online switch can have a short-lived cost that matters during peak traffic. One setting can save CPU and spend patience.
Roll Back Forced Parameterization or Keep the Evidence
If the change hurts important queries, use ALTER DATABASE CURRENT SET PARAMETERIZATION SIMPLE and recheck after cache stabilizes. Document which plans changed and whether the application code can be fixed to use parameters directly. I prefer application parameterization for a long-term contract because it is explicit and helps avoid injection risk. Forced parameterization is a practical server-side lever when code cannot change quickly, but it still needs an owner and review date.
The decision should cite measured compile cost, CPU, latency, and parameter-specific behavior. A fast switch is attractive. A measured decision is what keeps it safe.
Related reading on this blog: Optimize for Ad Hoc Workloads: SQL in Sixty Seconds #173 and Parameter Sniffing and Bad Plan.
A lower compile rate is not the whole win, it is one part of a stable workload.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
