Course Outline
01. Establishing the Development Environment
➡ SQL Server Configuration Manager.
➡ SQL Server Management Studio (SSMS).
➡ Configuring the database instance for this instructional module
➡ DBO permissions and data initialization
02. Monitoring Frameworks and Utilities
➡ SQL Server Profiler
➡ Extended Events (XEvents, XE).
➡ Activity Monitor
➡ Performance Monitor
➡ Data Collector (DC)
➡ Query Store (QS)
03. Catalog and Management System Views
➡ Frequently utilized Dynamic Management Views (DMV) and Functions (DMF) categories.
04. Database and Server Surveillance
➡ Resource utilization metrics: RAM, disk I/O, processor load, and network throughput
➡ Analysis of executed SQL statements
➡ Active session tracking
➡ Recent connection logs
➡ Identification of high-cost and blocked queries
➡ TEMPDB resource allocation
➡ Sessions consuming significant TEMPDB space
➡ Resource distribution and allocation
05. Fundamentals of Query Optimizer Mechanics
06. Index Management Principles
➡ Row-based indexes and classifications: CLUSTERED INDEX, NON-CLUSTERED INDEX
➡ Index selectivity assessment.
➡ Measuring operational execution time relative to index usage
➡ Server-recommended missing index suggestions
➡ Heap-structured tables (STERTA).
➡ Columnstore indexes: COLUMNSTORE INDEX
➡ COLUMNSTORE_ARCHIVE compression techniques.
07. Query Execution Plans (QUERY EXECUTION PLAN).
➡ Estimated Execution Plan: Theoretical cost analysis
➡ Actual Execution Plan: Observed execution details
➡ Retrieving and interpreting live query plans
➡ Distinguishing between INDEX SCAN and INDEX SEEK operations.
08. Statistics Management (STATISTICS)
➡ Architecture and operational logic of statistics
➡ Ongoing monitoring and maintenance procedures
➡ Addressing cardinality estimation discrepancies
➡ Classification of statistics types
09. Index Health Monitoring
➡ Analysis of index fragmentation
➡ Strategies for index reorganization and rebuilding
10. Parameter Sniffing and Code Recompilation Events
11. Identification of Common Performance Degradation Factors
Requirements
This training is tailored for database administrators and developers seeking to enhance their expertise in diagnostics and performance troubleshooting for SQL Server environments and associated applications.
Participants are expected to possess foundational knowledge of the Windows operating system and familiarity with the Microsoft SQL Server database platform.
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.