Category: Presentation

My presentation so far is really rough. I need to improve a lot before SQL Saturday Chicago! For now, the goals are to practice the presentation and write the demos.

Practice, practice, practice

I’ve started to talk through what I have done with the slides. This way, I’m used to talking about the topics that will come up on the slides. I’m also practicing what I can remember during my commute.

Writing a demo that needs a memory grant

I need to write at least one really good demo. I hope I can write a few more demos, but I needed a solid yet simple demo that could be used multiple times.

I also wanted to highlight a possible real world scenario. This is what I came up with:

SELECT TOP 500 Title
FROM Posts AS p
WHERE PostTypeId = 1
ORDER BY CreationDate DESC

This is a query on the Stack Overflow database, on the Posts table. PostTypeId = 1 is Posts, and I’m sorting by CreationDate to find the most recent Posts. To me, this is a realistic scenario since I often find applications that want to show users the most recent activity.

Assuming there’s either no indexes here or inadequate indexes, this sort will request a memory grant.

More on that tomorrow! I’ll show the execution plan and how I’ll use this demo tomorrow. Stay tuned.

This is a series I’m writing as I prepare for SQL Saturday Chicago 2019. My session is called Performance tuning with SQL Server memory grants.

In my last post, I laid out three ideas for the flow

Now it’s time to pick. I’ll move the ideas from post 1 into slides, then I’ll write the final wrap-up slide. Each idea will get its own slide, then I’ll work on developing some content on the slides that come to mind.

The idea I liked the most was idea #3. I’ve decided to introduce the idea of memory grants by talking about their most common operator, the Sort.

Then, I’ll describe how to monitor the memory grants in an execution plan, demonstrate the issues when memory grants conflict, and show ways to performance tune those pains away.

Let’s take a look at what this looks like:

Next steps

So I’ve got the general flow laid out here in the outline but I don’t have clear thoughts on what I want to say for each section, and I don’t have any demos yet.

The next step for me is to flesh out the ideas with some thoughts on each slide, then identify which concepts are best displayed in demos, creating those demos or borrowing them from previous blog posts.

I might add more slides between completing these steps, it just depends on what the presentation needs.

Stay tuned!

This series is about my process for writing my upcoming presentation at SQL Saturday Chicago #825.

Today’s goals

Today I’m going to post some of the ideas for the flow of the presentation. These will be general ideas, based on the abstract.

By the time I’m done with this presentation, these could be completely different.

Idea #1: Start with the pain that memory grants can cause RESOURCE_SEMAPHORE

It can be terrifying to feel like you don’t know what to do, and that fear only gets worse when you’re facing an unknown performance issue.

When your system is facing too many big memory grants, a number of troubleshooting scripts won’t work. I feel like this could be catchy and interesting, but it’s risky. If I don’t have the right demo, and if I don’t pull it off right, I could miss the point.

Idea #2: Start in familiar territory, with a normal execution plan and develop from there

In my prerequisites, I mentioned a basic knowledge of execution plans. That can mean a lot of things to different people, but as long as they are familiar with a few operators, I could work my way from there into requested and grant query memory.

This has a lot of potential because it introduces my subject and then I can work into situations where you’d want to tune a query to reduce its memory grant.

Idea #3: Pick a few execution plan operators that request memory

I don’t want to spend the whole session discussing execution plan operators, but I think I could start with the Sort operator. I think people are familiar with explicit sorting, using ORDER BY.

My fear here is that I want to focus on the impact on the whole SQL Server, and spending too much time on operators could burn a lot of session time.

Tomorrow and the weekend

I’m going to leave these ideas here and let them digest overnight. I’m planning to do some research and testing this weekend, which should help me pick (or add more ideas).

What do you think? Am I missing something? Let me know!

Stay tuned!

For this session, I’m relying on a mixture of previous blog posts and new material/demos that I’ll have to write. I’ve written three full presentations, and I usually follow the same pattern.

My method for developing a presentation

First, I write down all my ideas. Then I sort through them to tell a coherent story, and find what demos I need to write. Finally, I polish and polish, to get to a point where I’m happy with the presentation. This is not the final copy.

The final copy is created after I practice the presentation and demos until they’re second-nature. That is the most time-consuming part by far, but it gives me confidence when presenting.

My current list of ideas

The session title is Performance Tuning with SQL Server Memory Grants. This has a big influence on what ideas I want to show.

Here’s the list, in no particular order:

• Include the information on the max size of a memory grant from this blog post
• Demonstrate problems with big memory grants, specifically RESOURCE_SEMAPHORE waits
• What does a memory grant do
• Why is a memory grant requested
• How do data types and sizes affect the memory grant
• Consider including information on the “excessive memory grant” warning in execution plans
• PerfMon counter: Memory Grants Pending
• Resource governor
• Currently running queries
• Small/large semaphores
• Memory grant hints
• What operators in execution plans use memory grants
• Minimum size of a memory grant

Next step

The next step is to work on connecting the ideas to form a story through the presentation. It’s likely that I’ll have to trim that list down, and there’s probably ideas I’ve forgotten.

Please feel free to give feedback as I work on this presentation. Stay tuned!

So, I’m presenting a session at SQL Saturday Chicago on March 23, 2019. This is a new session, called Performance tuning with SQL Server memory grants.

Blogging about writing a presentation

I’m going to write a couple blog posts as I write this session and practice for it. I hope this is interesting to read.

Tomorrow, I’ll brain dump all my ideas into a blog post.

Here’s the session abstract

When you’re performance tuning queries with memory grants, you want to know where, how and why the memory is granted and used in your query.

In this session, we’ll talk about analyzing requested and granted memory inside execution plans, the plan cache, and in currently running queries.

We’ll also talk about troubleshooting your system when many queries have memory grants.

Prerequisites: General knowledge of execution plans and query writing

Stay tuned!

It’s been a while since I’ve posted a blog post! In the meantime, I gave a new presentation at GroupBy.org. Check it out here  and find the slides/demos here. The presentation is up on YouTube and the transcript is available on the link, so it’s pretty easy to access. GroupBy conference was the biggest event I’ve ever been a part of, and it was such an incredible experience.

Preparing for The Perfect Index

I spent a long time preparing to give this presentation. I had three months to prepare but I didn’t start preparing until about four weeks out. This was a mistake. I knew what I wanted to say, but I procrastinated so much that I had to start rushing about a week before the conference. That’s when I really kicked it into gear and I quickly found that mornings were the best times to work. I was on-call for work so by the time it was the evening, my brain was fairly worn out. I’d wake up at 5 AM, run to my work computer and write the demos/slides until I had to leave.

Practice #1

The first time I gave this session was Wednesday, September 5th. I had volunteered to give the presentation for my team, to test what worked and what didn’t. I’m very grateful for their patience for this run, since it didn’t go smoothly. ZoomIt wasn’t zooming correctly on the second monitor and the presentation only lasted about 35 minutes. This was a big warning sign to me, that I needed to beef up the material to prepare for Friday.

Final prep

I spent the last couple days in a frenzy to complete the presentation. The hardest part of this was practicing the demos. I don’t mind practicing my presentation, but it can be pretty boring to run the same queries over and over and focusing on the same parts. When I practice talking through the presentation I don’t have a script, so I’ll change words between each practice.

Friday – the big day

It was finally Friday morning. I didn’t log into GroupBy or start up any distractions, I wanted to remain super focused on the goal. When I loaded into the conference and Brent started talking to me, I knew things were getting real. The first five minutes were scary. I have only done one remote presentation before, and it’s definitely a different type of challenge. Once I got into the flow of the presentation, I felt much more natural and the presentation flew by. Before I knew it, Daniel Hutmacher and I were talking about questions and the feedback in the SQL Community Slack Channel.

Post-session

I mentioned this during the presentation; the feedback I received from my session was incredible. I saw so many familiar names saying good things and I was riding the adrenaline from this presentation for another couple hours after my session. I’m incredibly grateful to hear the feedback. I want to thank several people who made this session and conference possibly.

Thank you

First, thank you Daniel for spending the time with me during my session, relaying questions and talking to me. It made it feel like a conversation. Second, thank you Brent Ozar for hosting the entire GroupBy event, for giving me feedback on this session abstract, and for taking the time to give me feedback after my session. Third, thank you to everyone who contributed to this session. My fellow DBA co-workers, Brian and Gerard, for sharing their thoughts and helping me pick the right terms. Finally, thank you to everyone who attended my session, either while it was running or afterwards. All of you have helped me give this session and it means the world to me.