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!

Scripts

https://github.com/LitKnd/SQLWorkbooks/tree/main/query_tuning_hints_optimizer_hotfixes

Each video has closed captions in English. A written transcript is included with each lesson.

Lessons

Why do we need hints and optimizer hotfixes? (3 minutes)

Why hints are not suggestions, and a tour of our slow query (12 minutes)

Hinting cardinality estimation level and setting row goals (15 minutes)

Join hints, temp tables, and table variables (13 minutes)

Hints lead to a query rewrite, and hinting away a nonclustered columnstore index (8 minutes)

Getting creative with TSQL rewrites (15 minutes)

Frequently used hints (10 minutes)

Quiz: Using Hints in Query Tuning

What RECOMPILE means (3 minutes)

A super simple load test of recompile hints (15 minutes)

Recompile bugs and best practices (4 minutes)

Quiz: Recompile behavior and pros and cons in SQL Server

Bumping execution plans out of cache (8 minutes)

Query optimizer hotfixes and trace flag 4199 (7 minutes)

Reproducing a bug and showing query optimizer hotfixes at work (11 minutes)

When to use query optimizer hotfixes and TF 4199 (4 minutes)

Quiz on query optimizer hotfixes

How to use the interview questions (1 minute)

Interview Question #1 (2 minutes)

Interview Question #2 (1 minute)

Interview Question #3 (2 minutes)

Copyright (c) 2024, Catalyze SQL, LLC; all rights reserved. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.