Ms SQL Server - Query Optimization

The training is designed for both database administrators and developers who want to expand their competencies to include diagnostics and performance troubleshooting in the area of SQL Server operations and applications running on it.
Trainees are required to have knowledge of the Windows environment and familiarity with the Microsoft SQL Server database environment.

This training is designed to introduce the knowledge that allows you to configure the database server according to your requirements, and to create appropriate procedures for monitoring the functioning of its various components, as well as to quickly respond to any irregularities that arise. After completing this training, the participant will be able to effectively diagnose the sources of performance problems in the operation of SQL Server and apply various types of implementations leading to the removal of so-called "bottlenecks".

Upon completion of the training, the participant:
▪ will be able to diagnose so-called "bottlenecks" in the operation of the database and/or server
▪ will be able to monitor the operation of SQL Server
▪ will know what to recommend to improve the performance of the database and server.

01. PREPARING THE DEVELOPMENT ENVIRONMENT
   ➡ SQL Server Configuration Manager.
   ➡ SQL Server Management Studio (SSMS).
   ➡ Setting up the database for this training course
   ➡ 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
   ➡ Most commonly used DMV and DMF categories.

04. DATABASE AND SERVER MONITORING
   ➡ Utilization of RAM, disks, processors, network interfaces
   ➡ Checking executed SQL queries
   ➡ Active sessions
   ➡ Recent connections
   ➡ Most expensive and blocked queries
   ➡ TEMPDB space
   ➡ Sessions using the most space in TEMPDB
   ➡ Resource allocation

05. PRINCIPLES OF QUERY OPTIMIZER OPERATION

06. PRINCIPLES OF INDEXES
   ➡ Row indexes and their types: CLUSTERED INDEX, NON-CLUSTERED INDEX
   ➡ Index selectivity index.
   ➡ Measuring the execution time of database operations based on the use of indexes
   ➡ 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
   ➡ Running and reading query plans
   ➡ INDEX SCAN and INDEX SEEK operations.

08. STATISTICS (STATISTICS)
   ➡ Construction and operation principle of statistics
   ➡ Monitoring and maintenance of statistics
   ➡ Errors of cardinality estimation
   ➡ Types of statistics

09. MONITORING OF INDICES
   ➡ Fragmentation of indexes
   ➡ Reorganization and reconstruction of indexes

10. PARAMETER SNIFFING AND CODE RECOMPILATIONS

11. MOST COMMONLY USED PERFORMANCE DEGRADING CONSTRUCTS