Table of Contents

1. Challenge

   Diagnose and optimize query performance

   • Analyze graphical and text-based query execution plans to identify performance bottlenecks.
   • Apply basic T-SQL query tuning techniques, such as adding appropriate indexes or rewriting queries.
   • Use Dynamic Management Views (DMVs) to identify long-running queries and resource consumption.
2. Challenge

   Implement and monitor automated administrative tasks

   • Configure SQL Server Agent and create a multi-step job for a recurring administrative task (for backups, for example).
   • Set up operators and alerts to receive notifications for job success or failure via Database Mail.
   • Create and schedule a comprehensive database maintenance plan for integrity checks and index optimization.
3. Challenge

   Troubleshoot and resolve common database health issues

   • Identify and resolve database fragmentation issues through index rebuilds or reorganizations.
   • Simulate and diagnose a blocking scenario, then identify and resolve the blocking process.
   • Perform a DBCC CHECKDB command to check for database integrity and understand its output.