SQL Server 2014 DMV Diagnostic Queries - Part 1
Learn how to easily query SQL Server for instance configuration information to help identify misconfigurations that can affect performance. 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 be configured incorrectly in one or more ways, and this often has a detrimental effect on workload performance. This course will describe and demonstrate more than 20 DMV queries you can use to easily examine SQL Server instance configuration 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
- Getting the Product Version 2m
- Demo: Getting the Product Version 2m
- When Was SQL Server Installed? 1m
- Demo: When Was SQL Server Installed? 1m
- Getting Useful Server Properties 1m
- Demo: Getting Useful Server Properties 1m
- Getting SQL Server Agent Job Information 1m
- Demo: Getting SQL Server Agent Job Information 1m
- Getting SQL Server Agent Alert Information 2m
- Demo: Getting SQL Server Agent Alert Information 1m
- Global Trace Flag Information 2m
- Demo: Global Trace Flag Information 1m
- Getting Information About Windows 1m
- Demo: Getting Information About Windows 2m
- SQL Server Services Information 1m
- Demo: SQL Server Services Information 1m
- SQL Server NUMA Node Information 1m
- Demo: SQL Server NUMA Node Information 1m
- Getting Hardware Information 1m
- Demo: Getting Hardware Information 2m
- Getting the Server Model Number 1m
- Demo: Getting the Server Model Number 1m
- Getting the Processor Description 1m
- Demo: Getting the Processor Description 1m
- Summary and What is Next 1m
- Introduction 1m
- SQL Server Failover Cluster Log Properties 1m
- Demo: SQL Server Failover Cluster Log Properties 1m
- OS Cluster Properties 1m
- Demo: OS Cluster Properties 0m
- Cluster Node Properties 1m
- Demo: Cluster Node Properties 1m
- Failover Cluster Node with AlwaysOn AG 1m
- Demo: Failover Cluster Node with AlwaysOn AG 1m
- Instance Configuration Values 1m
- Demo: Instance Configuration Values 3m
- Buffer Pool Extension Properties 1m
- Demo: Buffer Pool Extension Properties 2m
- Buffer Pool Extension Usage 1m
- Demo: Buffer Pool Extension Usage 1m
- TCP Listener States 1m
- Demo: TCP Listener States 1m
- Memory Dump Information 1m
- Demo: Memory Dump Information 1m
- Database Filenames and Paths 1m
- Demo: Database Filenames and Paths 2m
- Volume Information 1m
- Demo: Volume Information 1m
- 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