SQL Server Performance Tuning Part 2

Name: SQL Server Performance Tuning Part 2
Rating: 4.6 (2754 reviews)

Resolving SQL Server performance tuning issues

Created byRaphael Asghar

Last updated 5/2018

English

What you'll learn

This is the second part of SQL Server performance tuning course; at the end of this course the student will be able to resolve and troubleshoot issues related index tuning, page splitting, tempb database sizing, recompilation of stored procedures, and much more with the use of tools and utilities such the performance monitor, DTA, DMVs, extended events, profiler and other tools.

Course content

1 section • 41 lectures • 7h 35m total length

PRE REQUISTES TO SQL SERVER PERFORMANCE TUNING PART 23:20
DOWNLOADING THE APPLICATIONS13:32
CREATING VM AND INSTALLING WINDOWS 2016 OS12:33
INSTALLING SQL 201710:00
INSTALLING SQL 2017 PART 212:04
install sql server 2017 part 2, disable python and polybase, install sql server management studio 17.4, and compare 2017 with 2016 and older versions for performance tuning prep.
SQL SERVER VERSION COMPARISON6:28
Compare SQL Server versions from 2014 to 2017, showing identical t-sql behavior across versions and noting Linux deployment is possible with newer releases.
PERFMOM5:14
PERFORMANCE COUNTERS19:12
SAVING PERMON COUNTERS8:05
DATA COLLECTION SET10:00
Create and manage data collection sets with Perfmon to build a baseline for SQL Server performance, capturing counters like CPU, memory, disk, and network for time-based comparison.
DATA COLLECTION SET PART 212:47
ADDING ADVENTUREWORKS 20175:57
ANALYSIS OF METRICS16:45
BASELINE CPU14:33
Create a baseline CPU with the SQL Query Stress Utility, stress the SQL Server, and compare the baseline and stress data to identify CPU bottlenecks.
SQL PROFILER6:51
LIMIT PROFILER DATA8:19
PERFMON AND PROFILER8:56
EXTENDED EVENTS PART 110:00
EXTENDED EVENTS PART 210:36
Explore extended events in SQL Server, create and filter event sessions, capture SQL text, database name, and user details, and analyze security and long-running queries for performance tuning.
EXTENDED EVENTS SESSION SCRIPT9:45
FIND LONGEST RUNNING QUERY8:17
DMVS12:15
SETTING UP QUERY STORE4:34
QUERY STORE10:00
QUERY STORE PART 216:49
FORCED PLAN10:00
Learn how the query store enables a forced plan, comparing execution plans for the same query and enforcing the best plan. Observe how a non-clustered index lowers logical reads.
FORCED PLAN PART 211:00
INDEXING AND FRAGMETATION13:35
SQL AGENT JOB FOR INDEX AND OLA10:00
SQL AGENT JOB FOR INDEX AND OLA PART 211:06
DTA INDEXING AND QUERY STORE18:24
SOLVING RECOMPILATION10:00
SOLVING COMPILATION PART 215:25
RESOVLING PAGE SPLITS PART 110:00
RESOVLING PAGE SPLITS PART 216:27
VIEWING PAGE SPLITS VIA EXTENDED EVENTS8:46
RESOLVING BLOCKING ISSUES16:13
TEMPDB PERFORMANCE10:00
Explore tempdb's role as a busy system database and learn how to improve performance by moving to a fast separate drive, sizing files, adding data files, and tuning auto growth.
TEMPDB PERFORMANCE PART 210:41
DATABASE BACKUPS9:17
WAIT STATS18:02

Requirements

SQL performance tuning part 2 requires that you MUST have SQL performance tuning part 1 course and SQL administration part 1,2,3 as well as Learn T-SQL courses. All SQL Server application, Virtual Box application and Windows 2016 operating system will be available as a trail period for 6 months via downloads

Description

This course is designed for any student who has knowledge of SQL Administration and wants to learn about SQL Server performance tuning their SQL servers. You must have the prerequisite SQL performance tuning part 1 course.

This is the second part of SQL Server performance tuning course; at the end of this course the student will be able to resolve and troubleshoot issues related index tuning, page splitting, tempb database sizing, recompilation of stored procedures, and much more with the use of tools and utilities such the performance monitor, DTA, DMVs, extended events, profiler and other tools. This will allow you to have an optimum running SQL Server and an extensive knowledge of how to trouble shoot and resolve the more pressing issues you may face in your environment.

Who this course is for:

Any student who has knowledge of SQL Administration and wants to learn about SQL Server performance tuning their SQL servers. You must have the prerequisite SQL performance tuning part 1 course