SQL Server Database Performance Monitoring and Tuning

Name: SQL Server Database Performance Monitoring and Tuning
Rating: 3.9 (138 reviews)

Become A Smart DBA

Created byGangadhar Gawande

Last updated 9/2020

English

English [Auto],

What you'll learn

SQL Server Database Performance Monitoring (Automated) and Tuning
How to capture database performance metrics, Causes of Performance bottlenecks, Diagnosing Performance issues, Indexes, Statistics, Wait Statistics, Parameter Sniffing, How to capture expensive stored procedures, blocking incidents, deadlock incidents, How to automate the entire performance metrics collection process
At the end of the course, the students will be able to deploy the code directly on their machine and see the results instantly. The final output of the consolidated script will be equivalent to other expensive software in the market.

Course content

9 sections • 33 lectures • 2h 37m total length

Introduction4:50
After completing this chapter you will be familiar with the overall understanding of the course.

TempDB Contention2:13
Non-Scheduled Database Maintenance0:57
Schedule regular maintenance activities such as index updates, statistics updates, and database health checks to prevent performance degradation and downtime.
Expensive Stored Procedures or Queries1:06
Identify expensive stored procedures or queries that drive high execution time and CPU usage, apply best practices, and continuously collect metrics to optimize performance and maintain business continuity.
Blocking Incidents1:26
Deadlock Incidents1:45
Wrong Index Strategy0:46
A High CPU Consumption Consistently3:51
High Memory Usage - Memory Allocation Background2:56
High Memory Usage - Configurations and Parameters8:19
Disk I/O Bottleneck1:52
Poor Database Design5:42
Poor Application Design0:36

Index Management8:21
Analyzing Execution Plan for Indexes6:41
Detecting and Reducing Fragmentation8:32
After completing this lecture, students will be able to understand and implement how to detect and reduce the index fragmentation.
Statistics7:43
Wait Statistics5:43
Learn how wait statistics guide performance troubleshooting by analyzing wait types and cumulative data from the wait statistics view. Identify dominating waits by total wait time to diagnose CPU contention.
Dealing with TempDB Contention6:37
Identify tempdb contention by monitoring DMV waits and heavy temp table usage. Increase data files to maximize disk bandwidth and match logical processors, as SQL Server 2019 auto-tunes the count.
Temporary Table Caching1:55
Temporary Table Vs Table Variable2:10
Identifying Expensive Stored Procedures and Queries8:15
Identifying Blocking Incidents6:43
Identifying Deadlock Incidents8:09
After completion of this lecture, students will be able to understand what is Deadlock incident and how to monitor it.
Optimize for Adhoc Query Workload2:57
Enable optimize for ad hoc workload to reduce plan cache bloat from single-use queries. The first execution stores a small, combined plan; subsequent executions reuse the full cached plan.
Analyzing Execution Plan5:20

Requirements

Basic knowledge of RDBMS database

Description

This course equips you with the skills required to manage the SQL Server database efficiently. The course mainly focuses on performance monitoring and tuning and how to automate the monitoring process. The most valuable gift of this course is to have ready to implement scripts including schema and other required objects at the end of the course. Students can directly deploy the code and start generating SQL Server database performance metrics.

Note: It is recommended to run the code on a test environment before deploying on production.

Who this course is for:

SQL Server DBAs, Developers, Architects who want to perform the database performance monitoring and tuning
Any Beginner who wants to start with Microsoft SQL Server as a career option

SQL Server Database Performance Monitoring and Tuning

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 5min

About The Course1 lecture • 1min

Why Automation Is Necessary ?1 lecture • 3min

What To Monitor ?1 lecture • 10min

Query Optimizer1 lecture • 5min

Parameter Sniffing1 lecture • 8min

Causes of Performance Degradation12 lectures • 31min

Diagnosing Performance Problem13 lectures • 1hr 19min

Automating the Monitoring Process - Putting It All Together2 lectures • 16min

Requirements

Description

Who this course is for: