Memory hook: Protect rows, protect keys, inspect waits.
Must remember
- Use supported passwordless identity and least-privileged database permissions. Object grants, roles and row-level security control access; Dynamic Data Masking limits displayed values for selected users but is not encryption.
- Always Encrypted protects supported columns with client-side key handling; column encryption and TDE address different layers. Key custody and application compatibility determine the right design.
- Secure model, REST, GraphQL and MCP endpoints separately from the database. Managed identities reduce secrets but still need narrowly granted permissions; audit sensitive operations without leaking data.
- Isolation levels trade blocking, consistency and version-store behavior. Use transactions only as long as necessary and consistent update ordering; deadlocks require analysis of competing resources, not endless retries.
- Isolation recall: READ UNCOMMITTED permits dirty reads; READ COMMITTED prevents them but can allow nonrepeatable reads/phantoms; REPEATABLE READ retains protection for read rows but can allow new matching rows; SERIALIZABLE also protects key ranges. SNAPSHOT uses a transaction-level versioned view and can raise update conflicts. READ COMMITTED with READ_COMMITTED_SNAPSHOT enabled uses statement-level row-versioning, which differs from SNAPSHOT. Platform defaults vary; inspect database settings before diagnosing lock behavior.
- Query Store records query/plan/runtime history; execution plans and DMVs expose scans, estimates, waits and resource pressure. Query Performance Insight and Azure monitoring provide supported views.
- Tune query shape, indexes, statistics and configuration from measured evidence. Parameterization improves safety but parameter-sensitive plans still need diagnosis.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Users may see only their tenant’s rows | Enforced row-level security plus appropriate identity and permissions. |
| Sensitive columns must remain hidden from the server in supported operations | Evaluate Always Encrypted and client key requirements. |
Traps
- Masking is not a substitute for controlling SELECT and privileged access.
- Raising compute may not solve a deadlock caused by inconsistent update order.
Active recall
1. What does Query Store retain?
Query, plan and runtime information useful for detecting regressions.
2. What is a deadlock?
A cycle of sessions waiting for resources held by each other.
3. Why keep transactions short?
To reduce lock duration, contention and failure impact.
4. What must a managed identity still receive?
The specific database/API permissions needed for its operation.
5. How validate tuning?
Compare representative plans, latency, resource use and correctness before and after.