SQL Server 2017: Diagnosing Configuration Issues with DMVs
By Glenn Berry
Course info
Course info
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 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.
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
Section Introduction Transcripts
Course Overview
My name is Glenn Berry, and welcome to my course: SQL Server 2017: Diagnosing Configuration Issues with DMVs. I'm a Principal Consultant with SQLskills, and this topic is one that I feel very passionate about. In the course of my career as a database administrator and consultant, I've used my DVM diagnostic queries extensively to find and correct many common SQL Server configuration issues. The queries that are detailed and demonstrated in this course will help you detect and correct many common configuration issues with your hardware, storage, operating system, SQL Server instance, and your database properties. I want to give you the best practices and configuration settings that you can use to have a more reliable and better performing SQL Server environment. If you work with SQL Server in any capacity, whatever your level, this course is relevant to you. Experience using SQL Server 2005 or newer is helpful, along with a basic understanding of how SQL Server works. I hope you'll join me as we explore proper SQL Server configuration, with SQL Server 2017: Diagnosing Configuration Issues with DMVs, here on Pluralsight.
Diagnosing Operating System Configuration Issues
Diagnosing Operating System Configuration Issues. So what are we going to talk about in this module? We're going to talk about the operating system configuration queries to actually find out how your operating system is configured and whether you need to make any changes. We'll talk about how to interpret the query results, and then we'll also get into some best- practice configuration settings for your operating system. Version information. The first query in this module is going to let us see the SQL Server version and edition information, including the exact build number. It's also going to show us the operating system version and edition, and it's going to let us know whether or not we have a hypervisor present on the host. Going a little bit deeper into this first query, it's going to give us the exact SQL Server version, edition, and build number; and that's really important to know. Then it's going to give us the operating system version and edition information. Then it's going to let us understand whether we can take advantage of Enterprise Edition features, and also whether we have to worry about Standard Edition license limits. It's going to help us know whether SQL Server's been properly patched. So if I run this and I find out that I'm way, way behind on my builds, I want to do something about that; and then finally, knowing if a hypervisor is present or not is useful, but it doesn't necessarily mean that you're running inside of the hypervisor. There's other queries you can run to determine that 100% one way or the other.
Diagnosing SQL Server Instance-level Configuration Issues
This is Glenn Berry with sqlskills. com, and this is Diagnosing SQL Server Instance-level Configuration Issues. So what are we going to talk about in this module? We'll be covering instance-level configuration queries, and how to interpret the results of those queries, and the finally I'll talk about some best-practice configuration settings for instance level settings. Server properties. We can call this built-in server property function to pull back a lot of useful information such as the machine name and server name, you clustering status, detailed build information. You can also get your process ID and what collation you're using. You can also get your default data and log paths, along with information about whether or not you're using Polybase or R services. So drilling deeper into this particular set of queries, it's going to give you a lot of useful information about your machine and your instance-level information, and it's going to help you just understand your basic environment that you're running in. It gives you discrete version, edition, and build information, so when you do select Adapt Version for example, you get one big, long string that you have to parse and pull out all the different pieces. This'll show you all the things in discrete columns that you can use for different versions programmatically, for example; and it's also going to show you the default data and log file paths that you set up when you installed SQL Server or later on if you ever want to change that. And then finally, it gives you more information about security, whether you're using FILESTREAM or not, whether you're using Polybase and/or R services.
Diagnosing SQL Server Database-level Configuration Issues
This is Glenn Berry with sqlskills. com, and this is Diagnosing SQL Server Database-level Configuration Issues. So what are we going to talk about in this module? We're going to go over a series of database-level configuration queries. Then we'll get into how to interpret all the query results from those queries. Then I'm going to talk about some best-practice configuration settings at the database level. Database properties. This query returns a lot of very useful database properties for all the databases on your instance, all the user databases and all the system databases. It shows you the recovery model for each database, the log reuse wait description, all your statistics properties for the database, whether or not you have delayed durability enabled or not, and whether Query Store is enabled or not for each one of your databases. Going deeper into this query, the database owner should be sa, not an individual login. That's one of the columns that comes back from this query. You also want to figure out what the log reuse wait description is and your log used percentage because that's going to help you figure out if you have a runaway transaction log, for example. You also want to know your compatibility level because it's super important to understand that because of the new cardinality estimator that was introduced in SQL Server 2014. You also want to understand your static properties because that can have a big effect on your query performance, and then finally, knowing your isolation level, whether you're using snapshot isolation or read committed snapshot isolation can have a big effect on locking and blocking issues.