Detailed Course Outline
Architecture of MS SQL Server
- Connection protocols
- Layers of the database engine
- Query life cycle
SQL Server configuration
- Configuration of database services
- Storage concepts
- Memory configuration
- Processor configuration
- Additional server configurations
- Configuration manager for the interface
- TempDB configuration
Database Architecture
- Database components (Data and Log Files, File Groups, Extents, Pages)
- Page structure and page types
- Handling database files (file configuration, shrink file, etc.)
Indexes and Statistics
- Types of indexes
- Advantages and disadvantages of indexes
- Column-stored indexes
- Statistics and filtered statistics
Security
- Permission 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 backup encryption
- SQL injection
- Security for SSRS and SSAS
SQL Server Maintenance
- Maintenance strategies for indexes, statistics, and databases
- Automating maintenance jobs
- SSIS DB as storage for maintenance jobs
Identifying Performance Bottlenecks
- Causes of performance bottlenecks
- Strategies for identifying performance bottlenecks
- Using Perfmon for performance parameter analysis
- Overview of key performance parameters
- Event monitoring with Profiler and Extended Events
- Monitoring with DMVs and DBCC commands
- Data Collector
Querying and Execution Plans
- Query processing architecture
- Understanding and interpreting execution plans
- Using the Database Tuning Advisor to improve query performance
- Utilizing the Query Store
- Auto Tuning
SQL Optimization
- Optimizing SQL
- 10 tips for performant SQL
- Locking Behavior of Queries
- Transactions and isolation levels
- Identifying deadlocks
- Influencing execution plans with hints
- Procedures and materialized views
- Using CLRs