You need to speed up a database server that you don’t fully understand – but that’s about to change in four days of learning and fun. Join Brent Ozar in person to learn how to make your SQL Server go faster.
Now with Instant Replay Access – as soon as you buy the course, you can start watching a recorded version! Prepare for the class by digging into your favorite modules, and then after the class, brush up on stuff you missed or revisit your favorite topics. It’s the best of both worlds: live training with Brent, plus reminders. Access starts when you buy, and lasts for one year.
During the in-person training classes, we provide you with breakfast, mid-morning breaks, lunch, and afternoon snacks. Dinner is on you, but during the training, we’ll organize groups so you can dine with fellow folks who share the same interests.
For the online class, we host it in GoToWebinar, live with Brent Ozar on webcam, with a 75-minute lunch break. Audio can come through either your computer audio, or by dialing into a US phone number – headset recommended either way. For more information, check out the Training Logistics PDF for our online classes.
- October 24-27 Chicago at Catalyst Ranch – 656 W Randolph St, Chicago IL, 60661. These hotels are nearby, and the Crowne Plaza is an easy 3-block walk. We don’t do a meeting block for our Chicago events because so many folks like to use AirBNB.
Schedule subject to change in the event of natural disasters, food poisoning, exploding demos:
Day 1 – Internals and Indexes
Performance Skills Quiz – Are you a production DBA, development DBA, or database developer? How much experience do you really have? What areas of the engine do you need to focus on to get the most out of this class?
How to Think Like the Engine – When you pass in a query, how does SQL Server build the results? We’ll role play: Brent Ozar will be an end user sending in queries, and you’ll be the SQL Server engine. Using simple spreadsheets as your tables, you’ll learn how SQL Server builds execution plans, uses indexes, performs joins, and considers statistics. This session is for DBAs and developers who are comfortable writing queries, but not so comfortable when it comes to explaining nonclustered indexes, lookups, sargability, fill factor, and fragmentation.
Physical Data Modeling – Heaps and Clustered Indexes – How should you structure your tables? You’ll learn as we cover how heaps cause performance problems, see what’s really inside an index, explain the logic behind IDENTITY and GUID fields as clustering keys, and show when to break the best practices.
Performance Tuning Nonclustered Indexes – You’re not starting from scratch – you’re dealing with a database that already exists, and you have to make it go faster. You need to analyze the current indexes, remove the ones that aren’t helping, and add the right ones. We’ll show you how.
How In-Memory OLTP (Hekaton) is Different – In-memory OLTP holds the promise of avoiding indexes altogether. It’s a super-fast way to keep tables in memory and reduce latch contention. Just like any new feature, though, there are significant risks. You’ll learn what it takes to go live with In-Memory OLTP.
Homework: Index Tuning Workshop – You’ll get a query for the StackOverflow.database, and then try to design the absolute perfect index to make it perform. You’ll see how your index design compares to the SQL Server DMV recommendation, and Brent’s guesstimate.
Day 2 – Tuning Queries and Execution Plans
How SQL Server Builds Execution Plans – Before we start tuning queries, you’ll learn the steps SQL Server takes to build its execution plans, the terminology for parts of a plan, and see what’s missing from execution plans.
Identify and Fix Parameter Sniffing – Got a query that’s sometimes fast, and sometimes slow, and there doesn’t seem to be anything changing? Learn how parameter sniffing impacts performance, how to recognize the symptoms, and improve your code to reduce parameter sniffing problems.
Watch Brent Tune Queries – Watch as we step through a real query, measure its performance, and iterate over improvements.
Recognizing SQL Problems in Plans – Many performance issues boil down to just a handful of bad T-SQL patterns and engine decisions. In newer versions of SQL Server, you can spot missing indexes, implicit conversions, TempDB spills, and missing join predicates in execution plans. You’ll learn to recognize their symptoms and fix them.
Dysfunctional Functions – Good developers are taught to reuse their code by putting it into functions. Unfortunately, SQL Server’s scalar functions and multi-statement table-valued functions have horrific performance downsides. You’ll learn how to tell whether functions are killing your queries, and how to work around them.
Designing Indexes for Queries – Now that you’ve seen how we handle index design and building plans, let’s look at the index DMVs. You’ll learn why we say that the missing index DMVs and index usage DMVs are deceiving little liars. Then, you’ll be given a set of indexes, DMV contents, and queries, and you’ll design your own indexes. We’ll finish by comparing your answers to Brent’s to see how you did.
Artisanal Indexes: Filters, Views, Computed Columns – When you really need to push performance, hand-crafted special index types can give you an amazing boost. Learn the catches behind filtered indexes, indexed views, and computed columns.
Homework: Query Tuning Workshop – Let’s get interactive! We’ll break into teams, and each team will be responsible for improving the performance of a query. We’ll then reconvene as a group, talk through your solutions, and cover our own too.
Day 3 – Measuring Your SQL Server’s Bottlenecks
How to Measure Your SQL Server – Performance tuners need to know 3 metrics about their SQL Server: how fast it’s going, how hard it’s working, and how big it is. We’ll explain where to get those numbers, and what normal ranges are.
LCK – Find and Fight Blocking – When you’re facing lock waits and blocked queries, it’s tempting to just slather NOLOCK all over the queries. Thing is, this won’t necessarily fix it, and there are much better ways to track, diagnose, and cure blocking issues. You’ll learn them here.
PAGEIOLATCH – Slow Data File Storage – One of the most common SQL Server wait types is when SQL Server has to wait to read data from disk. We’ll show a live example, then step through a prioritized list of how to reduce this wait type.
CXPACKET – Parallelism Gone Wild – The parallelism terms CXPACKET and MAXDOP often stump database professionals – what do they mean, and what are we supposed to do about them? You will learn how parallelism works, how it goes wrong, what basic rules to start with, and how to know when to deviate from those guidelines. You’ll understand how much CXPACKET is too much, and why your queries aren’t going parallel when they should.
Homework – Using the tools we discussed today, connect back to one of your servers at work. Get the top wait types, and the list of queries causing these waits.
Day 4 – Hardware and Architecture
What If It’s Hardware – Some wait types seem to indicate that you’ve got a hardware problem – but they’re misleading. We’ll show examples of SOS_SCHEDULER_YIELD, WRITELOG, THREADPOOL, RESOURCE_SEMAPHORE, and ASYNC_NETWORK_IO, and cover how to fix them.
Using AlwaysOn AGs for Performance – AlwaysOn Availability Groups let you scale out a single database to multiple SQL Servers. You’ll learn whether it makes sense for your workloads, plus see what preparations you need to make in order for AGs to succeed.
Scaling Out of SQL Server – When your workload exceeds what you can achieve with a single SQL Server, you can scale your load out across multiple SQL Servers – or even move it outside of SQL Server altogether. We’ll cover common scale-out issues and the solutions that make the most sense.
VLDBs: The Terabytasaurus – Very Large Databases are generally defined as over 1TB. The best way to deal with a VLDB is to avoid it altogether, and we’ll cover how to do just that. Once you get stuck with over 1TB of data, then you’ll also learn how to deal with backup, recovery, filegroups, SAN snapshots, and DBCC CHECKDB.
How to Triage SQL Server Emergencies – When the phone rings, you need a repeatable process to discern the symptoms, root cause, what will happen if you don’t take action, and based on that, what actions you should take. You’ll see Brent step through several emergency scenarios live, learn what tools he uses, and how he uses them.
Online Class Logistics
We work together over GoToWebinar, the same platform we use for our weekly Office Hours webcast. The audio will come through computer audio, or phone (US dial-in number), whichever you prefer to use. You can ask questions aloud, or via typed chat. The online class is live with Brent on webcam teaching. We break for about 15 minutes per hour for bio breaks, and a one-hour break for lunch.