Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

How to Become a World Class SQL Server Performance Tuner

Name: How to Become a World Class SQL Server Performance Tuner
Rating: 4.3 (354 reviews)

Use These Scripts in Production Right Now

Created byMike West

Last updated 10/2020

English

What you'll learn

By the end of this course you will have a systematic process approach and all the necessary tools needed to begin your journey as a SQL Server performance tuner.
In this course we will define our performance tuning objectives before we dive head first into tuning. What is our end goal? What steps are we going to take to arrive at this goal? Our end goal needs to be realistic and measureable.
Let’s define them right now. Your goal be the end of the course will be speed up data retrieval on the worst performing queries by at least 30%.
Secondly, your goal will be to reduce system stress on either the IO subsystem, memory or cpu by 20%.

Course content

4 sections • 50 lectures • 2h 3m total length

This Course is About Learning How To Properly Tune A SQL Server.1:37
What is the course about? Performance tuning is a complicated subject but in this course we will break down the basics.
Our two main goals will be:
Reduce critical resource consumption.
Increase transaction speed.
I've Tuned over 500 SQL Servers in the last 5 years. Let Me Show You How.0:30
A little about me. Your instructors experience and background is important. Take a moment to look at mine.

Why take a course from me:

Highly Certified

Twenty years of IT experience

Fifteen years as a DBA.

Author

Community contributor

Performance Tuning is my niche
All of The Course Content Can Be Downloaded Right Here. Download it Now.
Location of scripts and all of the course content. Please take minute to download the course content and save it to a folder you'll be able to easily access. The downloadable is included as a zip file.

NOTE: I've removed sp_whoisactive from our scripts library. We want to make sure we are getting the latest and most up to date version. In order to do that we need to navigate to Adam Machanic's blog. I've created a lecture that point to his blog. Please download sp_whoisactive from there.
Install sp_whoisactive1:28
This is the latest version of sp_whoisactive.
Creating the Udemy Database Video1:09
In this lecture let's create the Udemy database.
Install Load Generator Tool2:59
In this lesson let's install our load generator.
A lot of Performance Tuning is Iteration. Let's find out what iterations is.0:38
Let's define it before we apply it to performance tuning. In this video we will look at our approach. This will help answer the process question of the course. We can use this simple process approach as a template for our performance tuning techniques.
Tuning Indexes in One of The More Iterative Things We Do. Let's learn how.1:17
Let's define it before we apply it to performance tuning. In this video we will look at our approach. This will help answer the process question of the course. We can use this simple process approach as a template for our performance tuning techniques.

Applying Iteration to Index Tuning. Let's apply our simple process approach to crafting new indexes. This is something you'll be dong often so a quick template will really help us out here.
You can Take This Course Several Ways But I'd Suggest From the Beginning.1:06
There are options but I'd take it in order if I were you. There's a ton of content in this course so let's take minute to talk about where to start.
Let's Take A Few Minutes To Go Over What We've Learned in This Section1:03
Lets wrap this section up. These are valuable summations of the content. These will help cement the facts and lectures we've discussed though out the section.
Create Script Library1:14
Preparation

The Blue Print - Where Do We Start Tuning Our SQL Boxes?2:57
Where should we start our tuning? We have a ton of information to gather, analyze and report on. Let's craft a simplistic blueprint on how to do that. It will really help lessen the feeling of information overload.
Let's Talk About The Importance of Having A Solid Baseline.0:45
What is a baseline? We need to know:

What's the server running like now.

What's it running like after fix 1.

After fixes 2.

You'll need to be able to provide proof that you're approach is providing value.
Our baseline is only as good as the metrics we use to monitor our servers with.1:03
We use a few tools to gather sundry metrics. Performance monitor is one of our core tools to viewing the health of the servers. I use it for gathering hardware related counters specific to cpu, io and memory.
In This Lecture We Cover the Basics of Performance Monitor5:16
What's performance monitor? We need to look at the basics of how performance monitor or "perfmon" works. This is a powerful tool for gathering many metrics.
Create A Custom Data Collector Set for Capturing The "Big Three."3:01
This is how we collect our metrics. We define what we need to capture. What are we going to measure and how to we add those counters to our collector set.
Let's Go Over What We've Covered on Crafting a Baseline0:48
Here we are going to highlight the main features of what performance monitor is and how we created data collector sets to capture our metrics.
Let's Define What Wait Statistics Are. You Must Learn This To Be Senior Level.1:19
We use a few tools to gather sundry metrics. Performance monitor is one of our core tools to viewing the health of the servers. I use it for gathering hardware related counters specific to cpu, io and memory.

Wait Statistic Analysis is one of the yet undiscovered gems of performance tuning analysis. You simply can't analyze SQL Server at a granular level without them. Let's go a basic definition of what they are and how they are created.
Wait Statistics Are Maintained Since The Last Reboot. Let's Learn What Else Is.5:16
Wait Statistics are built up since the last reboot. Let'sf find out how to view them and what some of the limitations of this specific method are.
We Need A Way To Capture Wait Statistics Over A Period of Time4:31
We need a way to capture wait stats over a period of time we specify and here's why.

Wait Statistics are accumulated since the last server reboot.

We may only want to gather them during core hours.

We may know a narrow window when the problem is ocurring and we want to capture for that period only.
Using sp_whoisactive to Capture Data For Quick Analysis4:02
No trace needed. Tracing is very heavy. It captures everything that comes through the pipe. Often times we don't need everything, just a sampling of the transactions. This is a quick way to debug how transactions are flowing.
Capturing IO Related Metrics Over a Period of Time5:38
IO is often our problem. We need a way to capture how the latencies are affecting our files. Once we know that we can pinpoint which database files are suffering the most IO related issues.
What is CrystalDiskMark and How We Can Use it To Speak A Little SAN1:04
My favorite tool for a quick speed check. There will be times when we need speed comparisons on the disks are LUNS on out SQL Servers. This free tool gives us read and write speeds.
How Fast Are Your Disks? Using Crystal Disk Mark to Size Up You Reads and Writes4:32
How fast are our disks or LUNS? There are no magic numbers to use as a baseline but if one LUN or disk differs greatly and that's where you are seeing read and write latencies, CDM will give you some ammunition for your argument.
Learn How to Answer Interview Questions About Performance Tuning4:05
Some interview thoughts. I try to cover possible interview questions specific to the content in our section. Perspective interviewers will want you to know the vernaular and the what steps your going to take to increase the health of their SQL Servers.
Section Summary1:34
Lets wrap this section up. These are valuable summations of the content. These will help cement the facts and lectures we've discussed though out the section.
Creating The Baseline

Instance Level Versus Database Level Tuning and Why It's Critically Important1:20
In this lecture we discuss tuning the instance versus tuning the database. The instance is an installation of SQL Server. If there are 20 databases on an instance and you tune only one then the other 19 may be drowning out your effort.
SQL Server Should Live Alone. Sharing is Not Caring in the Database World.1:17
SQL needs to live a hermetic lifestyle. SQL Server should live alone on production. One instance per server. When we decide that SQL Server should share the price for that decision is often poor performance.
Prioritizing Our Fixes So We Can Make The Best Decisions For Our Databases.1:24
We should be deciding what gets added. Period. In this lecture we discuss whey priortizing our fixes isn't a good idea on the final email or report to management.
Sitting On SP_WHOISACTIVE and Watching How Transactions Flow.5:21
The waiting is the hardest part. A healthy database has an organic flow of transactions. An unhealthy one will have transactions that just sit there waiting on a resource. We need to find our why those transactions are waiting.
What Is sp_whoisactive0:56
The waiting is the hardest part. A healthy database has an organic flow of transactions. An unhealthy one will have transactions that just sit there waiting on a resource. We need to find our why those transactions are waiting.
Understanding Min and Max Memory is Harder Than You Might Have Thought.1:09
What Min and Max mean in the various versions. This one is confusing because it's version dependent and was never well publicized but it's critical we know how to set these levels correctly.
Cofiguring Memory at the Instance Level1:55
Let's configure the memory for the instance.
What is Tempdb Latch Contention?0:50
Latches can be our issue. Latch contention on tempdb data files happens often. We need to know why and what we can do to alleviate it.
What Tempdb Latch Contention Looks Like in sp_whoisactive0:41
Tempdb contention in sp_whoisactive. With our holy grail tool we can view the contention on each of these data files in real time. This become more important with each release and with the number of databases we have on the instance.
Resolving Tempdb Latch Contention5:47
The latch fix is almost an easy button one. We simply do to things:

Add more tempdb data files

Ensure the grow at equal clips. (very important)
The IO Tuning Overview1:03
Subsystem vernacular. DBA don't speak SAN nomenclature. We use terms like "latencies" to describe the read and writes that are ocurring on the individual SQL files. Let's go over them.
Optimize For Adhoc Workloads1:23
The buffer pools is a section of memory that is critical to the database engine. Part of it is carved up into caches. That adHoc cache is often a problem. In this lecture we discuss why.
Turning On Switch For Adhoc Workloads2:58
A true easy button fix. A load of our cache. In this lecture we discuss and easy button fix for taking pressure off the buffer pool. This means more pages in the cache and less time we have to go to disk.
The Worst Performing Queries4:28
We can use DMVs to capture the worst performing queries by the sundry metrics. I like to capture IO related worst performers because so often in the real world the SAN is our nemesis.
Why Too Many Indexes Are Bad and What We Can Do to Solve This Epidemic.1:10
It often worse than you think. Missing indexes do more than just take up space. In this lecture we discuss why that is.
Unused Indexes and Why The Wasted Space Isn't Our Biggest Problem.5:28
Let's clean up the unused indexes. Once we have enough information gathered we can remove the unused indexes. We need to keep in mind that the information we use to make this decision is cleaned out after every reboot. Performance tuners must know the nuances.
What Are Index Statistics and Why Are They So Important To The Optimizer.1:17
Let's not get these index 'statistics' confused with watt statistics. These are statistics that give the optimizer the information it needs to execute your queries correctly with the indexes you've created.
UPDATING Statistics is Easy. Let's Look At How We Can Get It Done.1:18
If the optimizer gets angry, you will pay. There are two main components to the SQL Server engine. The optimizer and the storage engine. The optimizer is responsible for query execution. We need to give it the most up to date information so it can make the most accurate assessment on how to run our queries.
Adding High Impact Indexes is much Harder and More Important than anything else5:21
A great script from Bart Duncan on how to craft high impact indexes at the instance level. This is our "first pass" on hunting down indexes that will have the most immediate impact on our instances.
Rebuilding Indexes - Why Offline is Better. The Less Fragmentations the better.0:37
If you have the option, it's much better to build offline. Building indexes off line is a great feature but it's one that comes at a very high cost. Too high in my opinion. Let's cover the difference in this lecture.
Identifying Incremental Growth and Correcting It4:51
Adjusting the ill fated default growth increment. This one we can blame squarely on Microsoft. Many of the default values are simply wrong for enterprise installations. This is one of them. In this lecture we discuss how to fix that issue.
Interview Questions Specific to Performance Tuning8:40
Some interview ideas. Interview can be stressful. In this audio I discuss various options for creating a conversation specific to performance tuning with the interviewer.
Section Summary2:38
Lets wrap this section up. These are valuable summations of the content. These will help cement the facts and lectures we've discussed though out the section. We covered a lot of information so if any of the bullet points aren't clear then go back and watch the lecture specific to the area in questions. If it's sill not lucid then post a question for me and I'll answer it.
Performance Tuning Final Exam

Thank You For Taking My Course. Let's Wrap This One up.2:50
You did it!!! Congrats. This was a long one. Performance tuning takes a long time master. You have to know many of the mechanics of the underlying engine to understand what and why you are tuning a specific object or objects. We covered a lot. I often suggest that students go back through the course with a notepad and write down anything that they are unsure about.

Requirements

You will need to have SQL Server 2012 or 2014 installed. It can be an express version.
I'll be providing all the scripts and schema for the course. There is no other cost other than the price of the course. Everything else is included or free.
If you don't have a performance tuning script library then we will start one. If you do, then we will add to it.
If you're not familiar with SQL Server fundamentals then this course may not be for you. I do appreciate and understand your enthusiasm and promise this course will be here when you're ready.

Description

Some Course Reviews.

"I've also looked at the database with multiple system monitoring products. But really where do I go from here? and when I find the problem what do I really need to do? Mike really shows you the nitty gritty."

"This was just the course I have been waiting for. Mike's demos and scripts have helped me become a confident tuner. The best thing about this course was that I was able to improve our server performance straightaway."

"I already have a great deal of DBA related experienced in the real world. What this course did for me was help me establish a model, and strategy for database tuning and performance monitoring."

"After taking this course not only will you have a sound performance tuning strategy and be well equipped to establish baselines and apply performance tweaks on your servers, but more importantly, you'll have a strong performance tuning foundation and be poised to take your new skills and understanding to the next level. At such a low price, this course is a no-brainer whether you're a junior or senior DBA. I'm so impressed with the course" - Jamar

Thank you reviewers for your kind feedback.

This course focuses on making SQL Server more performant.

SQL Server is like a high performance sports car. Anyone can drive it but put it in the hands of a skilled performance tuner and you've just gone from average to incredible.

No one wants an average performing system and with the KNOWLEDGE acquired from THIS COURSE your performance tuning acumen won't be average either.

This course will give YOU the foundation to become a skilled PERFORMANCE TUNER.

WE will start by building a baseline. How can you measure YOUR success without knowing how poorly the system was before you arrived?

Baselines give us a before and after snapshot of the system we are going to tune.

Once thought of as an art, performance tuning is nothing more than a series of processes that seek to accomplish TWO basic goals.

The FIRST one is to increase the response time for a given transaction or set of transactions.

The SECOND one is reduce resource consumption. On the surface this sounds simple but nothing could be further from the truth.

A database is a collection of objects. The most fundamental and most important is the table object. Users interact with tables via transactions. Most online transaction processing databases are read and write heavy. We put data into to our tables or INSERT it and we read that data or SELECT it.

Let's use an online ordering system as an example. I need a product and I've found it on x(dot)com. Before I can order it I need to give x(dot)com a few things. My name, address, product I'm ordering and money of course. All this information is INSERTED into various tables. Once I've provided all the necessary information someone at x(dot)has to SELECT or read that data so my order can be filled.

Imagine for a minute that I make several attempts to provide x(dot)com with my name, address… etc. However, after three attempts the information can't be saved to the database and I receive multiple errors. Most people won't try three times unless the product can't be purchased somewhere else. Most users will look elsewhere after that second failure.

This course WILL provide YOU with the foundation necessary to ensure data can be saved and retrieved so the database will have an organic flow of transactions.

ENROLL now. Let's get started!!!

Who this course is for:

If you have basic understanding of SQL Server and want to learn how to tune and optimize a SQL Server then this course is for you.
This course is not a beginner’s course. Most of the students will be junior to mid-level SQL Server resources.
You'll need to be familiar with transact SQL and have a strong grasp navigating SQL Server Mangement Studio.
Using the collegiate scale as a guide this course will be a 300 level course. Please keep in mind this is a 101 level course for performance tuning and optimization, which is an advanced topic within the umbrella of the DBA.

How to Become a World Class SQL Server Performance Tuner

What you'll learn

Explore related topics

Course content

Preparation11 lectures • 13min

Creating a Baseline15 lectures • 46min

Performance Tuning23 lectures • 1hr 2min

Conclusion1 lecture • 3min

Requirements

Description

Who this course is for: