SQL Server 2017: Diagnosing Performance Issues with DMVs
Learn how to easily query SQL Server 2017 for performance information to help identify and fix issues that can affect performance and stability. This course is applicable to anyone responsible for SQL Server 2017 and earlier versions.
What you'll learn
It is very common for SQL Server instances to have workload performance problems in one or more ways. In this course, SQL Server 2017: Diagnosing Performance Issues with DMVs, you will see more than 45 DMV queries you can use to easily examine performance information related to your workload, instance health, and storage health. First, you will learn how to detect and correct many common performance issues related to the overall activity on the SQL Server instance and around the storage subsystem. Next, you will be shown how to investigate memory and CPU issues at the instance level. Finally, you will see how to drill into the database level and investigate performance issues there. By the end of this course, you will be able to find and correct the most common SQL Server performance issues. This course is perfect for anyone who is responsible for a SQL Server 2017 instance, with most topics also applicable to SQL Server 2016 and earlier versions.
Table of contents
- Introduction and Version Information 2m
- Demo: Version Information 2m
- Hardware Information 2m
- Demo: Hardware Information 2m
- Average Task Counts 2m
- Demo: Average Task Counts 1m
- Top Waits 2m
- Demo: Top Waits 3m
- Detect Blocking 1m
- Demo: Detect Blocking 2m
- Version Store Space Usage 1m
- Demo: Version Store Space Usage 1m
- Top Average Elapsed Time Queries 2m
- Demo: Top Average Elapsed Time Queries 1m
- UDF Statistics by Database 2m
- Demo: UDF Statistics by Database 1m
- Missing Indexes for All Databases 2m
- Demo: Missing Indexes for All Databases 4m
- Connection Counts by IP Address 1m
- Demo: Connection Counts by IP Address 1m
- Module Summary 1m
- Introduction and System Memory 3m
- Demo: System Memory 1m
- Process Memory 1m
- Demo: Process Memory 1m
- Total Buffer Usage by Database 2m
- Demo: Total Buffer Usage by Database 1m
- PLE by NUMA Node 2m
- Demo: PLE by NUMA Node 1m
- Memory Grants Pending 1m
- Demo: Memory Grants Pending 1m
- Memory Clerk Usage 2m
- Demo: Memory Clerk Usage 2m
- Ad Hoc Queries 1m
- Demo: Ad Hoc Queries 2m
- Top Logical Reads Queries 1m
- Demo: Top Logical Reads Queries 2m
- Module Summary 1m
- Introduction and Processor Description 2m
- Demo: Processor Description 1m
- CPU Utilization History 2m
- Demo: CPU Utilization History 1m
- CPU Utilization by Database 1m
- Demo: CPU Utilization by Database 1m
- Top Worker Time Queries 2m
- Demo: Top Worker Time Queries 2m
- SP Worker Time 2m
- Demo: SP Worker Time 2m
- High Aggregate CPU Queries 2m
- Demo: High Aggregate CPU Queries 2m
- Module Summary 1m
- Introduction and Lock Waits 3m
- Demo: Lock Waits 1m
- Scalar UDF Statistics 1m
- Demo: Scalar UDF Statistics 1m
- Input Buffer 1m
- Demo: Input Buffer 1m
- Query Execution Counts 2m
- Demo: Query Execution Counts 2m
- SP Execution Counts 1m
- Demo: SP Execution Counts 1m
- SP Average Elapsed Time 2m
- Demo: SP Average Elapsed Time 1m
- Bad Nonclustered Indexes 2m
- Demo: Bad Nonclustered Indexes 1m
- Missing Indexes 2m
- Demo: Missing Indexes 3m
- Missing Index Warnings 2m
- Demo: Missing Index Warnings 1m
- Overall Index Usage - Reads 2m
- Demo: Overall Index Usage - Reads 2m
- Overall Index Usage - Writes 1m
- Demo: Overall Index Usage - Writes 1m
- Volatile Indexes 2m
- Demo: Volatile Indexes 1m
- Module Summary 1m
- Introduction and Buffer Usage 3m
- Demo: Buffer Usage 1m
- SP Logical Reads 1m
- Demo: SP Logical Reads 1m
- High Aggregate Logical Reads Queries 1m
- Demo: High Aggregate Logical Reads Queries 2m
- I/O Statistics by File 2m
- Demo: I/O Statistics by File 2m
- SP Physical Reads 2m
- Demo: SP Physical Reads 1m
- SP Logical Writes 2m
- Demo: SP Logical Writes 1m
- Top I/O Statements 1m
- Demo: Top I/O Statements 1m
- Module and Course Summary 1m
About the author
Glenn works as a Principal Consultant at SQLskills.com. He has been a SQL Server MVP since 2007, and he has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, hardware selection, and performance tuning. He is also an Adjunct Faculty member at University College - University of Denver, where has been teaching since 2000. He has completed the Master Teacher P... more