Kurs SQL Server Masterclass 2022 (19 godzin)

looks like you're outlining a comprehensive curriculum for advanced SQL Server management and performance optimization, including a variety of topics such as T-SQL functions, backup and restore strategies, handling corruption issues, and improving server performance with indexing. Your plan also includes new content for 2021 that covers additional areas like configuring and modifying database settings, securing the server with proper configurations, administering the server using PowerShell, optimizing stored procedures, and working with SQL Azure.

Here's a breakdown of your curriculum:

First Chapter: Advanced T-SQL (5 godzin kursu)

1. SELECT and WHERE Clause
   • LIKE, IN, EXISTS vs JOIN performance differences, BETWEEN or <> versus NOT BETWEEN, and using parentheses to control logic evaluation order
2. Subqueries & Common Table Expressions (CTEs)
   • Understanding different types of subqueries, recursive CTEs for hierarchical data, and performance considerations
3. Aggregates & Window Functions
   • Using SUM, AVG, MIN, MAX, COUNT aggregates with GROUP BY and understanding the performance implications of window functions like ROW_NUMBER(), SUM() OVER (...), etc.
4. T-SQL Functions
   • Understanding scalar, inline, and multi-statement table-valued functions; best practices for function design and when to use an indexed view versus a function
5. Performance Optimization Techniques
   • Indexing strategies, query tuning using EXPLAIN and INDEX, optimizing statistics, and understanding how the SQL Server optimizes queries
6. Advanced JOINs
   • Understanding different join types (nested loop, hash, merge), and when to use each type for performance reasons
7. Temporary Tables
   • Usage scenarios for global versus local temporary tables, memory optimization using table variables, and performance comparison
8. Transactional vs Non-Transactional Operations
   • Understanding transaction isolation levels, READ COMMITTED SNAPSHOT, and NOLOCK with its caveats
9. Error Handling in T-SQL
   • Using TRY...CATCH blocks to handle exceptions gracefully and maintain performance
10. Advanced Security Features
    • Row-level security (RLS), dynamic management views for security, and performance impacts

Second Chapter: Backup & Recovery (3 godzin kursu)

1. Backup Fundamentals
   • Types of backups (full, differential, transaction log), backup models (full, differential, incremental), and how to schedule them using SQL Server Agent or maintenance plans
2. Recovery Strategies
   • Understanding RESTORE commands, point-in-time recovery (PITR), and how to implement different recovery strategies based on business needs
3. Database Integrity Checks
   • Using DBCC CHECKDB and DBCC CHECKTABLE to maintain data integrity and performance considerations during checks
4. Automation of Backup & Recovery Processes
   • Setting up automated backup verification, monitoring, and alerting mechanisms

Third Chapter: How to Improve Wydajność on Serwerze SQL (3 godzin kursu)

1. Optimization with Indexing
   • Creating indexes, clustered vs non-clustered, filtered indexes, and understanding the impact of FILLFACTOR
2. Query Performance Tuning
   • Analyzing execution plans, identifying bottlenecks, and optimizing queries using indexes and proper use of joins
3. Procedures and Functions Optimization
   • Analyzing performance of stored procedures and functions, optimizing batch sizes, and understanding the impact of memory usage
4. Configuration for Performance
   • Query store, resource governance, and optimizing configuration settings like MAXDOP, Cost Threshold for Parallelism, and other DMVs and dynamic configurations
5. Caching and Buffer Pool Configuration
   • Understanding the memory hierarchy in SQL Server (buffer pool, cache manager, etc.), and configuring memory settings for optimal performance
6. Disk I/O Optimization
   • RAID configurations, storage layout considerations, and understanding the impact of I/O patterns on performance
7. Optimizing Statistics & Query Plan Caching
   • Automatic and manual update statistics, DISABLE_QUERY_OPTIMIZER hint, and query plan caching best practices
8. Hardware Considerations
   • Understanding the role of CPU, RAM, storage, and network in SQL Server performance and how to size these components based on workload requirements
9. SQL Server Maintenance Plans
   • Setting up maintenance plans for index reorganize/rebuild, updating statistics, and database integrity checks
10. Performance Monitoring & Alerting
    • Using SQL Server Management Studio (SSMS), SQL Server Profiler, and third-party tools like SolarWinds or Redgate to monitor performance and set up alerts for potential issues

2021 Additions

1. Database Settings Configuration and Modification
2. Advanced Security Features and Performance Implications
3. Automation of Backup & Recovery Processes
4. Performance Monitoring & Alerting

This curriculum provides a comprehensive overview of the skills needed to manage, maintain, and optimize the performance of SQL Server databases. It covers a wide range of topics from basic to advanced levels and includes practical examples and real-world scenarios that professionals in the field encounter daily.