SQL Server 2017: Diagnosing Configuration Issues with DMVs from Pluralsight

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.

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.

What's inside

Syllabus

Course Overview

Introduction

Diagnosing Hardware and Storage Configuration Issues

Diagnosing Operating System Configuration Issues

Diagnosing SQL Server Instance-level Configuration Issues

Diagnosing SQL Server Database-level Configuration Issues

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Teaches a broad range of skills relevant to the work of a professional database administrator

Thoroughly examines configuration issues with SQL Server 2017 and earlier versions

Taught by Glenn Berry, a recognized expert on SQL Server performance and stability

Suitable for beginners and experienced SQL Server administrators alike

Provides a comprehensive overview of SQL Server configuration management

May require prior knowledge of SQL Server administration for some learners

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in SQL Server 2017: Diagnosing Configuration Issues with DMVs with these activities:

Review Hardware and Network Considerations

Show steps

Ensure a solid foundation in hardware and network concepts

Browse courses on Hardware

Show steps

Revisit basic principles of computer hardware and networking
Review best practices for SQL Server hardware and network configuration

Review SQL Server terminology

Show steps

Review SQL Server terminology to remind yourself of the key jargon that will be used throughout this course.

Browse courses on Relational Database Management Systems

Show steps

Visit Microsoft Learn: SQL Server terminology
Read through the introduction and concepts pages
Complete the online quiz: SQL Server terminology

Review SQL Concepts

Show steps

Refresh basic understanding of SQL Server through reviewing previous knowledge

Browse courses on SQL

Show steps

Review SQL Basics
Create a sample database in SQL Server

15 other activities

Expand to see all activities and additional details

Show all 18 activities

Review database principles

Show steps

Review database principles to refresh your understanding of the fundamental concepts that underpin SQL Server.

Browse courses on Database Normalization

Show steps

Visit SQL Server training: Database fundamentals
Review the following modules:
Database design basics
Database normalization
Database integrity
Take the assessment quiz

Join a SQL Server Study Group

Show steps

Enhance understanding and learn from others through peer discussions

Show steps

Find a study group or create one
Attend the study sessions and engage in discussions
Collaborate on projects or assignments

Understand SQL Server Diagnostic Information Management (DIM)

Show steps

You can explore guided tutorials to understand how to use SQL Server Diagnostic Information Management (DIM) to collect and analyze diagnostic information for troubleshooting SQL Server issues.

Show steps

Find tutorials on SQL Server DIM
Follow the tutorials

Join a SQL Server user group

Show steps

Join a SQL Server user group to connect with other professionals, learn from their experiences, and stay up-to-date on the latest trends.

Browse courses on Networking

Show steps

Search for SQL Server user groups in your area
Attend a user group meeting
Introduce yourself and share your interests
Participate in discussions and ask questions
Connect with other members on LinkedIn

Volunteer at a SQL Server conference

Show steps

Volunteer at a SQL Server conference to gain practical experience, network with professionals, and contribute to the community.

Browse courses on Volunteering

Show steps

Search for SQL Server conferences in your area
Contact the conference organizers
Express your interest in volunteering
Attend the conference and fulfill your volunteer responsibilities
Network with attendees and speakers

Run queries against DMV tables

Show steps

You can practice running queries against commonly used DMV tables to identify performance issues.

Show steps

Install a SQL Server Management Studio (SSMS) tool
Connect to an instance of SQL Server
Run queries against DMV tables

Complete Pluralsight SQL Server Tutorials

Show steps

Gain a deeper understanding of SQL Server through guided tutorials

Show steps

Find suitable Pluralsight SQL Server tutorials
Follow the tutorials and complete the exercises provided

Configure a SQL Server instance

Show steps

You can practice configuring a SQL Server instance to troubleshoot configuration issues and improve SQL Server performance.

Show steps

Install SQL Server
Configure SQL Server instance
Troubleshoot configuration issues

Practice querying system DMVs

Show steps

Practice querying system DMVs to gain hands-on experience in identifying potential configuration issues.

Browse courses on SQL Server Performance

Show steps

Install SQL Server Management Studio (SSMS)
Connect to a SQL Server instance
Query the sys.dm_os_performance_counters DMV
Query the sys.dm_os_wait_stats DMV
Query the sys.dm_exec_sessions DMV

Write a blog post or article about SQL Server configuration best practices

Show steps

You can write a blog post or article about SQL Server configuration best practices to share your knowledge and insights with others and reinforce your understanding of the topic.

Show steps

Research SQL Server configuration best practices
Write a blog post or article
Publish your content

Practice Querying SQL Server

Show steps

Build proficiency in querying SQL Server through repetitive exercises

Show steps

Create practice databases
Craft queries to retrieve data and generate reports
Test the accuracy and efficiency of the queries

Write a Technical Blog on SQL Server Configuration

Show steps

Reinforce understanding and share knowledge by creating a technical blog

Show steps

Choose a specific topic related to SQL Server configuration
Research and gather information from reliable sources
Write a well-structured and informative blog post

Write a blog post on SQL Server configuration best practices

Show steps

Write a blog post on SQL Server configuration best practices to solidify your understanding of the topic and share your knowledge with others.

Browse courses on SQL Server Performance

Show steps

Research SQL Server configuration best practices
Create an outline for your blog post
Write the content for your blog post
Edit and proofread your blog post
Publish your blog post

Contribute to SQL Server Open Source Projects

Show steps

Gain practical experience and contribute to the SQL Server community

Show steps

Find suitable open source projects
Review the codebase and identify areas for contribution
Create and submit pull requests

Mentor Junior SQL Server Professionals

Show steps

Enhance your knowledge by sharing it and supporting others

Show steps

Find opportunities to mentor junior professionals
Provide guidance and support on SQL Server configuration and related topics
Share real-world experiences and lessons learned

Career center

Learners who complete SQL Server 2017: Diagnosing Configuration Issues with DMVs will develop knowledge and skills that may be useful to these careers:

Database Administrator

A Database Administrator (DBA) is responsible for the day-to-day operations of a database system, including maintenance, performance tuning, and security. This course can help a DBA ensure that their SQL Server instances are configured correctly, which can improve performance and stability, and reduce the risk of data loss or corruption. Additionally, understanding the concepts covered in this course will be foundational to success in this role.

See salaries and explore the career path for Database Administrator

Data Analyst

A Data Analyst helps businesses make data-driven decisions by collecting, cleaning, and analyzing data, and then presenting their findings to key stakeholders. By learning how to query SQL Server for configuration information, this course can help a Data Analyst identify misconfigurations that can affect performance and stability, thereby maximizing the reliability and performance of the database systems they manage.

See salaries and explore the career path for Data Analyst

Database Developer

A Database Developer designs and develops database systems, including the creation of tables, indexes, and stored procedures. This course can help a Database Developer understand how SQL Server is configured, which can be helpful when designing and developing database systems.

See salaries and explore the career path for Database Developer

Systems Administrator

A Systems Administrator is responsible for the day-to-day operations of a computer system, including hardware, software, and networking. This course can help a Systems Administrator ensure that the SQL Server instances on their system are configured correctly, which can improve performance and stability, and reduce the risk of downtime or data loss.

See salaries and explore the career path for Systems Administrator

Data Warehouse Engineer

A Data Warehouse Engineer designs and builds data warehouses, which are large repositories of data that are used for business intelligence and reporting. This course can help a Data Warehouse Engineer understand how SQL Server is configured, which can be helpful when designing and building data warehouses.

See salaries and explore the career path for Data Warehouse Engineer

Technical Support Engineer

A Technical Support Engineer provides technical support to users of computer hardware and software. This course can help a Technical Support Engineer troubleshoot problems with SQL Server instances, which can help them to resolve issues and improve customer satisfaction.

See salaries and explore the career path for Technical Support Engineer

Network Administrator

A Network Administrator is responsible for the day-to-day operations of a computer network, including hardware, software, and security. This course can help a Network Administrator ensure that the SQL Server instances on their network are configured correctly, which can improve performance and stability, and reduce the risk of downtime or data loss.

See salaries and explore the career path for Network Administrator

DevOps Engineer

A DevOps Engineer works to bridge the gap between development and operations teams, and is responsible for the deployment and management of software applications and systems. This course can help a DevOps Engineer understand how SQL Server is configured, which can be helpful when deploying and managing SQL Server instances.

See salaries and explore the career path for DevOps Engineer

Cloud Engineer

A Cloud Engineer designs and builds cloud-based solutions, including the deployment and management of SQL Server instances. This course can help a Cloud Engineer understand how SQL Server is configured, which can be helpful when deploying and managing SQL Server instances in the cloud.

See salaries and explore the career path for Cloud Engineer

IT Manager

An IT Manager is responsible for the planning, implementation, and management of an organization's IT systems. This course can help an IT Manager understand how SQL Server is configured, which can be helpful when making decisions about the deployment and management of SQL Server instances.

See salaries and explore the career path for IT Manager

Software Engineer

A Software Engineer has a broad skillset, including the ability to design, develop, and maintain software applications and systems. This course can help a Software Engineer who works with SQL Server databases ensure that their applications are configured correctly, which can improve performance and stability.

See salaries and explore the career path for Software Engineer

Security Analyst

A Security Analyst is responsible for protecting an organization's information and systems from unauthorized access, use, disclosure, disruption, modification, or destruction. This course can help a Security Analyst understand how SQL Server is configured, which can be helpful when assessing the security of SQL Server systems.

See salaries and explore the career path for Security Analyst

Data Scientist

A Data Scientist uses scientific methods and techniques to extract knowledge and insights from data. This course can help a Data Scientist understand how SQL Server is configured, which can be helpful when working with data from SQL Server databases.

See salaries and explore the career path for Data Scientist

Project Manager

A Project Manager is responsible for the planning, execution, and closure of projects. This course can help a Project Manager understand how SQL Server is configured, which can be helpful when managing projects that involve the deployment or management of SQL Server instances.

See salaries and explore the career path for Project Manager

Business Analyst

A Business Analyst analyzes business processes and systems to identify areas for improvement. This course can help a Business Analyst understand how SQL Server is configured, which can be helpful when analyzing data from SQL Server databases.

See salaries and explore the career path for Business Analyst