MasterClass: SQL Server Performance Tuning and Optimization

This Masterclass suits Database administrators who are responsible for the proper and reliable operation of one or more SQL servers. If you would like to learn how to search and detect problems within SQL database, or if you would like to learn the QP and code Execution.

Level: 300 

Audience:

• Database administrators who are responsible for the proper and reliable operation of one or more SQL Servers
• Developers who want to create a good and scalable code by understanding how the internal processes works, the QP and code execution
• SQL Server architects and professionals who want to be successful in performance tuning of SQL Server environments, to empower themselves with knowledge, skills and methodology for finding, isolating and troubleshooting performance problems

Course objectives:

After attending this training students will be able to

• Find performance problems in SQL Server workload and tune them properly
• Know out of the box monitoring and profiling tools that SQL Server provides in different product versions
• Choose the right tool in order to get the right monitoring data for fast problem reaction
• Have a deep understanding of the architecture and the way SQL Server works in order to explore the problem root cause and to act properly on their production environments
• Configure SQL Server at all levels properly in order to avoid and reduce problems
• Get to the problem quickly in order to reduce the downtime in production environment
• Develop SQL code that is aligned to good coding practices and provides scalability and performance of the database access layer
• Avoid traps in code development and object usage
• Find/assess code that performs badly and to know how to optimize it
• Understand how session use temdb, the tempdb stores, recognize and avoid tempdb problems
• Use Waits and Queues methodology for performance monitoring and problem isolation, understand and know common wait types and the reasons they occur
• Recognize some common performance problems and reasons behind them by gaining practical experience in a lab environment
• Choosing and working with proper tools for isolating a performance problem
• Understand the query optimization process and how the QP process a query
• Read query plans, recommend optimizing queries trough different optimization techniques based on problems found in query plans
• Understand deeply indexes and statistics, understand when indexes can help and when to use other optimization technique
• Implement index assessment strategy for a database in order to improve and optimize indexes
• Find expensive queries, isolate coding problems, recommend a way to tune them
• Understand how to optimize data loading and use minimally logged operations for avoiding resource problems

Course outline:

• SQL Server Resource Management and important configurations, SQLOS
• TempDB and Database level Performance Tuning and Optimizations
• Waits and Queues Methodology for Performance Tuning and Optimization
• Specific Event based Monitoring and common PTO Scenarios
• Query Execution and optimization, exploring query plans
• The importance of statistics and indexes
• Query Tuning Methodology
• Optimizing Data Loading

Other relevant courses