Month: March 2019

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!