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.
Course info
Rating
(277)
Level
Beginner
Updated
May 19, 2014
Duration
1h 7m
Table of contents
Introduction
Instance-Level Configuration Queries Part 1
Instance-Level Configuration Queries Part 2
Description
Course info
Rating
(277)
Level
Beginner
Updated
May 19, 2014
Duration
1h 7m
Description

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.

About the author
About the author

Glenn works as a Principal Consultant at SQLskills.com. He has been a SQL Server MVP since 2007, and he is also an Adjunct Faculty member at University College - University of Denver. He is the author of the book SQL Server Hardware (Redgate 2011), and he wrote chapters for both SQL Server MVP Deep Dives books.

More from the author
More courses by Glenn Berry
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
This is Glenn Berry with SQLskills. com and I'm recording this course for Pluralsight. This is SQL Server 2014 DMV Diagnostic Queries - Part 1 and this is the introduction. So what are we going to cover in this course? Well, database performance issues are immediately noticeable. They can quickly affect multiple applications or your entire organization. People tend to notice database performance issues very quickly and you can't just reboot the database server and hope that that fix the problem, even though that's what a lot of people do when they first run into a performance problem, and you may not know it, but DBA actually stands for Default Blame Acceptor. I can't tell you how many times I've heard, what's wrong with the database, why is the database server so slow whenever there's any kind of a problem with the application and the database or the database server is typically guilty until proven innocent and, as a DBA, you need the tools and knowledge to help quickly determine the actual problem, if there is one, because maybe there's nothing wrong with the database or the database server, but until you've got the right kind of tools to investigate and prove that, you're going to be at the mercy of everybody who blames the database whenever there's any kind of performance issue, and using the DMV queries in this course will help you come to that conclusion and be able to prove it.

Instance-Level Configuration Queries Part 1
This is Glenn Berry with SQLSkills. com and this is Instance-Level Configuration Queries Part 1. Instance-Level Configuration Queries are a set of initial queries to collect hardware and instance-level configuration information. These can be run in the context of any database on the instance, so it doesn't matter what database you're connected to, they're not database specific. A very high percentage of SQL Server instances that I've run across in my career have instance-level configuration issues and most of these instance-level configuration issues are very easy to correct. Quite often you don't even have to restart SQL Server for the change to go into effect. Now my Pluralsight course, SQL Server 2012: Installation and Configuration, covers the best practice installation configuration settings that you should use for a new installation of SQL Server, and most of those settings are going to be the exact same for SQL Server 2014. That URL you can find that at is right here.

Instance-Level Configuration Queries Part 2
Instance-Level Configuration Queries Part 2. Instance-level configuration queries are a set to initial queries to collect hardware and instance configuration information. These can be run in the context of any database on your instance, so it doesn't matter what database you're connected to when you run these queries, they're not database specific. A very high percentage of SQL Server instances that I have ever looked at have instance-level configuration issues and most of these are pretty easy to correct. Sometimes you don't even have to restart SQL Server for the change to go into effect. Now, my Pluralsight course, SQL Server 2012: Installation and Configuration, covers best practices for instance-level configuration and you can look at it by going to this URL.