Why does a counter labeled per second keep growing? Measuring compilations per second from T-SQL requires two samples and an elapsed interval. Compare that rate with batch requests before deciding whether compilation deserves a closer investigation.
Read the Counter Type Before the Label
sys.dm_os_performance_counters exposes the underlying counter values. Some counters are gauges, while the rate counters in this example accumulate activity. Their names include /sec because a monitoring client calculates a rate from samples. Selecting cntr_value once does not perform that calculation.
I check the counter type whenever a reported rate looks implausibly large. A cumulative value since startup can look impressive while saying very little about the current workload. The meaningful observation is the change between two samples over a known interval.
Use the SQL Statistics object and an empty instance_name for Batch Requests/sec, SQL Compilations/sec, and SQL Re-Compilations/sec. Keep all three in the same sampling design. Named instances alter the object-name prefix, so filter its suffix rather than hard-coding one instance's complete object name. The column is padded with trailing spaces, so trim it before matching the suffix. Counter labels are helpful, but they are not arithmetic instructions executed by SELECT.
Measuring compilations per second requires two samples that describe the same running instance and counter lifetime.
Capture Compilations per Second With Two Timed Samples
The following query stores the first sample, waits for a deliberate sampling interval, and captures the second. The wait is a test setting, not a measured performance claim. Use the actual timestamps to calculate elapsed seconds because the requested delay is not a guaranteed exact observation interval.
The join pairs the same counter, object, and instance. A negative delta indicates a reset or another invalid comparison and should not be reported as a negative workload rate. A missing counter is also a collection issue to inspect, not evidence of zero activity.
I keep the raw samples available while validating a rate calculation. They reveal resets, unexpected duplicate rows, and an interval that was shorter or longer than intended. For repeated collection, use a permanent history design with startup context rather than running an endless WAITFOR loop in an administrative window. Sampling should be intentional enough to explain later.
SELECT SYSUTCDATETIME() AS SampleTime,object_name,counter_name,instance_name,cntr_value
INTO #CounterBefore
FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE '%:SQL Statistics' AND instance_name=''
AND counter_name IN('Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec');
WAITFOR DELAY '00:00:05';
SELECT SYSUTCDATETIME() AS SampleTime,object_name,counter_name,instance_name,cntr_value
INTO #CounterAfter
FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE '%:SQL Statistics' AND instance_name=''
AND counter_name IN('Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec');
SELECT a.counter_name,a.cntr_value-b.cntr_value AS CounterDelta,
(a.cntr_value-b.cntr_value)*1000.0
/NULLIF(DATEDIFF_BIG(millisecond,b.SampleTime,a.SampleTime),0) AS RatePerSecond
INTO #CounterRates
FROM #CounterAfter AS a JOIN #CounterBefore AS b
ON a.object_name=b.object_name AND a.counter_name=b.counter_name AND a.instance_name=b.instance_name
WHERE a.cntr_value>=b.cntr_value;
SELECT * FROM #CounterRates;Compare Compilation With Batch Activity
A compilation rate means more when compared with the amount of submitted work. The next query returns the rates together and calculates compilation divided by batch-request rate. NULLIF protects a quiet interval whose batch rate is zero. Leave that ratio undefined rather than inventing a healthy or unhealthy percentage.
The counters are instance-wide. One busy application can dominate them while another application's queries behave normally. A batch is also not a fixed unit of useful work: one batch can contain multiple statements. The ratio is therefore a broad diagnostic signal, not a universal efficiency score.
What was running during the sample? Record deployments, reporting bursts, maintenance, and application changes that affect compilation. A deliberately recompiling report and a short repeatedly parameterized lookup have different expectations. Compare similar workload windows before deciding that a changed ratio proves a new problem. Keep recompilations visible rather than folding all planning activity into one unexplained number.
SELECT MAX(CASE WHEN counter_name='Batch Requests/sec' THEN RatePerSecond END) AS BatchRate,
MAX(CASE WHEN counter_name='SQL Compilations/sec' THEN RatePerSecond END) AS CompilationRate,
MAX(CASE WHEN counter_name='SQL Re-Compilations/sec' THEN RatePerSecond END) AS RecompilationRate,
MAX(CASE WHEN counter_name='SQL Compilations/sec' THEN RatePerSecond END)
/NULLIF(MAX(CASE WHEN counter_name='Batch Requests/sec' THEN RatePerSecond END),0) AS CompileToBatchRatio
FROM #CounterRates;
Inspect Single-Use Plans for a Repeatable Pattern
If compilation appears high, inspect the cache for a relevant pattern. The following query groups ad hoc plan entries by current usecounts and sums their bytes. It helps show whether many entries have little observed reuse. It does not turn usecounts into an exact lifetime execution count.
Read representative statement texts after identifying a category. Repeated logical statements with changing literals suggest a parameterization review. Unrelated one-time analytical requests need a different discussion. A cache snapshot also misses plans already evicted, so it cannot fully explain the sampled rate by itself.
Keep compilation activity and cache memory as separate metrics. A large number of tiny entries can consume little memory but still require planning work. A few large cached plans can consume memory with modest current compilation. The investigation should connect the counter signal to a real query-submission pattern instead of choosing a fix from one aggregate alone.
SELECT usecounts,COUNT_BIG(*) AS CacheEntries,
SUM(CONVERT(bigint,size_in_bytes)) AS CacheBytes
FROM sys.dm_exec_cached_plans
WHERE objtype='Adhoc'
GROUP BY usecounts
ORDER BY usecounts;Investigate Recompilation Causes Separately
Recompilation can follow statistics changes, schema changes, temporary-table behavior, or an explicit RECOMPILE choice. Those causes require different responses. Capture targeted recompilation evidence when the counter indicates that category matters. Do not remove a deliberate hint without checking why it exists.
Parameterization can improve reuse, but a single reused plan still needs to serve the actual parameter distribution. Test skew and representative inputs. The goal is appropriate planning work, not a zero compilation rate. New statements and invalidated plans legitimately need compilation.
The optimize for ad hoc workloads setting addresses memory retained by eligible first-use ad hoc plans. It does not remove the first compilation. Keep that distinction clear when discussing options. Clearing the cache also does not fix excessive compilation. It removes plans and makes subsequent requests compile again, which works against the objective being investigated.
Measure Compilations per Second Again After a Change
Collect another comparable interval after the tested change. Keep the same counter selection, reset handling, and elapsed-time calculation. Include the workload's actual query performance so a lower rate does not conceal a slower reused plan.
Short samples reveal bursts, while longer samples smooth them. Choose an interval that fits the question and retain its duration. A quiet interval cannot validate behavior during a busy processing period. A server restart between samples invalidates the delta and needs a new baseline.
Measuring compilations per second is useful when the arithmetic is explicit and the interpretation remains modest. Capture cumulative values, calculate their rates, compare with batch activity, and investigate the statements behind the pattern. The result should direct a focused workload check rather than produce an automatic instance-wide configuration change.
Related reading on this blog: Reading Performance Counters in T-SQL Without Misreading Them and The Handful of Counters That Actually Matter.
A compilation ratio is not a verdict, it is a signal to examine the workload.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
