A public-health blocked-session report shows long lock waits on encounter queries filtered by clinic_id with scans. What root cause should the SQL AI developer suspect?
Select an answer to reveal the explanation.
Short Explanation
If everyone is stuck behind a clinic_id scan, you’re missing the index that turns that hunt into a pointed lookup—blocking is the traffic jam; the missing index is the closed lane.
Full Explanation
A public-health blocked-session report showing long lock waits on encounter queries filtered by clinic_id that perform scans strongly suggests a missing supporting index on clinic_id (and likely covering columns). Without a selective seek path, each encounter query scans large ranges, holds locks longer under the isolation level in use, and blocks concurrent clinic writers or other readers—turning a missing-index problem into a blocking incident during clinic hours. Managed Identity rotation on the clinic API can cause auth failures; it does not create clinic_id scan-driven lock waits inside SQL. Dynamic Data Masking on clinic names affects result masking, not exclusive schema locks that explain encounter blocking. SDK-style Database Project builds run in CI/build agents; they do not hold production row locks during clinic operations. Exam caveat: blocking may also involve lock escalation on large scans—indexing reduces rows touched and often prevents escalation. RCSI can reduce reader/writer blocking but will not fix the underlying scan cost; still add the clinic_id index. Verify the predicate is SARGable (no wrapping functions on clinic_id) or the new index will not be used. Operational check: from a blocked encounter session, inspect the wait resource and the query plan for a scan on the encounter table with clinic_id filter; create a nonclustered index on clinic_id INCLUDING columns needed to avoid lookups; update statistics; re-run clinic_id queries and blocking reports to confirm seeks replace scans and lock wait durations drop.