SQL Server 2017: Diagnosing Performance Issues with DMVs from Pluralsight

Learn how to easily query SQL Server 2017 for performance information to help identify and fix issues 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 have workload performance problems in one or more ways. In this course, SQL Server 2017: Diagnosing Performance Issues with DMVs, you will see more than 45 DMV queries you can use to easily examine performance information related to your workload, instance health, and storage health. First, you will learn how to detect and correct many common performance issues related to the overall activity on the SQL Server instance and around the storage subsystem. Next, you will be shown how to investigate memory and CPU issues at the instance level. Finally, you will see how to drill into the database level and investigate performance issues there. By the end of this course, you will be able to find and correct the most common SQL Server performance 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 Activity-related Instance Issues

Diagnosing Storage-related Instance Issues

Diagnosing Memory-related Instance Issues

Diagnosing CPU-related Instance Issues

Diagnosing Activity-related Database Issues

Diagnosing Storage and Memory-related Database Issues

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Examines SQL Server 2017 performance, which is highly relevant to database administrators

Explores common SQL Server 2017 performance issues, which is standard in the industry

Teaches use of DMVs, which helps learners monitor and improve SQL Server performance

Covers various aspects of SQL Server performance, including activity, storage, memory, and CPU issues, which provides a comprehensive understanding

Appropriate for individuals responsible for SQL Server 2017 and earlier versions

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 Performance Issues with DMVs with these activities:

Find a More Experienced SQL Server Developer as a Mentor

Show steps

Connect with an experienced SQL Server developer who can provide guidance and support as you learn and grow

Browse courses on Mentoring

Show steps

Attend industry events and meetups
Reach out to people in your professional network
Search online forums and communities

Mentor Junior SQL Server Developers

Show steps

Share your knowledge and experience by mentoring junior SQL Server developers to reinforce your own learning and deepen your understanding

Browse courses on Mentoring

Show steps

Find a junior developer who is eager to learn
Set up regular meetings to discuss SQL Server concepts
Provide guidance and support on specific projects

Review SQL Basics

Show steps

Brush up on SQL basics to refresh your memory and prepare for more advanced topics

Show steps

Review SQL syntax and data types
Practice writing basic SELECT, INSERT, UPDATE, and DELETE statements

Nine other activities

Expand to see all activities and additional details

Show all 12 activities

Review Advanced SQL Concepts

Show steps

It will ensure that you have a solid understanding of the fundamental SQL concepts before diving into performance-related topics.

Show steps

Review your notes or study materials on advanced SQL concepts such as indexing, query optimization, and stored procedures.
Practice writing efficient SQL queries using advanced techniques.

Review Modern Database Management

Show steps

Review Modern Database Management core concepts - database design, data modeling, storage structure and query processing - to reinforce your learning

Show steps

Read chapters 1-3 and take notes on key concepts
Complete the exercises at the end of each chapter
Create a simple database design for a small business

Explore SQL Server DMV Tutorials

Show steps

They will provide practical examples and hands-on experience in using DMVs for performance troubleshooting.

Browse courses on Dynamic Management Views (DMVs)

Show steps

Find online tutorials or documentation on SQL Server DMVs.
Follow the tutorials to learn how to use specific DMVs for performance analysis.

Query SQL Server Using T-SQL

Show steps

Practice writing T-SQL queries to improve your understanding of data retrieval and manipulation

Browse courses on SQL Server

Show steps

Review T-SQL basics (SELECT, FROM, WHERE)
Write queries to retrieve data from different tables
Use joins to combine data from multiple tables
Practice writing queries to update and delete data

Analyze Queries with Query Store

Show steps

It will enable you to practice analyzing query execution plans and identifying performance bottlenecks.

Browse courses on Query Store

Show steps

Enable Query Store on your SQL Server instance.
Run a workload or execute queries to generate data in Query Store.
Use Query Store views to analyze query execution plans and identify performance issues.

Configure and Optimize SQL Server Indexes

Show steps

Learn how to create and manage indexes to improve the performance of your SQL Server databases

Browse courses on SQL Server

Show steps

Understand the different types of indexes
Create and modify indexes using SQL Server Management Studio
Monitor and troubleshoot index performance
Optimize indexes for specific queries

Contribute to SQL Server Open Source Projects

Show steps

Contribute to open source SQL Server projects to gain hands-on experience and expand your knowledge beyond the classroom

Browse courses on Open Source

Show steps

Find a project that aligns with your interests
Read the project documentation and contribute to discussions
Submit a bug report or feature request
Make code contributions to the project

Document Performance Recommendations

Show steps

It will allow you to synthesize your findings and provide practical recommendations for improving performance.

Show steps

Analyze the performance data and identify areas for improvement.
Develop specific recommendations for optimizing queries, tuning indexes, or adjusting server settings.
Create a detailed document outlining your recommendations.

Design a Database for a Real-World Application

Show steps

Create a database design for a real-world application to apply your knowledge of database concepts and SQL Server

Browse courses on Database Design

Show steps

Identify the requirements of the application
Create an entity-relationship diagram
Normalize the data model
Create the database tables and columns
Test the database design

Career center

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

Database Manager

Database Managers oversee database systems. They are tasked with planning, creating, and administering these systems in order to meet the needs of their organization. They may also be responsible for developing and implementing security measures, disaster recovery plans, and performance optimization strategies. This course may be useful for aspiring Database Managers as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of a database system.

See salaries and explore the career path for Database Manager

Database Administrator

Database Administrators manage and maintain database systems. They are responsible for ensuring the security, performance, and availability of databases. This course can be helpful for aspiring Database Administrators as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of a database system.

See salaries and explore the career path for Database Administrator

Network Administrator

Network Administrators manage and maintain computer networks. They are responsible for ensuring the security, performance, and availability of networks. This course can be helpful for aspiring Network Administrators as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of computer networks.

See salaries and explore the career path for Network Administrator

Cloud Architect

Cloud Architects design, plan, and manage cloud computing systems. They work with clients to determine their needs, then design and implement solutions that meet those needs. They may also be responsible for managing the security and performance of cloud systems. This course can be helpful for aspiring Cloud Architects as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of a cloud computing system.

See salaries and explore the career path for Cloud Architect

DevOps Engineer

DevOps Engineers work to bridge the gap between development and operations teams. They are responsible for ensuring that software is developed and deployed in a way that meets the needs of both teams. This course can be helpful for aspiring DevOps Engineers as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of software development and deployment.

See salaries and explore the career path for DevOps Engineer

Cloud Engineer

Cloud Engineers design, deploy, and manage cloud computing systems. They may work with clients to determine their needs, then design and implement solutions that meet those needs. They may also be responsible for managing the security and performance of cloud systems. This course can be helpful for aspiring Cloud Engineers as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of a cloud computing system.

See salaries and explore the career path for Cloud Engineer

Systems Administrator

Systems Administrators manage and maintain computer systems. They are responsible for ensuring the security, performance, and availability of systems. This course can be helpful for aspiring Systems Administrators as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of computer systems.

See salaries and explore the career path for Systems Administrator

IT Consultant

IT Consultants provide guidance to businesses on how to use technology to meet their business needs. They may work with businesses to develop IT strategies, select and implement software and hardware, and manage IT systems. This course may be helpful for aspiring IT Consultants as it can help them learn how to diagnose and correct performance issues, which can be a valuable skill for helping clients to optimize their IT systems.

See salaries and explore the career path for IT Consultant

Data Engineer

Data Engineers design, build, and maintain data pipelines. They work with data from a variety of sources to create data products that can be used by businesses to make informed decisions. This course may be helpful for aspiring Data Engineers as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of data pipelines.

See salaries and explore the career path for Data Engineer

Machine Learning Engineer

Machine Learning Engineers design, develop, and deploy machine learning models. They work with data from a variety of sources to create models that can be used to solve a variety of problems. This course may be helpful for aspiring Machine Learning Engineers as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective development and deployment of machine learning models.

See salaries and explore the career path for Machine Learning Engineer

Software Engineer

Software Engineers design, develop, and maintain software applications. They may work on a variety of projects, from small personal apps to large enterprise systems. This course may be useful for aspiring Software Engineers as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the development and maintenance of software applications.

See salaries and explore the career path for Software Engineer

Data Analyst

Data Analysts collect, clean, and analyze data to help businesses make informed decisions. They may use data mining, statistical analysis, and machine learning to identify trends and patterns in data. This course may be helpful for aspiring Data Analysts as it can help them learn how to diagnose and correct performance issues, which is a critical skill for managing and analyzing data.

See salaries and explore the career path for Data Analyst

Data Scientist

Data Scientists use data to solve a variety of problems. They may work with data from a variety of sources to create models that can be used to predict outcomes, identify trends, and make recommendations. This course may be helpful for aspiring Data Scientists as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of data and the development of data science models.

See salaries and explore the career path for Data Scientist

Business Intelligence Analyst

Business Intelligence Analysts use data to help businesses make informed decisions. They may work with data from a variety of sources to create reports, visualizations, and dashboards that can be used to track performance, identify trends, and make predictions. This course may be helpful for aspiring Business Intelligence Analysts as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the effective management of data and the creation of business intelligence reports.

See salaries and explore the career path for Business Intelligence Analyst

Web Developer

Web Developers design, develop, and maintain websites and web applications. They may work on a variety of projects, from small personal websites to large e-commerce sites. This course may be helpful for aspiring Web Developers as it can help them learn how to diagnose and correct performance issues, which is a critical skill for the development and maintenance of websites and web applications.

See salaries and explore the career path for Web Developer