This year, I’m working with you to get you to the PASS Summit stage. Last week, we talked about your first homework: a list of a few pains you’ve relieved over the last year.
This week, you need to write the recap slide for each of those pains. Think about the end of the session: when the attendee walks out, what are the most crucial takeaways you want on their mind? What do you want them saying, “Thank God I went to that session, because now I know that ____. When I get back to the office, I can’t wait to ____.”
Let’s tackle my pain point topics.
How often should you take backups and run CHECKDB
The recap slide for this one should cover:
- Understand what scenarios would cause you to do what kinds of restores
- Get the RPO/RTO HA/DR worksheet
- Fill out the current state columns with sp_BlitzBackups
- Use my management tips to get real RPO/RTO goals from the stakeholders
- When the jobs are too slow, use my time-saving tricks for backups and CHECKDB
- Trend database size & backup times to understand when you need to recalculate
I think those are the most important things I can teach ’em in 75 minutes. That list of bullet points tells me a lot: the session level, what they already need to know coming in, what kinds of attendees should attend, which kind shouldn’t attend, and more.
For time budgeting, I’m guessing I wanna spend 10-20 minutes on each of those.
What to do when SQL Server is unusually slow
I’ve done this one a lot, and in the past, the recap slide has been a list of tools. For the new session, I want it to be a T-SQL file with the commands filled in – and they’re so short that it’s easy to just use a screenshot of the commands:
When your SQL Server is unusually slow, step through these in order:
- sp_WhoIsActive @get_locks = 1, @get_plans = 1
- sp_BlitzFirst @ExpertMode = 1
- sp_BlitzCache @ExpertMode = 1, @SortOrder = ‘all’
This looks like a short list, but the way I teach this is to use a series of server emergencies to troubleshoot live, so it ends up taking ~20-25 minutes per scenario.
Analyzing parallelism
I’ve been thinking a lot about this one over the last week, and it feels like I need to tell the story in terms of time. Your server starts up, certain things are set in place that affect how queries are handled. Then someone logs in, and they inherit settings from their RG workload group. Then they use a database, and they inherit that database’s MAXDOP. As they start a query, their query contents change it yet again.
So the recap slide might look like this:
- Server startup: Cost Threshold, MAXDOP, core count
- Login: Resource Governor
- Database picked: MAXDOP
- Query starts: query hints, parallelism inhibitors
- Single query in progress & aftermath: live plan, actual (completed) plan, parallelism operators, thread stats, DMVs that show DOP, XE
- Multiple execution aftermath: parallelism operators in estimated plans, DMVs that show DOP
Looking at that list of 6 bullet points, holy cow – it’s not going to be possible to explain how all those things work in 75 minutes. That means I’ve either gotta reduce the scope, or I’ve gotta set the stage as, “I’m not going to teach you how all these things work, but I’m going to give you a mental toolkit you can use to investigate parallelism challenges.”
It’s kinda funny – when I wrote my list of 4 topics, this was the one I liked the least because I thought I’d have the toughest time making it interesting. Now, I like it a lot.
Demystifying cardinality estimates
This one’s more about a thought process – getting people to read an execution plan, correlate the plan operators with the T-SQL they passed in, and then think about how easy (or hard) it is for SQL Server to guess how many rows will come out of each operator. So for this one, I’m thinking the recap slide is better as a series of questions:
Look at the driver operator (top right) first, and:
- Can SQL Server quickly understand your T-SQL?
- Are you asking for something that’s already stored on disk?
- Are there statistics about the data you’re asking for?
- Is the data stored in a way that makes subsequent operations easier?
- Now move through the plan’s operators, repeating those questions
The more YES’s you have, the better SQL Server’s estimates will be.
There we go! Now you’ve seen me pick pain points, and sketch out a possible recap slide for each one. Next up: Read on in Week 3: The Abstract’s Technical Details
