Course Overview
This 5-day practical workshop is aimed at database administrators who already have basic knowledge of installing and managing MS SQL Server and want to learn more about configuration, optimization, and troubleshooting.
On the other hand, the course is aimed at developers who have SQL knowledge and now want to take the next step to learn, monitor, and optimize advanced functionalities such as stored procedures, transaction programming, and locking behavior.
All topics on the agenda are independently implemented in a separate virtual environment using practical exercises.
Who should attend
The target groups are database administrators who already have knowledge of installing and managing MS SQL Server and want to build on their expertise in the areas of configuration, optimization, and troubleshooting.
Also, developers who want to learn, monitor, and optimize advanced SQL functionalities such as stored procedures, transaction programming, and locking behavior.
Prerequisites
Knowledge in the following areas is desirable:
- Knowledge of MS SQL Server administration
- Knowledge of relational databases
- Knowledge of TSQL language
Course Objectives
Knowledge in the following areas is desirable:
- Knowledge of MS SQL Server administration
- Knowledge of relational databases
- Knowledge of TSQL language
Course Content
Architecture of the MS SQL Server
- connection logs
- Layer of the database engine
- Life Circle query
Configuring the SQL Server
- Configuring database services
- Storage concepts
- Memory configuration
- processor configuration
- Additional server configurations
- Surface Configuration Manager
- Configuring TempDB
database architecture
- Database components (data and log files, file groups, extents, pages)
- Page structure and page types
- Working with database files (file configuration, shrink file, etc.)
Indices and statistics
- Types of indexes
- Advantages and disadvantages of indices
- Column Stored Index
- Statistics and filtered statistics
safety
- Authorization concepts in SQL Server
- Creating logins, users, roles, and schemas
- Transferring logins
- Working with instances
- Always Encrypted
- Code and data encryption
- Dynamic Data Masking
- Server and database auditing
- Password protection and encryption of backups
- SQL Injection
- Security for SSRS and SSAS
Maintenance of the SQL Server
- Maintenance strategies for indexes, statistics, and databases
- Automation of maintenance jobs
- SSIS DB as storage location for maintenance jobs
Identifying performance bottlenecks
- Reasons for performance bottlenecks
- Strategies for identifying performance bottlenecks
- Using Perfmon to analyze performance parameters
- Overview of the most important performance parameters
- Monitoring events with the Profiler and Extended Events tools
- Monitoring with DMVs and DBCC statements
- Data Collector
Queries and execution plans
- Query processing architecture
- Understanding and interpreting implementation plans
- Using the Database Optimization Advisor to Improve Query Performance
- Benefits of the query store
- Auto Tuning
Optimizing SQL
- 10 tips for high-performance SQL
- Blocking behavior of queries
- Transactions and isolation levels
- Identifying deadlocks
- Influencing execution plans with hints
- Procedures and materialized views
- Use of CLRs