Most “random” slowdowns on IIS-hosted ASP.NET apps are not random. The same stored procedure or EF Core query that returns in 20 ms for one user can take seconds for another because SQL Server reused a plan built for the wrong parameter values. That is parameter sniffing: the first execution shapes the cached plan; later calls inherit it whether the cardinality still fits or not.

On shared Windows hosts and small SQL instances this shows up as intermittent page timeouts, spiky CPU, and support tickets that vanish when you “run it again in SSMS.” Fix the plan-quality problem at the query and schema layer instead of raising Command Timeout until the symptom hides.

#How sniffing shows up under IIS

Web traffic is bursty and skewed. A list page filtered by a rare StatusId, a detail page by a hot CustomerId, or a report date range that sometimes covers one day and sometimes a year all feed different cardinalities into one parameterized statement. Connection pooling keeps sessions cheap, but the plan cache is process-wide on the SQL instance. One bad compile from an overnight job or a single power-user filter can poison daytime ASP.NET traffic until the plan ages out or you recycle something you should not need to recycle.

In practice you see: identical RPC calls with different durations in Query Store, excessive logical reads on an index seek that should be a scan (or the reverse), and thread-pool waits when many requests pile behind a nested-loop plan meant for one row. Deadlock retries and pool exhaustion are downstream effects; the root is often a sniffed plan that does not match the live parameter histogram.

#Confirm before you rewrite

Prefer evidence over folklore. On SQL Server 2022 and 2025, Query Store (already worth enabling for deploy windows) makes this straightforward: compare plans for the same query_id, note which plan_id correlates with high duration or reads, and check the sniffed parameter values on the slow plan. From SSMS, actual execution plans on a slow reproduction will show dense arrows and underestimated row counts next to the parameter list.

tsql 
-- Recent runtime stats for a problem query (adapt query_id)
SELECT qsq.query_id, qsp.plan_id,
       rs.avg_duration / 1000.0 AS avg_ms,
       rs.avg_logical_io_reads,
       rs.count_executions,
       qsp.is_forced_plan
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = qsp.plan_id
WHERE qsq.query_id = 12345  -- from Query Store UI
ORDER BY rs.last_execution_time DESC;

If two plans flip-flop and only one is healthy, you have a sniffing (or stats) problem, not a generic “needs more indexes” problem. Check that auto-update statistics is on and that major data-shape changes after bulk loads are followed by targeted UPDATE STATISTICS on the hot tables your ASP.NET paths touch.

#Fixes that work from the app and the database

Start with the narrowest change. For a single stored procedure that serves a multi-tenant filter or optional search, local variables or OPTIMIZE FOR UNKNOWN can densify the plan toward average density when values are highly skewed. Use this when the “average” plan is acceptable for all callers—not when one tenant is 90% of the rows and needs a dedicated shape.

tsql 
CREATE OR ALTER PROCEDURE dbo.GetOrdersForCustomer
  @CustomerId int,
  @Since date
AS
BEGIN
  SET NOCOUNT ON;
  -- Average-density plan when first-call values are atypical
  SELECT o.OrderId, o.OrderedAt, o.Total
  FROM dbo.Orders AS o
  WHERE o.CustomerId = @CustomerId
    AND o.OrderedAt >= @Since
  ORDER BY o.OrderedAt DESC
  OPTION (OPTIMIZE FOR UNKNOWN);
END;

When EF Core generates the SQL, you still control a lot from the database: covering indexes that match the WHERE plus the SELECT list reduce the penalty of a suboptimal join order, and filtered indexes help status or soft-delete columns common in ASP.NET models. Prefer FromSqlInterpolated or a thin stored-proc wrapper for the handful of endpoints that dominate CPU rather than sprinkling OPTION hints through the whole model.

csharp 
// .NET 10 / EF Core 10: keep the hot path parameterized and explicit
public async Task<List<OrderDto>> GetRecentAsync(
    int customerId, DateOnly since, CancellationToken ct)
{
    return await _db.Orders
        .Where(o => o.CustomerId == customerId && o.OrderedAt >= since.ToDateTime(TimeOnly.MinValue))
        .OrderByDescending(o => o.OrderedAt)
        .Select(o => new OrderDto(o.OrderId, o.OrderedAt, o.Total))
        .AsNoTracking()
        .TagWith("GetRecentOrders")
        .ToListAsync(ct);
}

TagWith helps you find the query in Query Store after a Web Deploy. Recompile-on-every-call (OPTION RECOMPILE) is a valid last resort for highly skewed reporting endpoints; it is a poor default for chatty list APIs because compile CPU adds up under an IIS app pool that already shares the SQL instance with other sites.

#Hosting-floor habits that keep plans honest

  • Ship schema and index changes in the same release train as the app; a new filter predicate without a supporting index forces scans that look like “sniffing” in the wild.
  • Avoid building SQL strings that change shape per request (optional columns in the SELECT list, dynamic ORDER BY without a stable form). That fragments the plan cache and multiplies sniffing surface.
  • Keep connection strings pooled and stable; opening ad-hoc connections per request does not fix plans and does stress the host.
  • After large data migrations on SQL Server 2022 or 2025, update statistics on the affected tables before sending traffic back through the ASP.NET site.
  • Use Query Store force-plan only as a temporary bridge while you add the right index or rewrite; forced plans rot when the schema evolves.

Practical takeaway: when an IIS-hosted page is fast in your session and slow in production, capture the query_id, compare plans and sniffed parameters, then apply the smallest fix—covering index, OPTIMIZE FOR UNKNOWN on a proc, or a targeted recompile on a skewed report—before you touch app-pool recycles or blanket timeout increases. Stable plans beat louder timeouts every time.