SQL
SQL Server diagnostics, autonomous.
dbexpertAI diagnoses SQL Server autonomously with detection paths for parameter sniffing, blocking chains, tempdb contention, index health, Always On lag, and memory-grant pressure — root cause plus resolution steps every 90 seconds.
SQL Server exposes excellent diagnostics — DMVs, Query Store, wait stats — and its failure modes are famously repeatable: parameter sniffing after a stats update, tempdb allocation contention, a blocking chain behind an uncommitted transaction. Repeatable is encodable; the path library reads the DMVs so your on-call doesn't have to.
Failure modes we detect
How SQL Server actually fails in production
Parameter sniffing after plan eviction
A recompile or stats update caches a plan optimized for an unrepresentative parameter; every other parameter value inherits it. The stored procedure that ran in 50ms now runs in 30s for most callers — Query Store has the before/after, if something correlates it.
Blocking chains and lock escalation
A head blocker (often an idle, uncommitted transaction) queues dozens of sessions; under volume, row locks escalate to table locks and widen the blast radius. The chain, its head, and the escalation events are all in the DMVs in real time.
tempdb contention
PAGELATCH waits on allocation pages (the classic 2:1:1) under heavy temp-table or spill activity — the whole-instance slowdown that traces to tempdb file layout, trace-flag posture, or one query spilling terabytes.
Memory-grant pressure and RESOURCE_SEMAPHORE waits
Misestimated hash joins request giant grants; concurrency makes queries queue for memory before they even start. The evidence (grant sizes vs. estimates, semaphore waits) points at the specific misestimating queries.
Always On availability-group lag and redo queues
Secondary redo falls behind under write bursts — readable secondaries serve stale data and failover RPO quietly degrades. Send-queue vs. redo-queue decomposition names whether the network or the secondary is the bottleneck.
A real diagnosis, walked through
What a SQL Server detection path delivers
// symptom
The main customer-lookup procedure intermittently takes 20–40s. Restarting the app "fixes" it for a while.
// diagnostic evidence
- Query Store: plan for the procedure regressed at 11:03; prior plan avg 60ms, current avg 28s
- the 11:03 plan was compiled for a parameter matching a single huge enterprise account
- current plan does a key-lookup loop suited to 100 rows, executed against millions
- app restarts 'fix' it because plan-cache eviction lets a representative parameter recompile it
// root cause
Classic parameter sniffing: a stats-update-triggered recompile happened to be for an outlier account, caching a plan that is wrong for the general population. The app restart was treating the symptom by flushing the cache.
// resolution steps
- Immediate: force the known-good plan via Query Store
- Rewrite the procedure with the appropriate strategy (OPTIMIZE FOR representative value, or RECOMPILE on the skewed path — evidence includes the parameter distribution to choose)
- Alert on Query Store plan regressions for the top-20 procedures so the next flip is caught in 90 seconds
Composite walkthrough drawn from the SQL Server detection-path library — the shape of every diagnosis: symptom, evidence, root cause, resolution. Yours will name your tables.
Path library coverage
What the SQL Server detection paths watch
- Query Store: plan regressions, forced-plan health, parameter-sniffing signatures
- Blocking: chain reconstruction with head-blocker identity, lock escalation, deadlock graphs
- tempdb: allocation contention, spill attribution, file-layout posture
- Memory: grant pressure, semaphore waits, buffer-pool health, misestimate attribution
- Always On: send/redo queue decomposition, failover readiness, readable-secondary staleness
- Index and statistics health: fragmentation that matters, stale stats on hot objects, missing-index consolidation
Connect your SQL Server — free.
Read-only agent, on-prem, 90 seconds to your first diagnosis. Your first database is free for life — make it the SQL Server that hurts.
First database free. For life.
Runs on Windows, macOS and Ubuntu — inside your network, read-only. Enterprise fleet or want humans in the loop? Talk to us.