SQL Server 2014 DMV Diagnostic Queries - Part 2
Learn how to easily query SQL Server for instance-level information to help identify and solve performance problems. This course is applicable to anyone who is responsible for a SQL Server instance, with most topics also applicable to SQL Server 2012 and earlier versions.
What you'll learn
It is very common for SQL Server instances to have performance problems, but it can be hard to know how to get the information necessary to figure out what the performance problems are. This course will describe and demonstrate more than 20 DMV queries you can use to easily examine SQL Server instance performance information and identify problems. This course is perfect for anyone who is responsible for a SQL Server instance, with most topics also applicable to SQL Server 2012 and earlier versions.
Table of contents
- Introduction 1m
- I/O Warnings 2m
- Demo: I/O Warnings 2m
- Drive-Level Latency 2m
- Demo: Drive-Level Latency 1m
- I/O Latencies by Database File 2m
- Demo: I/O Latencies by Database File 2m
- Database Properties 1m
- Demo: Database Properties 3m
- Missing Indexes for All Databases 2m
- Demo: Missing Indexes for All Databases 6m
- Getting VLF Counts 1m
- Demo: Getting VLF Counts 2m
- CPU Usage by Database 1m
- Demo: CPU Usage by Database 1m
- I/O Usage by Database 1m
- Demo: I/O Usage by Database 1m
- Total Buffer Usage by Database 1m
- Demo: Total Buffer Usage by Database 1m
- Top Waits 2m
- Demo: Top Waits 2m
- Signal Waits 1m
- Demo: Signal Waits 1m
- Summary 1m
- What Is Next? 1m
- Introduction 1m
- Connection Counts 1m
- Demo: Connection Counts 1m
- Connection Counts by IP Address 1m
- Demo: Connection Counts by IP Address 1m
- Average Task Counts 2m
- Demo: Average Task Counts 2m
- CPU Utilization History 1m
- Demo: CPU Utilization History 1m
- Top Worker Time Queries 1m
- Demo: Top Worker Time Queries 1m
- System Memory 1m
- Demo: System Memory 1m
- Process Memory 1m
- Demo: Process Memory 1m
- Page Life Expectancy by NUMA Node 1m
- Demo: Page Life Expectancy by NUMA Node 1m
- Memory Grants Pending 1m
- Demo: Memory Grants Pending 1m
- Memory Clerk Usage 1m
- Demo: Memory Clerk Usage 1m
- Ad Hoc Queries 2m
- Demo: Ad Hoc Queries 2m
- 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