Course Overview
The Advanced SQL and Performance Optimization course provides an in-depth understanding of Microsoft SQL Server architecture, database internals, memory management, CPU scheduling, indexing, locking, and query optimization. It focuses on understanding database engine operations and identifying factors that affect SQL Server performance.
The course covers execution plan analysis, index and statistics maintenance, deadlock troubleshooting, wait statistics, performance monitoring, and resource management. Participants explore Dynamic Management Views (DMVs), SQL Profiler, Extended Events, Performance Monitor, Database Engine Tuning Advisor, Resource Governor, and Data Collector to analyze and troubleshoot database performance issues.
This is a customized SQL Server performance optimization curriculum covering SQL Server 2012, 2014, and 2016 concepts rather than a current official Microsoft course.
Course Objective
- Understand SQL Server architecture, database engine components, and storage mechanisms.
- Analyze SQL Server memory management and identify memory-related performance issues.
- Understand SQLOS scheduling, CPU utilization, and CPU performance bottlenecks.
- Analyze index architecture, index fragmentation, statistics, and index maintenance requirements.
- Understand locking mechanisms, transaction isolation levels, blocking, and deadlocks.
- Analyze query compilation, execution plans, query optimization, and execution plan caching.
- Evaluate query performance using execution plans, SQL hints, and query analysis techniques.
- Identify performance bottlenecks using wait statistics, DMVs, and monitoring tools.
- Monitor and optimize SQL Server, database, application, hardware, and operating system performance.
- Understand Resource Governor, Data Collector, and Extended Events for performance monitoring and resource management.
Pre-requisites
- Practical database administration and development experience with previous versions of SQL Server.
- Knowledge of SQL Server development and T-SQL coding.
- Experience as SQL Server administrators or developers.
- Familiarity with SQL Server 2012, 2014, or 2016 environments, particularly those considering or currently undertaking migration.
Course Curriculum
