How to Become a World Class SQL Server Performance Tuner
3.8 (92 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
886 students enrolled
Wishlisted Wishlist

Please confirm that you want to add How to Become a World Class SQL Server Performance Tuner to your Wishlist.

Add to Wishlist

How to Become a World Class SQL Server Performance Tuner

Use These Scripts in Production Right Now
Bestselling
3.8 (92 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
886 students enrolled
Created by Mike West
Last updated 9/2015
English
Current price: $10 Original price: $20 Discount: 50% off
6 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 1.5 hours on-demand video
  • 13 mins on-demand audio
  • 16 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Have a coupon?
What Will I 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%.
View Curriculum
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 is the target audience?
  • 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 52 Lectures Collapse All 52 Lectures 02:08:28
+
Preparation
7 Lectures 05:59

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.

https://thesqlspot.leadpages.co/dec92015/

Preview 01:37

A little about me. Your instructors experience and background is important. Take a moment to look at mine.

Why take a course from me:

  1. Highly Certified
  2. Twenty years of IT experience
  3. Fifteen years as a DBA.
  4. Author
  5. Community contributor
  6. Performance Tuning is my niche
Preview 00:49

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.

All of The Course Content Can Be Downloaded Right Here. Download it Now.
00:01

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.

Preview 00: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.

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.

Preview 01:17

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.

Preview 01:06

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.

Let's Take A Few Minutes To Go Over What We've Learned in This Section
00:30

Preparation
10 questions
+
Installation
6 Lectures 11:54
Download SP_WHOISACTIVE From This Location
00:10

Adding sp_whoisactive to our list of tools. This is, in my opinion, the greatest free tool any SQL Server DBA has at their disposal.

It's so important my first course is devoted to understanding how transactions flow through the engine using sp_whoisactive.

It's free. It's safe. It's simple to use.

Installing sp_whoisactive and Applying Occam's Razor To It.
04:53

Setting Up The Udemy Database.
00:33

All senior level resources have one thing in common. They rely heavily on their script library. Let's set one up and now and I'll include some tips for keeping them accessible and pragmatic.

Let's Set Up Our Script Library and Discuss Some Tips and Tricks
01:16

Creating load for our sql server tuning examples. Simulating a SQL Server OLTP load is not easy. This tools does a great job and it's free. There are some bugs but only a few and by the way, it's free.

Install and Configure OLTP Load Generator
04:25

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.

Section Summary
00:37
+
Creating a Baseline
15 Lectures 44:45

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.

The Blue Print - Where Do We Start Tuning Our SQL Boxes?
02:22

  • What is a baseline? We need to know:
    1. What's the server running like now.
    2. What's it running like after fix 1.
    3. After fixes 2.
    You'll need to be able to provide proof that you're approach is providing value.
Let's Talk About The Importance of Having A Solid Baseline.
00:45

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.

Our baseline is only as good as the metrics we use to monitor our servers with.
01:03

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.

In This Lecture We Cover the Basics of Performance Monitor
05:16

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.

Create A Custom Data Collector Set for Capturing The "Big Three."
03:01

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 Go Over What We've Covered on Crafting a Baseline
00:37

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.

Let's Define What Wait Statistics Are. You Must Learn This To Be Senior Level.
01:19

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.

Wait Statistics Are Maintained Since The Last Reboot. Let's Learn What Else Is.
05:16

We need a way to capture wait stats over a period of time we specify and here's why.

  1. Wait Statistics are accumulated since the last server reboot.
  2. We may only want to gather them during core hours.
  3. We may know a narrow window when the problem is ocurring and we want to capture for that period only.
We Need A Way To Capture Wait Statistics Over A Period of Time
04:31

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.

Using sp_whoisactive to Capture Data For Quick Analysis
04:02

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.

Capturing IO Related Metrics Over a Period of Time
05:38

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.

What is CrystalDiskMark and How We Can Use it To Speak A Little SAN
01:04

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.

How Fast Are Your Disks? Using Crystal Disk Mark to Size Up You Reads and Writes
04:32

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.

Learn How to Answer Interview Questions About Performance Tuning
04:05

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.

Section Summary
01:13

Creating The Baseline
10 questions
+
Performance Tuning
23 Lectures 59:19

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.

Instance Level Versus Database Level Tuning and Why It's Critically Important
01:20

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.

SQL Server Should Live Alone. Sharing is Not Caring in the Database World.
01:17

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.

Preview 01:24

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.

Sitting On SP_WHOISACTIVE and Watching How Transactions Flow.
05:21

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.

Understanding Min and Max Memory is Harder Than You Might Have Thought.
01:09

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_whoisactive
00:56

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 is Tempdb Latch Contention?
00:50

Correctly configuring the Max and Min memory settings. We need to leave memory for the OS and we also need to set a basement level for SQL Server.

Cofiguring Memory Correctly at the Instance Level
00:34

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.

What Tempdb Latch Contention Looks Like in sp_whoisactive
00:27

The latch fix is almost an easy button one. We simply do to things:

  1. Add more tempdb data files
  2. Ensure the grow at equal clips. (very important)
Resolving Tempdb Latch Contention
05:47

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.

The IO Tuning Overview
01:03

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.

Optimize For Adhoc Workloads
01:23

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.

Turning On Switch For Adhoc Workloads
02:58

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.

The Worst Performing Queries
04:28

It often worse than you think. Missing indexes do more than just take up space. In this lecture we discuss why that is.

Why Too Many Indexes Are Bad and What We Can Do to Solve This Epidemic.
01:10

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.

Unused Indexes and Why The Wasted Space Isn't Our Biggest Problem.
05:28

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.

What Are Index Statistics and Why Are They So Important To The Optimizer.
01:17

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.

UPDATING Statistics is Easy. Let's Look At How We Can Get It Done.
00:54

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.

Adding High Impact Indexes is much Harder and More Important than anything else
05:21

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.

Rebuilding Indexes - Why Offline is Better. The Less Fragmentations the better.
00:26

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.

Identifying Incremental Growth and Correcting It
04:51

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.

Interview Questions Specific to Performance Tuning
08:40

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.

Section Summary
02:13

Performance Tuning Final Exam
19 questions
+
Conclusion
1 Lecture 02:24

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.

Thank You For Taking My Course. Let's Wrap This One up.
02:24
About the Instructor
Mike West
4.2 Average rating
1,665 Reviews
29,079 Students
32 Courses
SQL Server Evangelist

I've been a production SQL Server DBA most of my career.

I've worked with databases for over two decades. I've worked for or consulted with over 50 different companies as a full time employee or consultant. Fortune 500 as well as several small to mid-size companies. Some include: Georgia Pacific, SunTrust, Reed Construction Data, Building Systems Design, NetCertainty, The Home Shopping Network, SwingVote, Atlanta Gas and Light and Northrup Grumman.

Experience, education and passion

I learn something almost every day. I work with insanely smart people. I'm a voracious learner of all things SQL Server and I'm passionate about sharing what I've learned. My area of concentration is performance tuning. SQL Server is like an exotic sports car, it will run just fine in anyone's hands but put it in the hands of skilled tuner and it will perform like a race car.

Certifications

Certifications are like college degrees, they are a great starting points to begin learning. I'm a Microsoft Certified Database Administrator (MCDBA), Microsoft Certified System Engineer (MCSE) and Microsoft Certified Trainer (MCT).

Personal

Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.