The same reference list can be fetched on every request even when it changes once a week. Caching query results in the application can remove those repeated trips when freshness rules are explicit.
Find a Good Candidate for Caching Query Results
A good cached result is read frequently, changes infrequently, has a clear owner, and can tolerate a defined delay before updates appear. Reference data and stable public configuration fit better than a customer’s current balance. Measure request frequency, query cost, result size, and freshness requirement before caching query results. A cheap query called rarely is not worth a new failure mode.
I ask which result users request again and again without expecting it to change. That is more useful than a broad rule to cache everything. If the query is slow because of a missing index, fix the database path first. Caching can reduce load, but it should not hide an inefficient query that still runs on every cache miss.
Know the Freshness Contract
Write down how long a cached value can be stale. Some data can wait minutes. Other data must change with the transaction. Set an expiry based on the business rule, not on a round number chosen by the developer. Tell users when a view is cached if the difference matters to a decision.
I have seen a cache described as fast while nobody could say when it refreshed. That is a speed claim with a correctness gap. Ask what happens if a price, permission, or status changes immediately after a value is cached. The answer determines whether expiry alone is enough or the application needs active invalidation.
Inspect Repeated Database Work
Query Store can show a statement’s execution frequency and average resource use. Use it to find high-frequency reads that return similar small results. The query below lists recent execution counts in the current database. It does not prove that every execution returned the same rows. That requires application-level understanding and a review of parameter groups.
I use the count as a lead. A frequently executed cheap lookup can add up, but a query with varied user-specific parameters can have little cache reuse. Check the parameter pattern and data sensitivity before creating a shared cache entry.
SELECT TOP (20)
q.query_id,
SUM(rs.count_executions) AS executions,
MIN(CONVERT(nvarchar(4000), qt.query_sql_text)) AS sample_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
GROUP BY q.query_id
ORDER BY executions DESC;Choose the Cache Key
The key must include every input that changes the result: tenant, user scope, locale, feature flags, and query parameters. A missing key component can serve one user’s data to another. Keep keys readable enough to debug and bounded enough to manage. Do not put secrets or raw personal data into cache key names or logs.
I test two users with different permissions against the same endpoint. If the result differs, a shared key is unsafe. A cache can become a security bug faster than a database query because it sits outside the permission check unless the application designs for that boundary. Treat cache access as part of authorization.
Plan Invalidation When Caching Query Results
Expiry is simple but allows staleness until the timer ends. Event-driven invalidation can refresh sooner but requires every write path to signal a change. Versioned keys or a small change token can make invalidation easier to reason about. Choose one primary method and test failure cases. A missed invalidation is harder to notice than a slow query.
I ask who owns every write to the underlying data. If an administrator can update the table directly and the application never hears about it, an event-only cache can remain stale. A short expiry can bound the problem, or the operational process can include an invalidation step. The contract must cover maintenance as well as normal application writes.
Avoid the Cache Stampede
When a popular key expires, many requests can hit SQL Server at once to rebuild it. Use a single-flight mechanism, jittered expiry, or stale-while-refresh behavior where correctness allows it. Limit the rebuild query so a miss does not overload the database. Monitor hit rate and miss bursts, not only average latency.
I test a cold start with realistic concurrency. A cache that looks excellent after warmup can make deployment restarts painful. The system should recover gracefully when the cache is empty or unavailable. The database remains the source of truth, so the miss path deserves just as much design attention as the hit path.
Budget Memory and Eviction
Cached results consume memory in each application instance or a shared cache service. Define maximum size, expiry, and eviction policy. Large personalized result sets can fill memory quickly while providing little reuse. Avoid turning every report result into a permanent object. A cache needs an owner who watches growth.
I compare the memory cost with the database work saved. If each user requests a unique result once, the cache is a collection of copies rather than a performance feature. A smaller query result or pagination can help more. The best candidate is a repeated, compact result whose freshness rule is understood.
Keep Query Store Healthy
Before using Query Store counts to justify a cache, confirm it is collecting. A read-only Query Store or narrow capture policy can omit recent work. The query below shows its current state. Combine that with application telemetry that identifies endpoint calls and parameter reuse. Database-side frequency alone cannot tell you cache hit potential.
I include a before-and-after measurement plan: database executions, CPU, application latency, hit rate, and stale-data incidents. Caching should improve both load and correctness under the agreed contract.
SELECT desired_state_desc,
actual_state_desc,
current_storage_size_mb
FROM sys.database_query_store_options;Test Failure and Correctness After Caching Query Results
Test expiry, invalidation after each write path, user-scope separation, cache restart, and database outage behavior. Decide whether serving stale data is permitted when SQL Server is unavailable. Do not silently switch to stale values for a workflow that requires current permission or financial information. Make the fallback visible and safe.
What result would be dangerous if it were one refresh behind? Exclude it or design stronger invalidation. Caching query results is a useful application tool when the data contract is explicit. It is not a substitute for a well-designed query or for a tested authorization boundary.
Related reading on this blog: List All Frequently Ran Stored Procedure From Server Cache and Query Store Status for All the Databases.
A cache hit is not automatically correct, it is correct when freshness and access rules still hold.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
