How to Become a World Class SQL Server Performance Tuner

Use These Scripts in Production Right Now
4.1 (75 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.
778 students enrolled Bestselling in SQL Server
$19
$20
5% off
Take This Course
  • Lectures 52
  • Length 2 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2015 English

Course 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."

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!!!

What are the 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.

What am I going to get from this course?

  • 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%.

What 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.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Preparation
01: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.

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

Article

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
Article

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.

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.

01: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.

01: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.

Article

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.

Preparation
10 questions
Section 2: Installation
Download SP_WHOISACTIVE From This Location
Article
04:53

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.

Setting Up The Udemy Database.
Article
Article

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.

04:25

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.

Article

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 3: Creating a Baseline
Article

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.

00:45
  • 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.
01: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.

05: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.

03: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.

Article

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.

01: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.

05: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.

04:31

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.
04: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.

05: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.

01: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.

04: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.

04: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.

Article

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
10 questions
Section 4: Performance Tuning
01: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.

01: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.

01: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.

05: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.

01: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.

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

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

Article

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.

Article

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.

05:47

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)
01: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.

01: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.

02: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.

04: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.

01:10

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

05: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.

01: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.

Article

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.

05: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.

Article

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.

04: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.

08: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.

Article

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
19 questions
Section 5: Conclusion
Article

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.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Mike West, 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.

Ready to start learning?
Take This Course