SQL Server Performance Optimization Course

Course Summary

The SQL Server Performance Optimization and Troubleshooting training course is designed to demonstrate the knowledge and skills to improve the performance of their SQL servers.

The course begins with a look at some common real-life performance problems based on customer case studies. Next, it explores techniques to scope performance issues using the top-down troubleshooting approach. The course concludes by examining many techniques for troubleshooting and improving performance.

Purpose

Learn to address common real-life performance problems both at the server and T-SQL code level. Learn about troubleshooting approaches, techniques, features and tools with SQL Server 2019 and Azure SQL Database.

Audience

Developers, data analysts, and database administrators who work with the SQL Server database platform on-premises or in Azure and troubleshoot performance related problems.

Role

Data Engineer - DevOps Engineer - Software Developer - System Administrator

Skill Level

Intermediate

Style

Workshops

Duration

5 Days

Related Technologies

Back-End Development Training

SQL

Cloud Computing Training

Azure

Download

Productivity Objectives:

Utilize the top-down troubleshooting approach that includes detailed problem scoping
Identify troubleshooting entry points with wait statistics
Apply troubleshooting approaches with different scopes in on-premises SQL Server and Azure database-as-a-service environments
Analyze a database environment and do health checks
Identify configuration and sizing problems including major red flags
Identify and troubleshoot resource utilization (CPU, memory, and IO) problems
Use tools and scripts to monitor and troubleshoot the server and your T-SQL workload
Utilize T-SQL coding best practices to avoid common performance and scalability bottlenecks and identify coding antipatterns
Utilize optimal indexing and identify indexing related performance problems
Analyze query plans to both prevent and identify performance and scalability problems at the T-SQL code level

Learn About Virtual Training

Request Information

Get your team upskilled or reskilled today. Chat with one of our experts to create a custom training proposal. Fully customized at no additional cost.

If you are not completely satisfied with your training class, we'll give you your money back.

about our training

Real-World Content

Project-focused demos and labs using your tool stack and environment, not some canned "training room" lab.
Expert Practitioners

Industry experts with 15+ years of industry experience that bring their battle scars into the classroom.
Experiential Learning

More coding than lecture, coupled with architectural and design discussions.
Fully Customized

One-size-fits-all doesn't apply to training teams. That's where we come in!

What You'll Learn

In the SQL Server Performance Optimization and Troubleshooting training course, you'll learn:

Common real-life performance problems based on customer case studies
Scoping the problem and the top-down troubleshooting approach
Characteristics and major differences of on-premises and Azure SQL Server environments
- (boxed product vs. SQL Server on Azure VM vs. Azure SQL Database vs. Azure SQL Managed Instance)
SQL Server resource utilization internals (CPU, memory, IO) and sizing
Installation and deployment problems, the importance of patching
Server configuration options and best practices
Database configuration options including scoped configurations and their impact on performance
The importance of Tempdb configuration and performance
Errorlog analysis to identify major server-level bottlenecks
Doing health-checks, well-known tools, and scripts
Wait types, wait statistics and troubleshooting patterns
Monitoring tools and common performance-related metrics
Extended Event tracing to identify workload related problems
Using the Query Store to historically analyze workload performance and identify bottlenecks
T-SQL coding antipatterns
- (scalar user-defined functions, cursors, extensive use of large temporary objects) and what to use instead (set-based approach, inline table-valued functions, tempdb performance considerations)
Query plan analysis
The importance of optimal indexing
- (index types, rowstore and columnstore indexes, index consolidation)
Troubleshooting blocking scenarios and deadlocks
SQL Server 2019 and Azure SQL Database specific performance improvements to help you with your troubleshooting and optimization efforts
- (tempdb improvements, Intelligent Query Processing features including Scalar UDF Inlining, automatic tuning features)