About This Course
This advanced SQL Server course is designed for database professionals who want to deepen their expertise in performance tuning, language enhancements, and tooling within modern SQL Server environments. Through a balanced mix of theoretical insights, hands-on labs, and real-world scenarios, participants will gain practical knowledge essential for optimizing and managing enterprise-grade SQL Server instances.
The course is structured into three core modules:
- Performance (20%): Learn about the latest concurrency and scalability improvements including system page latch enhancements, optimized buffer pool scans, and spinlock algorithms. Explore in-memory OLTP, virtual log file growth, and query execution enhancements such as DOP feedback and optimized plan forcing. This module includes a hands-on lab to reinforce key performance strategies.
- Language (20%): Dive into new and updated T-SQL features such as resumable DDL operations, enhanced index creation options, and advanced JSON and time series functions. Learn how to work with new SQL syntax and functions, along with improved replication and statistics handling. Practical labs provide an opportunity to apply these language enhancements in real scenarios.
- Tools (10%): Get up to speed with essential database tools including Azure Data Studio, SSMS, SQL Server PowerShell, and diagnostic utilities like Profiler and Extended Events. Gain hands-on experience with performance monitoring and management tools used by database administrators and developers.
In addition to structured modules, the course features real-life scenarios and tips covering locks, replication, SSIS, PowerShell automation, and practical SSMS usage. Participants will learn proven strategies for performance measurement, system monitoring, and issue troubleshooting in live environments.
Whether you're managing on-premises systems or working in hybrid cloud architectures, this course equips you with the knowledge and skills to improve SQL Server performance, streamline development, and enhance operational efficiency.
Audience Profile
This course is designed for database professionals & database developers who are responsible for the performance, scalability, and reliability of SQL Server environments. It is ideal for:
- Database Administrators (DBAs) looking to deepen their understanding of advanced performance tuning and system internals.
- Database Developers who want to leverage the latest T-SQL enhancements and language features.
- Data Engineers and Architects involved in designing high-performance, scalable data solutions.
- IT Professionals and System Administrators who manage SQL Server infrastructure and need to monitor, troubleshoot, and optimize operations.
- Power Users and Analysts seeking to improve efficiency using advanced tools like SSMS, Azure Data Studio, and PowerShell.
- Individuals transitioning into database roles or those looking to learn SQL Server as part of their career development in data management and analytics.
Participants should have a solid foundation in SQL Server, including familiarity with database concepts, T-SQL, and basic performance tuning. Prior hands-on experience with SQL Server tools and features is recommended, but motivated learners preparing for a career in SQL Server will also benefit greatly from the course.
Prerequisites
To get the most out of this course, participants should meet the following prerequisites:
- Basic knowledge of SQL Server: Familiarity with database objects such as tables, indexes, views, and stored procedures.
- Experience with T-SQL: Understanding of writing and reading Transact-SQL queries, including SELECT, INSERT, UPDATE, DELETE, and basic joins.
- Understanding database concepts: Such as normalization, indexing, transactions, and query execution.
- Familiarity with SQL Server tools: Prior exposure to SQL Server Management Studio (SSMS), Azure Data Studio, or similar database tools is helpful.
- Basic Windows and server administration skills: Comfortable navigating the Windows environment and understanding system-level configurations.
While prior experience with performance tuning or advanced database operations is not mandatory, it will be beneficial. Motivated learners new to SQL Server but pursuing a career in database administration or development are also encouraged to enroll.
At the End of the Course, Participants Will Be Able To:
- Analyse and improve SQL Server performance using advanced tuning techniques and tools.
- Understand and manage system concurrency with enhanced latch and spinlock mechanisms.
- Configure and utilize in-memory OLTP, virtual log file growth management, and DOP feedback.
- Apply new T-SQL features, including JSON functions, time series analysis, and enhanced indexing options.
- Create, modify, and optimize database objects using the latest language enhancements.
- Use tools like SSMS, Azure Data Studio, PowerShell, and Extended Events to monitor and troubleshoot SQL Server environments.
- Implement and maintain transactional replication and understand its real-world use cases.
- Apply practical techniques for handling locks, replication issues, and SSIS integration.
- Automate tasks and performance monitoring using scripting and built-in SQL Server capabilities.
- Leverage real-life examples, best practices, and hands-on experience to solve complex SQL Server challenges in enterprise environments.
Course Outline
Performance
- System page latch concurrency enhancements
- Buffer pool parallel scan
- Ordered clustered column store index
- In-memory OLTP management
- Virtual log file growth
- Enhanced spinlock algorithms
- Degree of parallelism (DOP) feedback
- Optimized plan forcing
- Locks mechanism
- Lab – hands on
Language
- Resumable add table constraints
- CREATE INDEX (WAIT_AT_LOW_PRIORITY with online index operations clause)
- Transactional replication
- CREATE STATISTICS (Adds AUTO_DROP option)
- IS [NOT] DISTINCT FROM
- Time series functions
- JSON functions
- Aggregate functions
- T-SQL functions
- Lab – hands on
Tools
- Azure Data Studio
- SQL Server Management Studio
- SQL Server PowerShell
- Profiler\Extended Events\Performance monitor
- Lab – hands on
Real Life Scenarios, samples and Tips
- Locks
- Replication
- SSIS
- PowerShell
- SSMS 2019 – tricks & tips
- Measure performance
- Monitor
