SQL Server Performance Monitoring Basics
Will my SQL Server keep up with the load of tomorrow?
This class will give you tools that enable you to see performance trends and identify problems before they become problems.
On the way to this goal we will stop at these stations:
- An introduction to the ACID properties with a deeper look at Isolation including an introduction to locks and latches.
- A short look at the query lifecycle with the query compilation and the three query execution phases.
- A visit to blocking, showing ways to monitor for blocking occurrences.
- An explanation of performance related DMVs.
The class closes out by introducing a tool that allows monitoring the covered concepts on all your servers from a central place, the Management Data Warehouse.
After attending the class, you will be able to:
- describe ACID and its components and explain what its impact on concurrency is
- differentiate between locks and latches
- explain the query lifecycle
- identify blocked and blocking sessions and their SQL statements
- use DMVs to get a picture of your server health
- monitor important SQL Server performance metrics over time using the Management Data Warehouse
- see important trends in your performance metrics