Azure SQL数据库查询性能异常，清理Query Store恢复，求技术协助

Hey Frank, let's break down what's happening here and how to fix it properly—your issue is a classic case of query plan degradation tied to Query Store behavior, especially with parameterized queries on your Azure SQL DB (compatibility level 150). Here's a step-by-step approach to diagnose and resolve this without having to constantly clear Query Store data:

1. Verify Query Store Core Configuration First #

Even though you've adjusted settings, double-check these critical values to rule out basic misconfiguration:

• Ensure Query Store is in READ_WRITE mode (it can flip to read-only if storage is full):

  SELECT name, value 
  FROM sys.database_scoped_configurations 
  WHERE name = 'QUERY_STORE_OPERATION_MODE';
  

• Check if storage limits are causing issues (full storage triggers read-only mode):

  SELECT current_storage_size_mb, max_storage_size_mb 
  FROM sys.database_query_store_options;
  

• Adjust the stale query cleanup threshold to avoid old, inefficient plans piling up (7-14 days is a sweet spot):

  ALTER DATABASE [mydb] 
  SET QUERY_STORE = ON (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 7));
  

2. Fix Parameter Sniffing & Plan Choice Issues #

Your symptoms (fast initial runs, slowdown after repeated/parameter changes) point directly to parameter sniffing—Query Store might be holding onto a bad plan generated for an atypical parameter value. Here's how to fix it:

• First, identify the problematic query and its stored plans:

  SELECT 
    q.query_id, 
    qt.query_sql_text, 
    p.plan_id, 
    p.is_forced_plan, 
    rs.avg_duration, 
    rs.count_executions
  FROM sys.query_store_query q
  JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
  JOIN sys.query_store_plan p ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
  WHERE qt.query_sql_text LIKE '%your-target-query-fragment%'
  ORDER BY rs.avg_duration DESC;
  

• Force the most efficient plan for the query (replace the placeholders with your actual IDs):

  EXEC sp_query_store_force_plan 
    @query_id = N'your-query-id', 
    @plan_id = N'your-efficient-plan-id';
  

• For queries with highly variable parameters, add OPTION (RECOMPILE) to the query itself—this generates a fresh plan for each execution (ideal for ad-hoc or parameter-sensitive workloads):

  SELECT * FROM your-table WHERE your-column = @param OPTION (RECOMPILE);
  

• Verify parameter sniffing is enabled (it's default, but double-check):

  SELECT name, value 
  FROM sys.database_scoped_configurations 
  WHERE name = 'PARAMETER_SNIFFING';
  

3. Refresh Outdated Statistics #

Rebuilding indexes doesn't always update statistics fully, especially for tables with skewed data distributions. Outdated stats can lead to bad plan choices:

• Update statistics with a full scan for the affected tables:

  UPDATE STATISTICS [your-table-name] WITH FULLSCAN;
  

• Enable asynchronous statistics updates to prevent queries from waiting for stats to refresh:

  ALTER DATABASE [mydb] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
  

4. Fine-Grained Query Store Cleanup (Instead of Full Clear) #

Instead of wiping all Query Store data every time, target only the problematic queries or stale data:

• Remove plans for a specific problematic query:

  EXEC sp_query_store_remove_query @query_id = N'your-query-id';
  

• Flush recent data to disk and clean up stale entries:

  EXEC sp_query_store_flush_db;
  

5. Compatibility Level 150-Specific Checks #

Compatibility level 150 introduced automatic plan correction, but it might not always work as expected:

• Check if automatic plan forcing is enabled:

  SELECT force_plan FROM sys.database_query_store_options;
  

• If auto-plan forcing is causing issues, disable it temporarily and use manual plan forcing instead:

  ALTER DATABASE [mydb] 
  SET QUERY_STORE = ON (AUTO_POLICY = (FORCE_PLAN = OFF));
  

Start with the parameter sniffing and plan forcing steps—those are the most likely fixes for your exact scenario. If you still run into issues, capture a Query Store report (via Azure Portal) for the problematic query to get more insight into plan changes over time.

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