Microsoft SQL Server Query Performance Tuning and Optimisation

DURATION

2 Days

LOCATION

Exeter – Devon, Bristol, Gloucester or onsite at your premise.

OVERVIEW

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.

PRE-REQUISTES

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 utilisation 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

1. Introduction to Query Performance Tuning

  • Performance Tuning Overview
  • SQL Server Performance Killers

2. Resource Performance Analysis

  • Tools of the Trade
  • Memory Performance
  • Disk Performance
  • CPU Performance
  • Network performance
  • SQL Server Performance
  • Baselining Performance

3. Query Performance Analysis

  • Implementing Extended Events
  • Analysing Costly Queries
  • Analysing Execution Plans

4. SQL Server Indexes

  • 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

5. Index Management and Maintenance

  • What is Index Fragmentation?
  • Analysing Index Fragmentation
  • Resolving Fragmentation issues
  • Looking for Missing Indexes

6. Special Index Types

  • Full Text Indexes
  • Columnstore Indexes

7. Analysing and Managing Statistics

  • What are Statistics?
  • Analysing Statistics
  • Managing Statistics
  • How Statistics Affect Query Performance

8. Query Execution Plans

  • 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

9. Query Design Best Practices

  • Query Against Small Sets of Data
  • Make Effective Use of Indexes
  • The Effect of Optimizer Hints
  • Other Important Considerations

10. Blocking and Locking

  • Introducing Blocking and Locking Concepts
  • Lock Types, Operations, and Modes
  • Isolation Levels
  • Locking and Indexes
  • Analysing and Resolving Blocking Issues
  • Analysing and Resolving Deadlocks

11. SQL Server Optimization Checklist

  • Database Design Considerations
  • Configuration Settings
  • Database Administration
  • Query Design Checklist

TO BOOK

For more information or to book onto our Microsoft SQL Server Querying Performance tuning and Optimisation training course please contact us on 01392 824 022 or email training@amesolutions.co.uk.

If there is a Microsoft SQL Server course that is not listed on our site, that you are interested in attending then please let us know and we can arrange one to one training onsite with our Trainer.

Still not convinced?
We can help you!

Fill out the form below and one of our IT specialists will contact you.





Testimonials

 

 

 

PhoneEmailTwitterAME Support ToolApple Mac Support tool