Topics

Database administration

Who Made That Schema Change? An Event Notification SQLChallenge (27 minutes)

In this challenge, you get to take a crack at creating an Event Notification to track schema changes.

We covered DDL Triggers in a past SQL Challenge. DDL Triggers are tightly coupled with the commands that call them: like other triggers, the code in the trigger becomes part of the transaction of the code that causes the trigger to fire. Event Notifications are different: even if the event notification fails, the command that causes it to fire may go on none-the-wiser. That’s often desirable (but not always).

Who Made That Schema Change? A DDL Trigger SQLChallenge (28 minutes)

In this challenge, you get to take a crack at writing a DDL Trigger to track schema changes.

DDL Triggers are tightly coupled with the commands that call them: like other triggers, the code in the trigger becomes part of the transaction of the code that causes the trigger to fire. For this reason, you are challenged not only to create a DDL trigger, but also to test the trigger by also causing the trigger to fire when run in the context of a specific user. Don’t worry, there’s code for it all in the challenge.

How to Decode Memory Pressure (4 hours)

SQL Server doesn’t make it easy to see if your SQL Server is under memory pressure, or if someone goofed when configuring the memory settings on your critical production instances.

Note: this webinar was recorded in 2018. This topic is not as “evergreen” as some: note that memory management changes as both SQL Server and the operating system it is on evolves.

How to Decipher CXPACKET Waits and Control Parallelism (4 hours)

Learn to tune parallelism and interpret CXPACKET waits in SQL Server.

Tuning parallelism has always been complicated — and with the introduction of new CXCONSUMER waits, it’s only getting more tricky to interpret.

XEvents SQLChallenge: Create an Extended Events Trace (55 minutes)

Build your skills with Extended Events in this hands-on SQLChallenge. You’ll set up a problem scenario in your test instance, then configure an Extended Events trace that captures the problem. Along the way, you’ll learn when traces will capture events and when they won’t, understand event filtering and configuration options, and work through sample solutions that demonstrate different approaches to Extended Events trace design.

Follow the script to set up a problem in your test instance of SQL Server. Then, your challenge is to configure a trace that will capture the problem – and also learn more about when the trace will capture an event, and when it won’t.

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

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.

Index design and tuning

Deduplicate Indexes - Level 1 SQLChallenge (56 minutes)

Your challenge is to de-duplicate the indexes on a table in a sample database. Can you predict which indexes are the safest ones to drop? In this hands-on SQLChallenge, you’ll learn how to script out all indexes, group them for analysis, identify duplicate and redundant indexes, and determine which ones can be safely removed. You’ll work through the challenge setup, analyze index structures and usage patterns, and review sample solutions that demonstrate different approaches to index deduplication.

Index Design SQLChallenge - One Year Wonders (50 minutes)

This challenge comes in three possible levels of difficulty: choose one or work through them all!

Level 1: design the best disk based nonclustered rowstore index for the sample query– in this case, “best” is defined as reducing the number of logical reads as much as possible for the query. Design only one index without using any more advanced indexing features such as filters, views, etc. Make no schema changes to the table other than creating the single nonclustered index.

Learn Indexing by Solving Problems (7 hours 23 minutes)

SQL Server asks for a lot of indexes — but it does NOT always ask for the right index! You need to know the essential principles of index design to create the best nonclustered index, filtered index, or indexed view to speed up your critical queries.

_This live virtual seminar was recorded in June 2018._This course teaches you the fundamentals of creating disk-based, rowstore indexes in SQL Server in two four-hour sessions.

How Index Keys and Includes Work (1 hour)

Build your powers of index design by learning how indexes are structured in SQL Server.

Understanding how indexes work will help you create effective indexes for your queries with less guess-work. This course teaches you:

Why can you seek on key columns?
Where are included columns written, and how can you use them?

Indexing for Windowing Functions (45 minutes)

Get the best performance for your windowing functions. Windowing functions like ROW_NUMBER(), RANK(), and aggregate functions with OVER() clauses give you great flexibility for analyzing data, but they can be tricky to index effectively. In this course, you’ll learn how to design indexes for windowing functions, understand when batch mode is important for performance, compare Window Spool versus Window Aggregate operators, explore nonclustered columnstore indexes, and discover what indexed views can do for windowing function performance.

Windowing functions give you great flexibility for analyzing data in SQL Server, but can be tricky to index.

Execution Plans: Partitioned Tables and Columnstore Indexes (1 hour 30 minutes)

Execution plans are incredibly helpful when it comes to tuning queries using partitioned indexes and columnstore indexes – but when you look closely, you’ll notice that some things are very weird!

Learn how to see:

How many partitions have been accessed by a query
- In actual plans
- What indications you can find for partition elimination in estimated/cached plans
When SQL Server will ’lie’ about the partition count– and what that means
When “0 rows” is really more than 0 rows
The basics of batch mode vs row mode operators in execution plans
When rowgroup elimination happens, and how this compares to partition elimination

Why Creating an Index Can Slow Down a Query (1 hour 30 minutes)

Sometimes performance can go badly wrong if SQL Server has an imperfect index for an query.

In this session, I show you a query whose performance regresses after you add a nonclustered index. Your challenge is to figure out why the query got slower.

Try your hand at speeding up the query by tuning the TSQL. Afterward, I explore the query execution plan with you and walk you through multiple potential solutions, showing the pros and cons of each one.

Locking and blocking

XEvents SQLChallenge: Create an Extended Events Trace (55 minutes)

Read Committed Snapshot and Snapshot Isolation (46 minutes)

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.

Read Committed is Bonkers - Webcast Recording (46 minutes)

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)

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.

Defuse the Deadlock SQLChallenge (23 minutes)

Sharpen your skills at fighting blocking and deadlocks. In this hands-on challenge, you’ll learn how to reproduce a deadlock, read and interpret deadlock graphs (including understanding where the graphical display can be misleading), and design indexes that prevent deadlocks from occurring. You’ll work with scripts to reproduce the deadlock scenario, analyze the lock contention, and explore multiple strategies for defusing the deadlock using index design.

You are equipped with scripts and a video showing you how to reproduce a deadlock, plus a copy of the deadlock graph.

The Dirty Secrets of NOLOCK (50 minutes)

What happens when you use NOLOCK hints in your code, or set your isolation level to READ UNCOMMITTED in SQL Server?

Learn the dirty secrets and potential uses of NOLOCK. In this course, you’ll learn what NOLOCK means, why NOLOCK can return incorrect results (and other problems), what allocation order scans are (and how to get them), and other risks and options for reading uncommitted data.

Repeatable Read and Serializable Isolation Levels (45 minutes)

Serializable and Repeatable Read isolation levels offer protections so your users won’t see weird or incorrect data – but there are tradeoffs for those protections.

In this course, you’ll dig into:

How to tell if your existing applications are using these types of isolation levels
When you might want to raise your isolation level in SQL Server
The tradeoffs you make if you choose serializable or repeatable read with disk based tables.

Troubleshooting Blocking and Deadlocks for Beginners (2 hours 10 minutes)

Your SQL Server is slow, and you suspect blocking. Try the course quizzes anytime: blocking diagnosis, troubleshooting retroactively, deadlocks

You need to prove if blocking really is the culprit and set up simple, lightweight monitoring using free tools and scripts to find the queries causing your blocking problems.

Partitioning

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

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.

Execution Plans: Partitioned Tables and Columnstore Indexes (1 hour 30 minutes)

Learn how to see:

How many partitions have been accessed by a query
- In actual plans
- What indications you can find for partition elimination in estimated/cached plans
When SQL Server will ’lie’ about the partition count– and what that means
When “0 rows” is really more than 0 rows
The basics of batch mode vs row mode operators in execution plans
When rowgroup elimination happens, and how this compares to partition elimination

Problem Queries in Table Partitioning (1 hour 30 minutes)

Some queries may get slower after you partition your tables: the SQL Server optimizer doesn’t always use indexes the same way after those indexes are partitioned into chunks.

Table partitioning is a fantastic tool to help you manage tables with skyrocketing rowcounts. SQL Server 2016 SP1 made table partitioning available in Standard Edition, so you may be planning to add partitioning to your database. This course teaches you to use execution plans to troubleshoot regressed queries using partitioned tables. You will learn what “non-aligned” indexes are, how to tell how many partitions a query is really using, and see a variety of methods to speed up your queries.

Query tuning

Tuning a Stored Procedure - SQLChallenge (1 hour 10 minutes)

Your challenge is to identify which statement is slowing down our stored procedure the most, then tune the code to speed it up.

You may change the query that is slow as well as anything else in the procedure that will help you make that statement faster.

In the solution videos, I’ll step through multiple strategies to figure out which statement in the procedure is slowing it down the most - because in real life, you need to have a whole bag of tricks in different situations.

Speed Up the Popular Names Query SQLChallenge (46 minutes)

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.

Forcing Parallelism SQLChallenge (48 minutes)

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)

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.

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

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?

The Case of the Slow Temp Table: A Performance Tuning Problem (50 minutes)

Why would using a temp table cause a stored procedure to slow down dramatically and use massively more logical reads, compared to a permanent table?

In this course…

Watch a demo of weird temp table performance problem in SQL Server
See how to measure the problem
Try your hand at figuring out ways to speed up the temp table
Then watch me explore the issue and see a couple of possible workarounds to make that temp table faster.

Indexing for Windowing Functions (45 minutes)

Windowing functions give you great flexibility for analyzing data in SQL Server, but can be tricky to index.

Why Creating an Index Can Slow Down a Query (1 hour 30 minutes)

Sometimes performance can go badly wrong if SQL Server has an imperfect index for an query.

In this session, I show you a query whose performance regresses after you add a nonclustered index. Your challenge is to figure out why the query got slower.

Query Tuning with Hints & Optimizer Hotfixes (2 hours 15 minutes)

In this course you’ll learn the pros and cons of using hints, trace flags, and scoped database configuration to tune your queries in SQL Server. Try the course quizzes anytime: using hints, recompile, optimizer hotfixes

Demos are run against SQL Server 2016, but most of the hints and settings shown can be used against lower or higher versions of SQL Server, too!

Problem Queries in Table Partitioning (1 hour 30 minutes)

Some queries may get slower after you partition your tables: the SQL Server optimizer doesn’t always use indexes the same way after those indexes are partitioned into chunks.

Sqlchallenges

The Idempotent TSQL SQLChallenge (28 minutes)

When writing TSQL, it’s often useful to make your TSQL idempotent – a fancy word, which in the case of databases really just means: you can run the command again and again and it will make sure that you have the intended effect, without causing problems by being run more than once.

In this challenge, you’ll get multiple TSQL statements and be challenged to make them idempotent.

Who Made That Schema Change? An Event Notification SQLChallenge (27 minutes)

In this challenge, you get to take a crack at creating an Event Notification to track schema changes.

Who Made That Schema Change? A DDL Trigger SQLChallenge (28 minutes)

In this challenge, you get to take a crack at writing a DDL Trigger to track schema changes.

A Query Writing SQLChallenge: The Most Unique Names (23 minutes)

Flex your T-SQL skills: you’re challenged to write four different (but related) queries in this query writing challenge.

The queries that you are challenged to write are each related to the prior query. The final challenge is the most complex. You can start at the fourth challenge and just do that one, or work through the challenge, copy the prior query, and build upon that – choose your own adventure.

A Dynamic SQLChallenge (32 minutes)

In this challenge, you are tasked with editing a stored procedure to gather data from multiple databases. What’s the most efficient way to do it? Try your hand at the challenge, then check out three different suggested solutions.

Tune the Peak Years Procedure - SQLChallenge (52 minutes)

Ready to tune some TSQL? In this challenge, you’re given a database to restore. You’ll create two indexes and a constraint in the database– and you aren’t allowed to change those.

Your mission, if you choose to accept it, is to tune the TSQL in a stored procedure so it can make the best use of the given indexes – and still returns the same results. You’re given two sample calls to the procedure, and your goal is to make it as fast as possible.