Microsoft SQL Server Query Performance Tuning & Optimization DURATION: 2 Days
Course Overview
INTRODUCTION This 2 day course is intended for students who are already competent at querying data in a Microsoft SQL Server database, and need to take their skills to the next level. This course would be suitable for anyone who wants to more clearly understand the inner workings of Microsoft SQL Server from a performance perspective. After taking this course, students will be able to analyse the performance of their queries, and the database engine, and be able to rectify various performance related problems. You will find that this is a comprehensive course for this level, with plenty of illustrated examples and augmented with practical hands-on exercises.
PREREQUISITES
Students should be familiar with working in a Microsoft Windows Server operating system environment. Delegates should also understand the fundamental design of relational databases including, but not restricted to, the purpose of primary and foreign keys, entity relationships, and data normalization. A good working knowledge of the SQL language will also be assumed.
This is an advanced level course, and attendees should feel confident querying and analysing data in a Microsoft SQL Server environment.
COURSE OBJECTIVES
Upon successful completion of this course, students will be able to:
- Understand the issues related to querying data that affect performance
- Use the right tools and utilities for analysing various facets of performance
- Measure the utilization of system resources
- Analyse the performance of queries
- Understand and implement different indexing architectures
- Understand how indexing affects query performance
- Manage and maintain indexes
- Understand and implement Full Text indexes
- Understand and implement Columnstore indexes
- Analyse and manage statistics
- Understand how statistics affect query performance
- Examine and interpret query execution plans
- Fine tune queries for performance
- Understand the effect that locking and blocking can have on performance
- Resolve issues related to locking and blocking
- Configure the server and database environment for optimal performance
- Implement best practices when writing queries
COURSE OUTLINE
Performance Tuning Overview SQL Server Performance Killers
Tools of the Trade Memory Performance Disk Performance CPU Performance Network performance SQL Server Performance Baselining Performance
Implementing Extended Events Analysing Costly Queries Analysing Execution Plans
Introducing SQL Server Row Indexes Index Design Considerations Clustered Indexes Nonclustered Indexes Comparing Clustered and Nonclustered Indexes Indexing Strategies and Techniques Using the Database Engine Tuning Advisor Analysing and Resolving Key Lookup Issues
What is Index Fragmentation? Analysing Index Fragmentation Resolving Fragmentation issues Looking for Missing Indexes
Full Text Indexes Columnstore Indexes
What are Statistics? Analysing Statistics Managing Statistics How Statistics Affect Query Performance |
What are Execution Plans? How Execution Plans are Generated Analysing the Execution Plan Cache The Effect of Ad Hoc Workloads Execution Plan Best Practices Parameter Sniffing Analysing Query Recompilation Issues Ways to Avoid Query recompilation
Query Against Small Sets of Data Make Effective Use of Indexes The Effect of Optimizer Hints Other Important Considerations
Introducing Blocking and Locking Concepts Lock Types, Operations, and Modes Isolation Levels Locking and Indexes Analysing and Resolving Blocking Issues Analysing and Resolving Deadlocks
Database Design Considerations Configuration Settings Database Administration Query Design Checklist |
Microsoft SQL Server Query Performance Tuning & Optimization DURATION: 2 Days
Course Overview
INTRODUCTION This 2 day course is intended for students who are already competent at querying data in a Microsoft SQL Server database, and need to take their skills to the next level. This course would be suitable for anyone who wants to more clearly understand the inner workings of Microsoft SQL Server from a performance perspective. After taking this course, students will be able to analyse the performance of their queries, and the database engine, and be able to rectify various performance related problems. You will find that this is a comprehensive course for this level, with plenty of illustrated examples and augmented with practical hands-on exercises.
PREREQUISITES
Students should be familiar with working in a Microsoft Windows Server operating system environment. Delegates should also understand the fundamental design of relational databases including, but not restricted to, the purpose of primary and foreign keys, entity relationships, and data normalization. A good working knowledge of the SQL language will also be assumed.
This is an advanced level course, and attendees should feel confident querying and analysing data in a Microsoft SQL Server environment.
COURSE OBJECTIVES
Upon successful completion of this course, students will be able to:
- Understand the issues related to querying data that affect performance
- Use the right tools and utilities for analysing various facets of performance
- Measure the utilization of system resources
- Analyse the performance of queries
- Understand and implement different indexing architectures
- Understand how indexing affects query performance
- Manage and maintain indexes
- Understand and implement Full Text indexes
- Understand and implement Columnstore indexes
- Analyse and manage statistics
- Understand how statistics affect query performance
- Examine and interpret query execution plans
- Fine tune queries for performance
- Understand the effect that locking and blocking can have on performance
- Resolve issues related to locking and blocking
- Configure the server and database environment for optimal performance
- Implement best practices when writing queries
COURSE OUTLINE
Performance Tuning Overview SQL Server Performance Killers
Tools of the Trade Memory Performance Disk Performance CPU Performance Network performance SQL Server Performance Baselining Performance
Implementing Extended Events Analysing Costly Queries Analysing Execution Plans
Introducing SQL Server Row Indexes Index Design Considerations Clustered Indexes Nonclustered Indexes Comparing Clustered and Nonclustered Indexes Indexing Strategies and Techniques Using the Database Engine Tuning Advisor Analysing and Resolving Key Lookup Issues
What is Index Fragmentation? Analysing Index Fragmentation Resolving Fragmentation issues Looking for Missing Indexes
Full Text Indexes Columnstore Indexes
What are Statistics? Analysing Statistics Managing Statistics How Statistics Affect Query Performance |
What are Execution Plans? How Execution Plans are Generated Analysing the Execution Plan Cache The Effect of Ad Hoc Workloads Execution Plan Best Practices Parameter Sniffing Analysing Query Recompilation Issues Ways to Avoid Query recompilation
Query Against Small Sets of Data Make Effective Use of Indexes The Effect of Optimizer Hints Other Important Considerations
Introducing Blocking and Locking Concepts Lock Types, Operations, and Modes Isolation Levels Locking and Indexes Analysing and Resolving Blocking Issues Analysing and Resolving Deadlocks
Database Design Considerations Configuration Settings Database Administration Query Design Checklist |
