Course Outline
01. PREPARING THE DEVELOPMENT ENVIRONMENT
• SQL Server Configuration Manager.
• SQL Server Management Studio (SSMS).
• Configuring the database for this training
• DBO and data preparation
02. MONITORING MECHANISMS AND TOOLS
• SQL Server Profiler
• Extended Events (XEvents, XE).
• Activity Monitor
• Performance Monitor
• Data Collector (DC)
• Query Store (QS)
03. CATALOG AND MANAGEMENT SYSTEM VIEWS
• Key DMV and DMF categories and their usage.
04. DATABASE AND SERVER MONITORING
• Tracking RAM, disk, processor, and network interface utilization
• Reviewing executed SQL queries
• Monitoring active sessions
• Analyzing recent connections
• Identifying the most expensive and blocked queries
• Monitoring TEMPDB space
• Sessions consuming the most TEMPDB space
• Resource allocation analysis
05. PRINCIPLES OF QUERY OPTIMIZER OPERATION
06. PRINCIPLES OF INDEXES
• Row indexes and their types: CLUSTERED INDEX, NON-CLUSTERED INDEX
• Index selectivity concepts.
• Measuring database operation execution time via index usage
• Server suggestions for missing indexes
• Tables of type HEAP (STERTA).
• Columnar indexes: COLUMNSTORE INDEX
• COLUMNSTORE_ARCHIVE compression.
07. QUERY EXECUTION PLANS (QUERY EXECUTION PLAN).
• Estimated Execution Plan: Estimated Execution Plan
• Actual Execution Plan: Actual Execution Plan
• Executing and analyzing query plans
• INDEX SCAN and INDEX SEEK operations.
08. STATISTICS (STATISTICS)
• Principles of statistics construction and operation
• Monitoring and maintenance of statistics
• Cardinality estimation errors
• Types of statistics
09. MONITORING OF INDICES
• Index fragmentation
• Reorganizing and rebuilding indexes
10. PARAMETER SNIFFING AND CODE RECOMPILATIONS
11. MOST COMMONLY USED PERFORMANCE DEGRADING CONSTRUCTS
Requirements
This course is intended for database administrators and developers seeking to broaden their skill set in diagnostics and performance troubleshooting for SQL Server environments and associated applications. Trainees are expected to have a solid understanding of the Windows environment and familiarize themselves with the Microsoft SQL Server database ecosystem.
Testimonials (4)
data was personalised to our organisations
Vincent Long - ASSMANG PTY LTD
Course - T-SQL Fundamentals with SQL Server Training Course
personalised to our understanding and data
Vincent Long - ASSMANG PTY LTD
Course - Business Intelligence with SSAS
The training was well structured and interactive
kgotla Moncho - Martin Engineering Africa
Course - MS 20761 : Querying Data with Transact SQL
The instructor brought his A game again as he superbly took my staff through the customized training with expert timing, knowledge, support, and rapport with my staff.
