Advanced SQL Server Performance Tuning
4.1 (100 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.
964 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Advanced SQL Server Performance Tuning to your Wishlist.

Add to Wishlist

Advanced SQL Server Performance Tuning

A Pragmatic Real World Approach To Tuning SQL Server.
4.1 (100 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.
964 students enrolled
Created by Mike West
Last updated 7/2015
Current price: $10 Original price: $20 Discount: 50% off
5 hours left at this price!
30-Day Money-Back Guarantee
  • 1.5 hours on-demand video
  • 15 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
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 dive a little into performance tuning. We will look caching, hints, filters, trace flags and best coding practices.
  • In the course we will use statistics and query plans to analyze the effectiveness of our indexes.
View Curriculum
  • 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'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.

Please note: This is a continuation from my first course titled, "How to Become a World Class SQL Server Performance Tuner." I highly recommend you take that course first.

"I've been working as SQLServer dba for +8 years, but I'm still learning every single day... So, thanks a lot for your courses, videos and tutorials, they really help me."


"He uses clear cut examples to illustrate real world scenarios. Mike is one of the few SQL Server instructors that seems very focused on performance issues and I'm really glad to have stumbled onto his courses."


This course focuses on making SQL Server perform more optimally.

In my first course we laid the groundwork by tuning OUR instances with a top down approach.

WE will continue on that path with this course.

Even though the course has the word “Advanced" in it, there's NOTHING COMPLICATED about taking what's in the course and applying it in the real word.

It's “advanced" in that I'll take more time explaining how to implement these recommendations.

The course is still very much a "buttons and Knobs" course. This simply means WE won't dive too deeply into the minutia. We are still going to focus on items that are easily implemented. Some features will have a level of contention associated with them in the SQL Server community. It will be up to you decide to implement or not.

Additionally, some of the concepts may be new to even more senior level resources so I want to make sure I'll cover the why of the topic as well as the how.

This course will give YOU deeper insight into what it takes to become a skilled PERFORMANCE TUNER.

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.

Take the next step on YOUR performance tuning journey and SIGN UP now.

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 mid-level to senior SQL Server resources.
  • You'll need to be familiar with transact SQL and have a strong grasp navigating SQL Server Management Studio.
  • My first is not required but it will help build a foundation for some of these more complicated topics.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
38 Lectures
4 Lectures 04:35

We are going to cover 4 main areas during throughout this course.

  1. Caching Issues
  2. Hints and Filters
  3. Trace Flags
  4. Real World Coding Best Practices

While this course is more advanced than my previous one on performance tuning my target audience remains mid to senior level SQL Server resources.

Preview 02:32

The deeper you delve into the internals of SQL Server the more nuances arise. All of my recommendations should be tested in each environment.

For example, many senior level SQL Server DBA add trace flags to their default builds. However, if you don't understand how the flag works or what the secondary effects are then please don't add it.

Preview 00:52

A little about me. It's the same canned BIO for all my courses so If you've read it once you can skip it here. I do highly recommend you join the LinkedIN blog. I blog there regularly and interact with some of the most skilled SQL resources on the planet.

Preview 00:48

Section 1 Summary
Tools. These Two Tools Really Help Us Get A Deeper Grain To Our Tuning
5 Lectures 07:06

This is one of the most used approaches to performance tuning. If showplan analysis does't give us what we are looking for then the reduction in logical reads often will. Keep in mind, you are looking for wider ranges here. If you have approach that has 50 logical reads and another one with 65 then that's not much of a difference. If you cut the logical reads in half.. that's a big difference.

Preview 03:16

This isn't a course on query plan analysis but we will look at a few. I recommend using this free tool for viewing showplans. It's superior to the native one.

Query Plans With Plan Explorer. The Second Best Free Tool On The Market.

This is Microsoft's learning database. It's available on codeplex and is used in many tutorials and demonstrations. We need to download it and attach or restore it to our instance we will be learning on.

Adding AdventureWorks. It's Free and Easy. We Will Use It In Our Demos.

This is where you download the course content. Some of the content has not been included because of it's simplicity.

NOTE: Please keep in mind simple queries and code is not included in downloads. Typing those out will help you cement them in memory.

Download Content Here

Summary 2 Summary

Section 2 Quiz
10 questions
Caching Issues
9 Lectures 23:29

All roads for most of our transactions in SQL Server lead to and from the buffer pool. Understanding the basics is paramount to understanding performance tuning concepts.

What is the Buffer Pool?

SQL Server has a pool of memory that is used to store both execution plans and data buffers. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. The part of the memory pool that is used to store execution plans is referred to as the procedure cache

High Level Structure of Procedure Cache

Your plan cache stores details about all the SQL statements that have been executed over time.Let's take a look at the columns we need to be familiar with for analyzing it.

Procedure Cache Architecture

Using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans.

NOTE: Many of these examples have very simple stored procedures or DBCCs. I haven't included these in the content download because of their simplicity.

Simple Parameterization

Let's walk through what parameter sniffing is and why it's a problem.

Parameter Sniffing Demonstration

Problems arise when the values queried in a table are not evenly distributed. If one parameter value returns 10 rows and another parameter value returns 10,000,000 rows, it would be best if SQL Server used different execution plans for the different values of the parameter. So... how do we fix this issue.

Fixing Parameter Sniffing

Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected.

What Are Plan Guides?

Let's create some simple plan guides. Plan guides can be created in SSMS or via transact.

Plan Guides: Demonstration

Let's review what we've covered.

Section 3 Summary

Section 2 Quiz
10 questions
Hints and Filters
5 Lectures 13:26

If you want to compare the effectiveness of an index you recently added then this trick will give you a side by side comparison. You can screen print it to show index effectiveness.

Using Index Hints For Comparing Index Effectiveness

A filtered index is an often unused tool that can have vast improvements on performance.

Filtered Indexes

Sparse columns work well where a high proportion of the data in a column is NULL, because negligible storage space is used for the NULL representation. A table that is wide in theory can be made narrow in practice by implementing sparse columns, and the addition of filtered indexes provides effective searches on the sparse data. Sparse columns support the use of check constraints, as long as the constraints allow NULL values (a sparse column requirement).

Filtered Indexes With Sparse Columns

In this video we significantly reduce logical reads and save a lot of space on the location of the filtered index on disk.

Filtered Index With Massive Reduction In Logical Reads

Section 4 Summary

Section 4 Quiz
10 questions
Trace Flags. If You Feel Safe Using Them - And I Do, Then Start Here.
4 Lectures 08:35
Trace Flag: -T4199

Trace Flag: -T1117

Trace flag 3226 simply stops SQL Server from writing backup successful messages to the error log.

Trace Flag: -T3226

Section 5 Summary

Section 5 Quiz
10 questions
Real World Coding Best Practices Put To The Test
8 Lectures 31:48

Does returning more columns in your queries really matter? Are you sure?

SELECT * versus SELECT Row1, Row2, Row3.

Does Using Fewer Columns Really Matter?

It matters alright but not for the reason you may think it does. Why using schema qualified names in your code is a great idea.

Schema Qualified Objects

It one of the most used "best practices" when discussing transact SQL. Does it do what you think it does and should we use it?

PS - We should use it but why?

Does SET NOCOUNT ON Really Help?

One long DELETE or UPDATE will hold many other transactions hostage. Batching up your deletes and updates is a great practice in jobs and in code.

Batching Up Deletes And Updates Is A Must

SQL Server does a lot of things really well. Sorting is not one of them. Sort using the language you are authoring your application in.

Stop Sorting In The Database - It's Killing Your Queries

Most applications are littered with Implicit Conversions. It should be this way. Simply matching the columns in the table with your code helps eliminate this.

Fixing Implicit Conversions

I'm not really interested in a side by side performance comparison between temp tables, table variables and CTEs, but I'd like to take the stress off of tempdb. This is one really straightforward way to accomplish that.

Taking The Load Off The Overburdened tempdb Database

Section 6 Summary

Section 6 Quiz
10 questions
2 Lectures 02:13

If you know what it does then you'll probably enable it. Let's cover what this "knob" does and why we should turn it on.

I've added some comments from SQL Server experts who help reinforce it's usage.

Enable Auto Update Stats Async

Section 7 Summary
1 Lecture 00:50

Congratulation!! You've completed this Advanced Course On SQL Server Performance Tuning.

Thank you! Let's Wrap Up This Course.
About the Instructor
Mike West
4.2 Average rating
2,941 Reviews
49,135 Students
42 Courses
SQL Server and Machine Learning 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 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).


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