SQL Server 2017: Diagnosing Configuration Issues with DMVs
Learn how to easily query SQL Server 2017 for configuration information to help identify misconfigurations 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 be configured incorrectly in one or more ways, and this often has a detrimental effect on workload performance and stability. In this course, SQL Server 2017: Diagnosing Configuration Issues with DMVs, you will see more than 30 DMV queries you can use to easily examine SQL Server instance and database configuration information. First, you will learn how to detect and correct many common configuration issues with your hardware, storage, operating system, SQL Server instance, and your database properties. Then, you will be given the best practices for properly checking an instance of SQL Server for configuration issues, both at the instance-level and at a database level. Finally, you will see the configuration settings that you can use to have a more reliable and better performing SQL Server environment. By the end of this course, you will be able to find and correct the most common SQL Server configuration 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 Hardware Information 2m
- Demo: Hardware Information 2m
- System Manufacturer and BIOS Date 2m
- Demo: System Manufacturer and BIOS Date 1m
- Processor Description 2m
- Demo: Processor Description 1m
- SQL Server NUMA Information 2m
- Demo: SQL Server NUMA Information 1m
- Memory-optimized Checkpoint Mode 1m
- Demo: Memory-optimized Checkpoint Mode 1m
- System Memory and BPE Configuration 2m
- Demo: System Memory and BPE Configuration 2m
- Fixed Drives and Volume Information 2m
- Demo: Fixed Drives and Volume Information 1m
- Module Summary 1m
- Introduction and Version Information 2m
- Demo: Version Information 1m
- Host Information 1m
- Demo: Host Information 1m
- Windows Information 1m
- Demo: Windows Information 1m
- Core Counts 1m
- Demo: Core Counts 2m
- Instant File Initialization 2m
- Demo: Instant File Initialization 1m
- SQL Server Services Information 1m
- Demo: SQL Server Services Information 1m
- Process Memory 1m
- Demo: Process Memory 1m
- Loaded Modules 1m
- Demo: Loaded Modules 1m
- Module Summary 1m
- Introduction and Server Properties 2m
- Demo: Server Properties 2m
- Configuration Properties 1m
- Demo: Configuration Properties 2m
- Global Trace Flags 2m
- Demo: Global Trace Flags 1m
- SQL Server Agent Jobs 2m
- Demo: SQL Server Agent Jobs 1m
- SQL Server Agent Alerts 1m
- Demo: SQL Server Agent Alerts 2m
- Memory Dump Information 1m
- Demo: Memory Dump Information 1m
- Suspect Pages 1m
- Demo: Suspect Pages 1m
- Tempdb Data Files 1m
- Demo: Tempdb Data Files 1m
- Module Summary 0m
- Introduction and Database Properties 2m
- Demo: Database Properties 4m
- Database Filenames and Paths 1m
- Demo: Database Filenames and Paths 1m
- VLF Counts 2m
- Demo: VLF Counts 1m
- Last Backup by Database 1m
- Demo: Last Backup by Database 1m
- File Sizes and Space 2m
- Demo: File Sizes and Space 2m
- Database-scoped Configuration 2m
- Demo: Database-scoped Configuration 1m
- Table Properties 1m
- Demo: Table Properties 1m
- Query Store Options 1m
- Demo: Query Store Options 1m
- Automatic Tuning Options 2m
- Demo: Automatic Tuning Options 1m
- Module and Course Summaries 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