Courses

Defuse the Deadlock SQLChallenge (23 minutes)

on February 17, 2018

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)

on February 1, 2018

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.

How Index Keys and Includes Work (1 hour)

on January 12, 2018

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?

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

on December 18, 2017

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.

Repeatable Read and Serializable Isolation Levels (45 minutes)

on December 15, 2017

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.

Indexing for Windowing Functions (45 minutes)

on December 8, 2017

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)

on July 16, 2017

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