Slow query

Understand the concept

In plain English

Slow query is not just a query that feels sluggish; it is a database statement whose duration, frequency, CPU use, I/O, waits, or memory pressure affects users, batch windows, or service capacity. In Azure, you usually find slow queries through Azure SQL Query Store, PostgreSQL query statistics, MySQL slow query logs, Application Insights dependency telemetry, or Log Analytics. The fix might be indexing, query rewriting, parameter review, scaling, cache design, or removing unnecessary calls. A good slow-query investigation connects the database symptom to the application path that users actually experience.

Why it matters

Slow queries matter because they are one of the most common causes of cloud applications feeling unreliable even when every Azure resource reports healthy. One inefficient statement can hold locks, saturate CPU, burn I/O, increase DTU or vCore pressure, extend batch windows, and trigger application timeouts. Scaling the database may hide the issue temporarily while increasing spend. Good teams identify whether the problem is query shape, missing index, stale statistics, parameter sensitivity, chatty application design, or insufficient capacity. The value is not only faster SQL; it is fewer incidents, cleaner capacity planning, and evidence-based decisions about tuning versus scaling during peak demand.

Technical context

In Azure architecture, slow query analysis sits across the data plane, application telemetry, and monitoring layer. The database engine records execution duration, plans, waits, reads, writes, and frequency, while Azure Monitor and Application Insights show how those statements affect request latency and dependency calls. Azure SQL often uses Query Store; PostgreSQL can use pg_stat_statements and diagnostic logging; MySQL can use slow query logs and performance metrics. CLI is usually used to discover resources, diagnostic settings, SKU, metrics, and log routing, while SQL or KQL tools perform deeper statement analysis.

Exam context

Compare with

Where it is used

Where you see it

1. Azure SQL Query Store surfaces slow query text, plans, duration, CPU, reads, waits, and regression patterns inside the database troubleshooting workflow for production reviews and audit evidence.
2. Application Insights dependency telemetry shows database calls that dominate request duration, helping teams connect slow SQL behavior to user-facing latency for a specific endpoint or workflow.
3. Log Analytics workbooks or database diagnostic logs reveal repeated long-running statements, deadlocks, timeouts, or high resource usage during incident windows and tuning reviews after releases.

Common situations

• Find the specific SQL statement causing checkout, login, reporting, or API latency instead of scaling blindly.
• Distinguish a missing-index problem from a capacity problem before upgrading database tiers or adding replicas.
• Correlate Application Insights dependency latency with Query Store evidence during a production performance incident.
• Detect release-related query regressions after schema changes, new filters, parameter changes, or ORM-generated SQL updates.
• Build a tuning backlog from recurring high-total-duration queries that quietly consume compute every business day.

Illustrative Azure scenarios

These examples show how the concept can affect design and operations. They are illustrative scenarios, not customer claims.

Azure CLI

From ten years of Azure engineering work, I use Azure CLI in slow-query investigations to establish the Azure facts before going deep in SQL tools. CLI quickly shows the database, server, SKU, region, diagnostic settings, metrics, private endpoint context, and resource tags. That prevents a common mistake: tuning the wrong database or blaming the engine when telemetry is not enabled. CLI also helps export evidence for change records, compare production with staging, and confirm whether recent scaling, firewall, or monitoring changes happened near the regression. The actual plan review may happen in T-SQL or KQL, but CLI frames the investigation.

az resource list --resource-group <resource-group> --output table

az resource show --ids <resource-id>

Architecture context

Architecturally, a slow query is a symptom that crosses application, database, and observability boundaries. The query text runs in the data plane, but the user impact appears in application latency, queue buildup, retry storms, or failed jobs. Azure SQL, PostgreSQL, MySQL, Cosmos DB, and analytics engines expose different evidence, so the architecture must identify which engine owns the workload and where diagnostics are retained. I expect a production design to include Query Store or equivalent telemetry, Log Analytics routing where appropriate, index and schema ownership, and an escalation path between app and database teams. Without that contract, slow-query incidents turn into blame loops.

Security

Security matters because slow-query evidence can expose sensitive business data, table names, tenant identifiers, predicates, stored procedure names, or customer behavior. Operators should avoid pasting full query text into public tickets or broad chat channels. Access to Query Store, database DMVs, diagnostic logs, and Log Analytics workspaces should follow least privilege. If the fix requires adding an index, changing a stored procedure, or enabling logs, the change should follow normal review because it can affect data exposure, retention, and compliance. Slow-query troubleshooting should also confirm that retry storms are not masking authentication failures or blocked network paths. Evidence handling should be as deliberate as code or schema handling.

Cost

Slow queries create cost through wasted compute, larger SKUs, longer job runtimes, excess logging, and engineering time. Teams often scale from General Purpose to Business Critical or raise vCores when the cheaper fix is a targeted index, query rewrite, or reduced call frequency. In serverless databases, slow queries can extend active compute time. In provisioned systems, they consume capacity that could serve other work. Cost analysis should compare tuning effort, index storage, plan stability, and scaling benefit. The FinOps lesson is simple: do not buy permanent capacity for a query problem until evidence proves capacity is the real constraint. That evidence also supports capacity requests when tuning alone is insufficient.

Reliability

Reliability impact is direct. Slow queries can exhaust connection pools, hold locks, delay transactions, miss batch windows, and cause upstream services to retry until a small database issue becomes a platform incident. A single reporting query can block operational writes if isolation, indexing, or workload separation is poor. Operators should correlate query duration with application errors, deadlocks, CPU, I/O, storage, and connection metrics. Reliable recovery may involve killing a session, scaling temporarily, applying an index, rolling back a release, or moving a report. The runbook should define safe emergency actions and post-incident tuning ownership. Recovery steps should be rehearsed before the busiest business window.

Performance

Performance is the visible symptom and the diagnostic target. Slow-query work looks at duration, CPU time, logical reads, physical reads, waits, plan choice, parameter values, blocking, and frequency. Azure telemetry should connect the statement to user-facing latency, p95 or p99 response time, and dependency calls. A query that runs once for ten seconds may be less damaging than one that runs thousands of times for 300 milliseconds. Performance tuning may require indexes, statistics updates, plan forcing, query rewriting, caching, batching, or application changes. Operators should measure before and after, not rely on explain-plan optimism alone. The final proof should be measured under representative workload conditions.

Operations

Operations teams handle slow queries by collecting evidence before guessing. They identify the affected database, time window, workload path, query signature, plan, waits, and related application dependency calls. Azure CLI helps confirm resource configuration and diagnostic routing; SQL tools, Query Store, logs, and KQL identify the expensive statements. Operators document whether the issue is new, recurring, release-related, data-volume-related, or capacity-related. Changes should be tested with representative data and monitored after deployment. Good operations also keep a known list of critical queries, expected durations, and rollback steps for index or procedure changes. They preserve before-and-after metrics so improvements are not anecdotal.

Common mistakes

• Scaling the database before checking whether one query, plan regression, or missing index is consuming capacity.
• Looking only at average duration and ignoring high-frequency queries with the largest total resource cost.
• Sharing full query text or parameters in broad incident channels where sensitive data may be exposed.
• Tuning in development with tiny data volumes and assuming the result will hold in production.
• Forgetting that application retry behavior can multiply the impact of one slow dependency call.