Get in Touch
 Duration 21 hours

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.

Number of participants


Price per participant

Testimonials (4)

Upcoming Courses

Related Categories