ASP.NET应用中Azure SQL查询执行缓慢超时问题咨询

Let’s dive into why your Azure SQL function is timing out in your ASP.NET app during peak traffic—even though it runs in under a second in SSMS—and walk through actionable fixes beyond just clearing the procedure cache.

Common Root Causes #

• Parameter Sniffing Mismatch: SQL Server generates execution plans based on initial parameter values. If your app uses a wide range of parameters during peak hours, the cached plan might be optimized for a value that’s no longer representative. SSMS often uses different parameter contexts (like local variables vs. your app’s direct parameterization) that lead to more efficient plans.
• Connection Context Differences: Your ASP.NET app and SSMS might use different session settings (like ANSI_NULLS, QUOTED_IDENTIFIER, or isolation level). These settings can change how the query optimizer generates and uses execution plans for your function.
• Stale/Bloated Plan Cache: Under heavy load, the plan cache can accumulate outdated or inefficient plans for your function. Clearing the cache works temporarily because it forces a fresh plan, but this is just a band-aid.
• Resource Contention: During peak traffic, Azure SQL might be throttling CPU, memory, or I/O resources. A query that runs fast in low-load SSMS can slow to a crawl when the database is under stress.

Step-by-Step Fixes #

1. Fix Parameter Sniffing #

• Add OPTION (RECOMPILE) to the Function: Force SQL Server to generate a fresh plan for each execution. For scalar functions, add the hint to the inner query:

  CREATE FUNCTION dbo.YourTargetFunction(@InputParam INT)
  RETURNS INT
  AS
  BEGIN
      DECLARE @Result INT
      SELECT @Result = TargetColumn FROM YourTable WHERE ID = @InputParam
      OPTION (RECOMPILE)
      RETURN @Result
  END
  

  For table-valued functions, apply the hint directly to the return query.
• Wrap Parameters in Local Variables: In your ADO command, use a local variable to avoid parameter sniffing:

  var sqlCommand = new SqlCommand(@"
      DECLARE @LocalParam INT = @AppParam
      SELECT * FROM dbo.YourTargetFunction(@LocalParam)
  ", sqlConnection);
  sqlCommand.Parameters.AddWithValue("@AppParam", yourInputValue);
  

2. Align Connection Settings with SSMS #

Match the session settings between your app and SSMS (check SSMS settings via right-click query window > Properties). Replicate these in your app’s command, for example:

sqlCommand.CommandText = "SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; SELECT * FROM dbo.YourTargetFunction(@Param)";

3. Optimize Plan Cache (Don’t Just Clear It) #

• Use Plan Guides: Capture the efficient execution plan from SSMS, then create a plan guide to force that plan for your function. This ensures consistent performance even under load.
• Enable Ad-Hoc Workload Optimization: Reduce cache bloat with this configuration:

  ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;
  

  This helps the database manage cache space more efficiently for frequently changing queries.

4. Diagnose Resource Contention #

• Check Azure SQL Metrics: In the Azure Portal, monitor CPU, memory, log IO, and data IO during peak hours. If any resource hits 100% utilization, you may need to scale your SQL tier or optimize the function’s underlying query (e.g., add missing indexes).
• Leverage Query Store: Enable Azure SQL’s Query Store to track execution plans over time. You can compare the slow app execution plan with the fast SSMS plan and force the efficient one to be used.

5. Rewrite the Function for Better Performance #

Scalar functions are often less efficient than inline table-valued functions (ITVF) or stored procedures. Rewriting your function as an ITVF can yield better optimization:

CREATE FUNCTION dbo.YourOptimizedITVF(@InputParam INT)
RETURNS TABLE
AS
RETURN (
    SELECT TargetColumn AS Result FROM YourTable WHERE ID = @InputParam
)

Call it in your app with: SELECT Result FROM dbo.YourOptimizedITVF(@Param)

Temporary Workaround (If You Need to Keep Clearing Cache) #

If you must use the cache-clearing command temporarily, automate it instead of running it manually. For Azure SQL Managed Instance, create a SQL Agent job to run the command during off-peak hours. For single databases, use an Azure Function triggered by performance metrics (like high query latency) to execute:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Remember this is not a long-term solution—focus on the fixes above to resolve the root cause.

内容的提问来源于stack exchange，提问作者Ahmed Mujtaba