You ship an ASP.NET Core 10 build to IIS on Friday. Saturday morning a list page that used to return in 80 ms sits at two seconds. CPU on the web box is quiet. The SQL connection pool is not exhausted. Something in the plan changed, and you will not catch it with a one-off Profiler session on a shared or lightly managed SQL instance.

Query Store is the durable flight recorder for that class of problem. It keeps query text, plans, runtime stats, and wait categories inside the database so you can compare “before deploy” to “after deploy” without leaving a trace running. It ships with SQL Server 2022 and SQL Server 2025; the workflow below is the same on both.

#Turn it on with bounds you can live with

On a Windows VPS or dedicated SQL instance you control, enable Query Store at the database level. Cap storage so it cannot grow without limit on a disk that also holds data and logs. AUTO capture mode drops trivial noise and keeps the interesting statements.

tsql 
ALTER DATABASE [YourAppDb]
SET QUERY_STORE = ON
(
  OPERATION_MODE = READ_WRITE,
  DATA_FLUSH_INTERVAL_SECONDS = 900,
  INTERVAL_LENGTH_MINUTES = 60,
  MAX_STORAGE_SIZE_MB = 1024,
  QUERY_CAPTURE_MODE = AUTO,
  SIZE_BASED_CLEANUP_MODE = AUTO,
  MAX_PLANS_PER_QUERY = 200
);

-- Confirm
SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

If the database lives on a multi-tenant SQL host, you may not have ALTER DATABASE rights. Ask the host to enable Query Store for your database with a modest MAX_STORAGE_SIZE_MB and AUTO capture. Do not assume it is already on.

#Tag ASP.NET traffic in the connection string

Query Store attributes work better when you can separate app traffic from ad-hoc SSMS sessions and background jobs. Set Application Name on every production connection string used by the IIS app pool. EF Core and Microsoft.Data.SqlClient both pass it through.

xml 
<!-- appsettings.Production.json or a Web Deploy parameter -->
"ConnectionStrings": {
  "AppDb": "Server=sql.example;Database=YourAppDb;User ID=app_user;Password=***;Encrypt=True;TrustServerCertificate=False;Application Name=YourApp.IIS;"
}

Use a distinct name per environment (YourApp.IIS.Prod vs YourApp.IIS.Staging) so a staging soak does not pollute production baselines. Keep the SQL login least-privileged; Application Name is metadata, not a security boundary.

#Find regressions after a publish

After a Web Deploy or folder swap, wait one Query Store interval (often 60 minutes with the settings above), then look for queries whose recent duration or CPU jumped relative to the prior window. SSMS has a “Regressed Queries” report; the catalog views work when you only have a query window.

tsql 
-- Top recent consumers by average duration (last 24 hours of runtime stats)
SELECT TOP (20)
  q.query_id,
  qt.query_sql_text,
  rs.avg_duration / 1000.0 AS avg_duration_ms,
  rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
  rs.count_executions,
  rs.last_execution_time
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query q ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
WHERE rs.last_execution_time >= DATEADD(hour, -24, SYSUTCDATETIME())
ORDER BY rs.avg_duration DESC;

Open the plan_id for a slow row and compare it to an older plan for the same query_id. Classic post-deploy patterns for ASP.NET apps: a parameter-sensitive plan flipped after data growth, a missing index that only hurts the new filter shape from a revised EF Core LINQ query, or a scan that appeared when a covering index was dropped in a migration.

#Fix the query first; force plans last

  • Confirm the call path in the app: which action, which EF Core query or SqlCommand, which parameters on a cold cache vs warm cache.
  • Add or adjust indexes for the actual predicate and ORDER BY the page uses—not a generic “index everything” script. Prefer covering includes for list endpoints.
  • Watch for EF Core patterns that multiply round-trips (lazy loads in a loop, unbounded Include graphs). Query Store shows the SQL; the app still owns the chatty shape.
  • Only after the text and indexes are sane, consider a forced plan for a known-good plan_id. Document it, re-test after the next schema change, and unforce when the underlying issue is fixed.

Forced plans are a tourniquet. They hide sniffing and skew until statistics or schema move again. On SQL Server 2022 and 2025, still treat them as temporary ops controls, not a substitute for indexing and stable parameter shapes from the ASP.NET side.

#Operational habits that keep it useful

Align Query Store cleanup with how often you deploy. If you ship daily, a seven-day practical history is more valuable than a huge store full of month-old ad-hoc text. When MAX_STORAGE_SIZE_MB is hit and the store goes read-only, you lose the exact window you need after a bad publish—monitor current_storage_size_mb or the hosting control panel’s disk alerts.

Pair Query Store with boring app-side hygiene: sensible SqlCommand/EF timeouts, connection pooling left on, and an Application Name you can grep in wait and session DMVs. Query Store explains plan history; it does not replace fixing pool starvation or lock convoys when those are the real wait.

Practical takeaway: before the next IIS production publish, enable Query Store with a hard size cap, set Application Name=YourApp.IIS on the production connection string, and save a simple top-duration query you can run an hour after deploy. When a page slows down, compare plans for that query_id first—then fix indexes or the EF shape, and only force a plan if you must keep the site up while you ship the real fix.