I85PREP
⌘ K
Azure DP-300 study materials
Azure DP-300 study resources

Diagnosing a daily CPU spike on Business Critical

Azure DP-300 · Put your knowledge to work in context.

All case studies

Diagnosing a daily CPU spike on Business Critical

An Azure SQL Database (Business Critical, 16 vCores) hosts an order-management API. Between 09:00 and 10:00 every weekday CPU reaches 96-100% for about 40 minutes and P95 request latency rises from 80 ms to 4.5 seconds. Wait statistics for that window are dominated by SOS_SCHEDULER_YIELD. Query Store is enabled with default settings. One stored procedure, usp_GetOrderSummary, accounts for most of the CPU while the report runs; it is fast for small customers and slow for the three largest. The team has already applied every index their vendor recommends and cannot change application code for six weeks. Read-only routing is not currently used.

1. What must you change before you can diagnose this reliably, and what evidence would confirm the cause?

Read the worked answer

Fix the instrumentation first. Query Store is on by default but with MAX_STORAGE_SIZE_MB = 100 and a coarse interval; under this workload it can hit the size cap and switch to READ_ONLY, at which point the plan history for the 09:00 window may simply be missing.

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE (
  OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 2048,
  INTERVAL_LENGTH_MINUTES = 5, STALE_QUERY_THRESHOLD_DAYS = 30,
  QUERY_CAPTURE_MODE = AUTO);

Then query sys.query_store_runtime_stats joined to the query text and plans, filtered to 09:00-10:00, ordered by average CPU per execution.

Evidence that confirms the cause: the same query_id for usp_GetOrderSummary with two or more distinct plan_ids whose average CPU differs by an order of magnitude, correlating with different parameter values. That is parameter sniffing producing a plan shaped for a small customer being reused for a huge one.

Note that SOS_SCHEDULER_YIELD only tells you the CPUs are saturated - it is a symptom, not a root cause. Do not tune against it.

2. The application change is six weeks away. What do you do now, and what is not available to you?

Read the worked answer

Not available: Resource Governor does not exist in Azure SQL Database (it is a Managed Instance feature), so you cannot cap the report's CPU. Everything below changes where or how the query runs, not how much it is allowed to consume.

  1. Move the report to the readable secondary. Business Critical includes one readable secondary at no additional compute charge. Set ApplicationIntent=ReadOnly in the report's connection string - a configuration change, not a code change. Confirm first that the report tolerates replica lag and that it does not need to read uncommitted changes.
  2. Tame the parameter-sensitive procedure. Prefer a Query Store hint (sp_query_store_set_hints) over forcing one plan for everyone: a plan that is optimal for the three largest customers can be terrible for the rest. Reserve sp_query_store_force_plan or automatic tuning FORCE_LAST_GOOD_PLAN for a clear single-plan regression.
  3. Scale up one tier as a bridge for the six weeks and label it as temporary spend, not as the fix.

Measure after each step in Query Store; if CPU on the primary does not fall after step 1, the report was not the only consumer.

3. After the code change ships, how do you prove it worked and keep it from coming back silently?

Read the worked answer

Establish a baseline from Query Store before the change (average CPU per execution and total CPU for the window, plus P95 latency), then compare the same aggregates for the following two weeks.

Keep watching with:

  • A metric alert on cpu_percent averaged over 15 minutes, using a dynamic threshold rather than a fixed 80% - a fixed threshold on a workload that legitimately peaks at 85% will be muted within a month.
  • A Query Store regression review: weekly query for queries whose average CPU per execution has doubled week over week, or that now have more than one heavily used plan.
  • Automatic tuning recommendations reviewed, not auto-applied, unless you have a rollback path.

Also revisit the tier: the 40-minute 100% plateau on 16 vCores may indicate the workload outgrew the tier, which the report offload only masked. If the primary still saturates after the fix, the honest next step is a genuine capacity change, not another index.