Performance Tuning SQL Server On RDS

Le's create a separate script library because certain scripts will work on RDS that won't run on our on premise boxes and vice versa.

Having them separated will make our lives easier.

These two tools are invaluable for learning how SQL Server works.

I use them in almost all of my courses.

No matter where SQL Server resides, the core engine is the same.

The job of SQL Server is to process transactions and store information on those transactions somewhere.

Let's learn a few ways to craft a highly functional script library.

Let's review what we've covered in Section 2.

We don't have access to performance monitor on our RDS instances but Amazon does provide a two week view of our most critical metrics.

Let's learn how to view that data.

Capturing the specifics of the big three is the core of what we will do to tune our RDS insances.

Often we will need to capture wait stats for a very specific period.

This script will help us do just that.

Let's learn how to capture our wait stats during a specific period of time.

Let's wrap up what we've learned in Section 3.

We need to know what latch contention is before we can erradicate it.

Every production instance should have 4 tempdb data files.

This is how we set that up with our on premise databases.

We can't add files to tempdb in RDS so we are going to have to get our hands dirty.

Almost every single instances I've looked at in the past decade had indexes that were hurting performance.

It's time to clean them up everywhere.

There are a few caveats to cleaning up our unused indexes.

Let's learn what they are in this lesson.

Fixing cache bloat is very easy but first let's learn what the caches are and what's often the bloater.

This is an easy button fix with our on premise instances and it's not much harder on RDS.

Let's learn how to flip this switch.

We don't like growth but we can't stand incremental growth.

Let's learn what we can do about it in lecture 26.

This is often a hot button issue but for RDS we only have a few real options.

Let's learn what those are.

This goes hand in hand with our MAXDOP setting.

Let's bump it up everywhere.

If we can we should be growing all of our files after hours so we don't grow during core hours.

Let's learn how to do this.

It's the same with RDS and on premise.

I like offline rebuilds for the cleanest indexes but online reorgs have begun to win me over.

In this lecture I'll tell you why I loathe online rebuilds

If you don't give the optimizer what it needs you'll pay the price.

Let's learn what index stats are.

Let's create a job to keep our statistics as updated as we can.

Reorgs are online and have gotten a lot cleaner in the latest versions. (2014)

Easily one of the top features in RDS.

I can add memory and cpu to my RDS instance with the click of a button.

Let's learn how.

Here's a checklist of items covered in the course we should analyze then apply to our instances.

It's a word document.

Let's wrap up what we've learned in this section.

Congratulations and thank you!!!!