0 – Prerequisites Before the Class
Hi, Brent here! I’m really excited to welcome you to class.
Get your brain ready by taking the classes in order.
I recommend that you take the classes in this order:
- How to Think Like the Engine
- How I Use the First Responder Kit
- Fundamentals of Index Tuning
- Optional: Fundamentals of Columnstore (you can skip this one, though – it’s only for people who want to use columnstore indexes. The rest of the classes below aren’t dependent on this one. This one also has its own separate prerequisites: it requires the much larger Mastering class VM.)
- Fundamentals of Query Tuning
- Fundamentals of Parameter Sniffing
- Fundamentals of TempDB
Get your computer ready too.
All of the Fundamentals courses except Fundamentals of Columnstore have the same prerequisites, so if you’ve set up for any of ’em, you’re ready to go.
Set yourself up a SQL Server:
- SQL Server 2017 or newer, either Developer Edition or Evaluation Edition. Download pages are linked from SQLServerUpdates.com. Express Edition, Azure SQL DB, and Amazon RDS won’t work, unfortunately.
- Use the default collation during install, SQL_Latin1_General_CP1_CI_AS (don’t get fancy with binary collations)
- Apply the latest Cumulative Update
- Install the most recent SQL Server Management Studio
To follow along with the demos, download the 50GB Stack Overflow 2013 database. I’ll be using the medium-sized 50GB StackOverflow2013 database, and it’s vital that you use the same one. Query tuning and parameter sniffing is all about getting different behavior based on your query’s parameters, so I need you to have the exact same data distribution that I’ll be working with onscreen.
Install SQLQueryStress for load testing.
SQLQueryStress is an easy, free, open source load testing utility. Download the latest version (zip), and extract it to a folder. You don’t have to run a setup or anything – it’s just a single .exe file that you can run whenever you want to do a load test.
Then, download my load test files and extract those to the same folder.
Attending live? Join the Slack to ask questions.
If you’re attending the class live, then to ask questions and talk to the other attendees, get an instant invite to Slack here, then join the #BrentOzarUnlimited channel. This way attendees can help each other with their questions during the live labs.
- 1 – How the Version Store Affects TempDB
- 2 – How Temp Tables Affect TempDB
- 3 – How Table Variables Affect TempDB
- 4 – Temp Tables and Table Variables at Scale: PFS/GAM/SGAM Contention
- 5 – Memory-Optimized Table Variables
- 6 – How Execution Plans Use TempDB
- 7 – Other TempDB Consumers: Cursors, Index Builds, AG Stats
- 8 – How to Provision TempDB on a New Server
- 9 – How to Monitor TempDB
2 Comments. Leave new
Dude, love what you’re doing for us “mere mortals”. Question: So I listen to you speak on different aspects of SQL Server, and I just want to be certain I’m not misinterpreting what you say about the relative size of in instance when it comes to performance. For example, SQL has to provide for StackOverflow, as well as some personal use and everything in between. So, my question is about your comment on batches/sec. Can I assume if I have a couple of instances with sub 200, SQL can handle without issue. and only when I start getting above 800 or so, need to monitor more closely. I ask, because we are about to open up a portal for COVID registration. Trying to determine when, or if, I should be concerned. Thanks for the help.
Thanks, glad you like it! For the answer on that one, watch the first module of the Mastering Server Tuning class where I talk about how to measure your SQL Server.