Courses

Read Committed Snapshot and Snapshot Isolation (46 minutes)

on May 25, 2018

This course is an overview of why Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation levels are critical for scaling workloads in SQL Server.

In this session, I use diagrams and slides for most of the discussion, with a short amount of demo. For a deeper dive, complete the Read Committed is Bonkers course first, and then skip the “Welcome” lesson in this course – it is a recap of the problems with Read Committed. Follow this course up by taking the course Snapshot Isolation Against Availability Group Secondaries.

Speed Up the Popular Names Query SQLChallenge (46 minutes)

on May 11, 2018

You’ve got a query that is just too slow! This SQLChallenge focuses on tuning a query that ranks baby names by popularity across multiple years. Your goal is to get the query under 500 logical reads by rewriting only the T-SQL (no index changes allowed). You’ll learn how to work with parameterized plans, explore quick fixes versus scalable solutions, and step through a real-world query tuning process that pushes filters down into index seeks and optimizes windowing functions.

Read Committed is Bonkers - Webcast Recording (46 minutes)

on May 10, 2018

If you haven’t thought much about isolation levels in SQL Server, chances are your application can easily return incorrect data to your users.

In this introductory session, you will learn:

What isolation levels are
What the read committed isolation level is, and why it’s still SQL Server’s default isolation level for on-premesis and PAAS installations of SQL Server
How easy it is to demonstrate incorrect results when using the read committed isolation level, and why it happens
Steps to take to begin analyzing isolation levels in your environment

Snapshot Isolation Against Availability Group Secondaries (28 minutes)

on May 10, 2018

In just half an hour, you’ll see how queries against a readable secondary in an Availability Group are automatically escalated to Snapshot Isolation – whether you ask for it or not!

In this course we investigate SQL Server’s version store against a readable secondary, as well as learn about the “low water mark for ghosts” in an Availability Group.

Forcing Parallelism SQLChallenge (48 minutes)

on April 20, 2018

A database administrator has changed Cost Threshold for Parallelism on the instance, and you’ve got a query that’s slowed down.

Your challenge: can you coerce SQL Server into giving the query a parallel plan again, without changing instance wide settings or re-writing the query?

Why Table Partitioning Does Not Speed Up Query Performance - With One Exception (52 minutes)

on March 11, 2018

Learn why SQL Server’s table partitioning feature won’t make your queries against disk-based rowstore indexes faster– and may even make them slower.

Table partitioning can absolutely be worth implementing, but it may be for different reasons than you think! In this session, you will learn what partition elimination really does, why table partitioning makes some queries trickier to optimize, and where table partitioning shines - and in which case it can make queries faster.

Auto Tuning with Automatic Plan Correction in Query Store (1 hour 8 minutes)

on February 23, 2018

WARNING: I do NOT currently recommend enabling the Automatic Plan Correction feature due to issues described in my post, Automatic Plan Correction Could be a Great Auto Tuning Feature for SQL Server. Here’s why it Isn’t. Want a built-in assistant performance tuner in SQL Server?

Your new assistant makes recommendations about queries that need speeding up? In SQL Server 2017+ Enterprise Edition and Azure SQL Database, Query Store can be your new Junior DBA. You get to choose whether or not to allow your assistant to try out and test its suggestions, or whether to manually review them yourself.In just over an hour of videos, we’ll explore how the Automatic Plan Correction feature in Query Store works: how to configure it, what it does, and steps to get started using it.