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 Designing Indexes for the WHERE Clause
- 2 Lab: Indexing for WHERE
- 3 Designing Indexes for ORDER BY
- 4 Lab: Indexing for WHERE + ORDER BY
- 5 Designing Indexes for JOINs
- 6 Lab – Indexing for Joins
- 7 SQL Server’s Built-In Index Recommendations
- 8 Recap and Next Steps
- Bonus Lab – Getting Index Recommendations and Tuning Them
10 Comments. Leave new
Files are in the process of downloading but I probably will not be able to use the database files since I do not have admin rights but I do have databases to use.
Gotcha. You’ll really need to use these databases, unfortunately. See you in class!
I can’t download the ZIP file of the slide
This XML file does not appear to have any style information associated with it. The document tree is shown below.
AccessDeniedRequest has expired
300
2020-12-02T21:26:43Z
2020-12-02T21:43:03Z
That’s because you waited too long. Read the instructions really carefully: “That link is good for 5 minutes only – after that, you’ll need to refresh the page to get a fresh link.”
thank you
Hi Brent – I’m setting up a VM to join in your European training sessions – any preferred version of MSSQL required and is there any collation setting I should use or should I stick to the default install settings.
Cheers
Martin
Martin – sure, for this class, check out this section: “Attach it to any supported version of SQL Server, can be Standard, Enterprise, or Developer.” Microsoft currently supports SQL Server 2012 & newer. I would stick to the default collation settings.
Here’s an updated link for SQL Query Stress, the link above isn’t working for me… https://github.com/ErikEJ/SqlQueryStress/releases
Hello, link to SQL Query Stress on this page – https://ci.appveyor.com/api/projects/ErikEJ/SqlQueryStress/artifacts/SqlQueryStress.zip?branch=master
is not working for me, either
shows {“message”:”Artifact not found or access denied.”}
Sorry about that! Looks like they’re having an outage – I put a version up there temporarily for you.