Building an Automated SQL Server Performance Tuning Engine
4.6 (10 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.
119 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Building an Automated SQL Server Performance Tuning Engine to your Wishlist.

Add to Wishlist

Building an Automated SQL Server Performance Tuning Engine

Real World Automated SQL Server Performance Tuning
4.6 (10 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.
119 students enrolled
Created by Mike West
Last updated 2/2017
English
Curiosity Sale
Current price: $10 Original price: $35 Discount: 71% off
30-Day Money-Back Guarantee
Includes:
  • 1 hour on-demand video
  • 3 Articles
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • At the end of the course you'll be able to take what you've learned and the code I've provided to completely automate tuning a production SQL Server instance.
  • The students will have a solid understanding of how the process of performance tuning at the instance level works.
View Curriculum
Requirements
  • A solid understanding of SQL Server
  • Basic C# skills would be beneficial but aren't required.
  • An Instance of SQL Server Installed
  • Visual Studio 2015 Installed
Description

Recent Reviews: 

"Mike has given a great template that will allow DBAs to ease their admin tasks. Really well done." -- Doug Alderson

"This is a great tool to help DBAs and non- database system administrator for performance fine tuning" -- Bernard Bernard

Welcome to Building an Automated SQL Server Performance Tuning Engine.

In this course, we are going to build a completely automated application for tuning a SQL Server instance.

The application has been running on live production instances for several years. These SQL Servers have needed no performance tuning attention since the tool was installed.

I’ve created the software and named it SQL Black Box. I choose the name because for most in the organization the inner workings of the tool will be a black box.

The software in the course will be written in C# and the SQL Server code will obviously be transact-sql.  

This is not a step by step on how I authored the code. This course assembles the sundry components that make up the application. 

You’ll be able to take the application and install it on any SQL Server you want.  You won’t be able to resale any portion of the code but that’s the only restriction.

Regardless of your skill level this application will ease your administrative burden.

Advanced students will be able to choose what modules they want to implement. I use the term module within the software to denote individual unit of work.

For example, there will be a module that removes exact duplicates and there will be a module that adds high impact indexes to the instance.

Please note I used the word instance and not databases. We don’t tune databases; we tune the instance they are on because all the critical resources are shared at that level.

Those students newer to SQL Server will be able to change a couple of lines of code, build the modules and then use task scheduler to automated their schedules.

Thank you for your interest in Building an Automated SQL Server Performance Tuning Engine and we will see you in the course.

Who is the target audience?
  • Anyone who wants to learn how to build an automated SQL Server performance tuning engine.
  • SQL Server DBAs, Developers and Network Administrators who want to automate the tuning of their SQL Servers.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
20 Lectures
01:15:06
+
What is our Goal?
8 Lectures 17:43

In this course you going to learn how to automate SQL Server performance tuning. 

Preview 01:40

What is the goal of SQL Black Box? 

What is performance tuning specific to an OLTP environment. 

Preview 02:09

What does the application do? 

Let's get granular in this lesson. 

Preview 04:27


What's a module? 

What language is the app written in and why? 

Preview 03:37

Learning how to properly use sp_whoisactive is the difference between the average and the superior DBAs. 

It's the one tool that can tell you what's going on at the instance level in minutes. 

Let's learn about it in this short lesson. 

SPID Watching
04:07

Course Downloads
00:05

Summary
00:37

Quiz
5 questions
+
Building The Engine
12 Lectures 57:22

In this lesson let's create the stored procedure that adds the indexes at the instance level. 

Add High Impact Stored Procedure to Master
05:23

In this lesson let's create the C# application to call the stored procedure that adds the high impact indexes. 

I'll also show you how to test each module with profiler before it goes into production. 

High Index Impact Module
09:33

Is the index we just created being used? 

If not we probably shouldn't have created it. 

The good news is the stats on almost every index the tool creates will show marked improvement. 

Index Usage Statistics
04:05

In this lesson let's create the application we will call to rebuild all the indexes on all the user databases on the instance. 

The Rebuild Indexes Module
07:47

In this lesson let's create the application that will update all the stats on all the user defined databases. 

The Update Stats Module
05:51

In this lesson let's learn about the three modules I'd suggest you start with. 

The 3 Modules to Start With
02:20

In this lecture let's fix the database growth settings. 

I don't think you'd have to create a module unless you were going to implement a more dynamic solution. 

The Database Growth Module
07:35

Drop Exact Duplicates Module
05:18

The Purge MSDB Module
04:14

Create Your Own Modules
01:03

Adhoc Worklaods Too High?
02:32

Summary
01:41
About the Instructor
Mike West
4.1 Average rating
2,610 Reviews
43,286 Students
40 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

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.