This study guide brings together major SQL Server administration concepts: measuring performance, using indexes, diagnosing slow queries, avoiding inefficient cursors, planning scalability, and protecting recoverability.
Performance investigation
Capture a reproducible slow query, its duration and logical reads, then inspect execution plans, Query Store, wait statistics, and appropriate dynamic management views. Establish baselines before tuning.
Queries and indexes
Retrieve only needed columns and rows, keep predicates sargable when possible, and create indexes based on observed query patterns. Every index adds storage and write overhead.
Cursor alternatives
A cursor loops through records; a set-based UPDATE, window function, or APPLY expression often expresses the same requirement more efficiently. If a cursor is unavoidable, restrict its input and clean up resources.
Availability versus recovery
Failover replicas and RAID improve certain availability scenarios but do not replace versioned backups. Plan restore tests, retention, log backup requirements, and clear recovery objectives.
Study checklist
Explain scaling up versus out; identify common wait categories; describe full, differential, and log backups; distinguish clustered and nonclustered indexes; and outline a measured tuning experiment.
AI, sources and reuse: This is an AI-generated rewrite informed by the original Open Education post (#69274), with additional explanations and examples. The new article text may be copied, adapted, and republished with or without a reference to this site. Third-party resources and trademarks keep their own rights. AI can make mistakes. Verify version support, code correctness, and security practices against Microsoft documentation, and test with nonproduction data first.
