Improving SQL Server performance is rarely a matter of hardware. It's more often a matter of how well you write queries and how efficiently you index them. Find out how to evaluate queries, identify errors and areas for improvement, depending on your situation.
Objectives
To provide the knowledge and skills required to optimize MS SQL Server to get the highest possible performance for complex and/or high-volume applications.
Is it for you ?
This course is aimed at database administrators, developers, operations or network managers, system engineers, professionals in development, databases, networks and Web specialists.
Prerequisite
Experience with MS SQL Server 2008 R2 administration (Courses MS509) ; Knowledge of SQL Server, database manipulation and SQL language ; experience as a database developer.
Your benefits
You will be able to optimize MS SQL Server databases to achieve high performance for complex or high-volume applications:
- The latest performance enhancements in SQL Server 2022.
- The most effective monitoring and measurement tools
- Query optimization through index management.
- Optimal use of server resources
- A working methodology for managing performance issues.
Content
Introduction to optimization concepts
- Introduction to performance optimization.
- The biggest causes of performance loss and the most common bottlenecks.
- Improving performance with SQL Server 2022.
- Optimizing SQL Server for the cloud and VMs.
- SQL health check checklist.
See more + / -
The optimizer and using an execution plan
- Understanding how the query estimator works.
- Role and operation of the optimizer: query optimization.
- Reading and understanding an execution plan.
Using performance monitoring tools
- Monitoring and measuring performance with the right tools: system store procedures, Query Store, Extended events, Dynamic system views (DMV).
Architecture and behavior of indexes and statistics
- Definition of an index, its role, possible types.
- Optimizing SQL Server with indexes (creation, options, structure, settings).
- Index behavior during query execution.
- Understanding fragmentation and fill factor.
- Index and statistics maintenance.
- Understanding and demystifying statistics.
- Improve decision-making with the right statistics.
Optimizing problematic query code
- Optimizing your queries and T-SQL code.
- SARGABLE and NON-Sargable.
- How to avoid bad queries.
- Tips and tricks 101.
Efficient use of resources
- Understanding the cardinality estimator enhancements after SQL Server 2012.
- The behavior, use and resource requirements of cached plans.
- Recompiling queries and procedures.
- Parameter-sensitive queries and parameter sniffing.
- Reducing server resource consumption.
- Query refinement: query plan and statistics.
Locks
- Locking, blocking and interlocking.
- Lock management with optimal isolation level.
- Lock escalation and deadlocks.
💡 Useful information
Our training sessions are offered in Montreal or Quebec City, in person or in virtual format. Dates and locations are provided when you select your session below. If you have any questions regarding registration, schedules, the language of instruction, or cancellation policies, please consult our FAQ .
Trainers
Private or personalized training
Do you have several employees interested in the same training course? Whether in person at your offices or remotely in virtual mode, we offer private training courses tailored to your team's needs. Group rates are available. Contact us for more details or request a quote online.
Request a quote