Category: Memory grant

The series is alive! It’s been a while since I last talked about memory grants. Don’t worry, I still care about memory grants.

First, our query

To StackOverflow2010! I’ll use the Users table, and we’ll query for the locations of users with a reputation of 500.

--First, an index
CREATE NONCLUSTERED INDEX ix_Users_Reputation 
ON Users(Reputation)
INCLUDE (Location);
GO

SELECT Location FROM Users
WHERE Reputation = 500;
--76 rows returned

Here’s our execution plan and its properties.

Alright, but now we need the distinct values!

SELECT DISTINCT Location FROM Users
WHERE Reputation = 500;
--46 rows returned

Ah, there’s our memory grant! But where did it come from? We’ll find the answer on the Sort.

Taking a look at the Distinct Sort operator’s memory usage

Let’s take a closer look at that Distinct Sort. In the properties above, we see that the MaxUsedMemory is 16 KB.

There it is, this Sort used all of the 16 KB! It takes a bit of memory to get the DISTINCT values in this case because it used this sort operator to find the distinct values.

I hope this was interesting! Keep in mind that the size of the columns in your DISTINCT clause will influence the size of the memory grant. More details on that here.

Stay tuned!

So far in this series, I’ve been focusing on the Sort operator. It’s pretty easy for demos since I can just write a statement with an ORDER BY and there will be memory requested.

However, there’s another way to get a memory grant easily. That’s when you need to do some aggregation, such as MAX or MIN.

Starting with a demo in StackOverflow2010

SELECT P.OwnerUserId
	,COUNT(*) as count_star
FROM Posts as P
GROUP BY P.OwnerUserId

This query is counting Posts per OwnerUserId, which is the current owner of the Posts.

First, let’s take a look at the query’s overall memory grant.

That’s for the whole query. Luckily, this query only has one operator that could use memory, the Hash Match (Aggregate). If you want to know which operators used memory, look for the Memory Fractions property in the execution plan.

Let’s take a look at that Hash Match’s properties

There’s our memory usage! However, it wasn’t enough memory for the query. While the query was running, it had to use some TempDB space to store the rest of the data, which we see in the warnings at the bottom.

TempDB spills deserve their own blog post. For now, I’ll just show exactly how much data was spilled to TempDB:

I wanted to write this post to show that using an aggregate like a COUNT will request a memory grant. It’s also important to note that if you include larger columns inside the query, you’ll get a large memory grant.

Using an aggregate like MIN or MAX on a large column

To demo a larger column in this query, I’ll add the Body column from the Posts table. It’s defined as Nvarchar(max).

SELECT P.OwnerUserId
	,MIN(Body) as smallest_post
	,COUNT(*) as count_star
FROM Posts as P
GROUP BY P.OwnerUserId

Now, the point of this change was not to remove the TempDB spill, but I do find it interesting. Here’s the new memory grant:

Now that we included a larger column in our aggregate, our query requested a couple hundred extra MBs of memory. It only used 20 MB but requested much more, because the Body column is nvarchar(max). If you’re interested in memory grants and data types, I have a post on that.

That’s the end of this post. If you’re curious about Hash Matches, take a look at Bert Wagner’s post on Hash Match. I’ll look at some other reasons for a memory grant beyond just the aggregates in a later post too. Stay tuned!

Proceed with caution, if you’re thinking of using query hints. This post will specifically cover min_grant_percent and max_grant_percent.

My opinion on query hints is that you’re often better off tuning the query, than using a query hint. Especially if you have time to consider using a query hint, you should take enough time trying to tune the query first.

Okay, now that we’re done with the disclaimers, what does max_grant_percent do?

Max_grant_percent will set a maximum memory grant for the query. The percent is based on the maximum memory available for a query, formula here.

Without a hint, the formula is:

(Maximum SQL Server memory * 90%) * 25%

Demo time! First, creating the table from Memory Grants part 3.

CREATE TABLE BigMemoryGrant (Id INT IDENTITY(1,1), column1 NVARCHAR(max));
GO

CREATE CLUSTERED INDEX cx_BigMemoryGrant on BigMemoryGrant(Id);
GO

INSERT INTO BigMemoryGrant
SELECT 'A'
FROM sys.messages

INSERT INTO BigMemoryGrant
SELECT 'B'
FROM sys.messages

INSERT INTO BigMemoryGrant
SELECT 'C'
FROM sys.messages

And we’ll use the same query. I’ll run it again for a baseline.

SELECT * FROM BigMemoryGrant
ORDER BY column1

Add in max_grant_percent.

SELECT * FROM BigMemoryGrant
ORDER BY column1
OPTION(max_grant_percent = 50)

Perfect! So the query still desires about 4 GB of memory, but since we applied our hint, the query receives 50% of the maximum memory grant.

Then with the hint, it becomes:

(Maximum SQL Server * 90%) * 25% * (max_grant_percent value)

What about min_grant_percent?

We need a query that gets a small memory grant to test this hint. I’ll re-use the SmallMemoryGrant table from Part 4.

CREATE TABLE SmallMemoryGrant (Id INT IDENTITY(1,1), column1 NVARCHAR(max));
GO
 
CREATE CLUSTERED INDEX cx_SmallMemoryGrant on SmallMemoryGrant(Id);
GO
 
INSERT INTO SmallMemoryGrant
SELECT top 10 'A'
FROM sys.messages
 
INSERT INTO SmallMemoryGrant
SELECT top 10 'B'
FROM sys.messages
 
INSERT INTO SmallMemoryGrant
SELECT top 10 'C'
FROM sys.messages
 
--Run query once to see the memory grant size, get actual execution plan
SELECT * FROM SmallMemoryGrant
ORDER BY column1

SELECT * FROM SmallMemoryGrant
ORDER BY column1
OPTION(min_grant_percent = 50)

Interesting! I haven’t used min_grant_percent, but it has the inverse behavior of max_grant_percent. Both hints are based on the maximum query memory grant formula, but they move the memory grant in different directions.

(Maximum SQL Server memory * 90%) * 25% * (min_grant_percent value)

When are these hints useful?

As stated in the disclaimer, when your query is still getting the wrong memory grants after hours and hours of performance tuning. Once you’ve done that much legwork, and tested multiple parameters, in multiple environments, I could possible use this hint.

In my experience, this hint becomes useful when a query has very inaccurate row estimates. That can mean other performance issues are there, which is why I recommend query tuning so much.

Stay tuned! I hope you’re enjoying this memory grant series.

Part of query memory grants, part 4! This post will cover the wait type RESOURCE_SEMAPHORE briefly, but the focus is on what a semaphore is.

What the wait? Why does my system have RESOURCE_SEMAPHORE waits?

SQL Server only has so much memory to distribute to its queries. To decide who gets that memory, by default there’s two things called semaphores in SQL Server. Let’s take a look at the semaphores before we go any deeper.

SELECT 
	resource_semaphore_id,
	total_memory_kb,
	available_memory_kb,
	grantee_count,
	waiter_count,
	pool_id
FROM sys.dm_exec_query_resource_semaphores

If you want to see the documentation, click away.

So I said there’s two, but the DMV shows four. Why is that? Well, the answer is in the last column. There’s a pool_id. The pool_id comes from Resource Governor pools!

SELECT 
	pool_id,
	name
FROM sys.dm_resource_governor_resource_pools

Okay, so we’ve established that there’s two semaphores for the default Resource Governor pool, which is where queries normally run. Don’t worry about the internal resource pool at this time.

Some quick math on the available memory. My SQL Server has current max memory set to 10 GB. It looks like semaphore 0 has 7.3 GB available, and semaphore 1 has 300 MB allocated.

So how do the semaphores work? Why do they exist?

I think the best way to describe the semaphores would be to show them in a demo. Let’s do that.

I’m going to take the query from Memory Grants part 3 because it uses a lot of memory.

SELECT * FROM BigMemoryGrant
ORDER BY column1

Here’s the memory grant from that query:

Okay, we’re ready. Using SQLQueryStress , I’ll run the query eight times. Since our query’s memory grant is 1.8 GB and semaphore 1 is 300 MB, we’ll ignore this smaller semaphore. The big semaphore has 7.3 GB available, and we’re running eight queries so there won’t be enough room for all the queries.

SELECT 
	resource_semaphore_id,
	total_memory_kb,
	available_memory_kb,
	grantee_count,
	waiter_count,
	pool_id
FROM sys.dm_exec_query_resource_semaphores
WHERE pool_id = 2 
AND resource_semaphore_id = 0

So according to this DMV, there’s only 3 queries with memory grants, while the remaining 5 queries have to wait for space in this semaphore. This is where the wait type comes in. When a query is sitting as a waiter, it will display the wait type RESOURCE_SEMAPHORE.

I don’t know why there’s extra available memory in this semaphore! It looks like there’s about 1,827,576 KB available and the query will request 1,827,560 so I’d think that one more query could get a memory grant. I’d be happy to know why though, if you know please leave a comment.

Here’s a quick look at what this same issue will look like in sp_WhoIsActive:

So what are these semaphores?

Think of them as a throttling mechanism for memory, to prevent all the server’s memory from being consumed. This way, there’s a system controlling access to the large semaphore, in this case semaphore 0.

The two systems are separate, so there’s still 300 MB available for queries that don’t need a lot of memory. Let’s take a look at a demo.

First, we need a table for a small memory grant. I’ll copy the script from part 3, but reduce the data size by a lot.

CREATE TABLE SmallMemoryGrant (Id INT IDENTITY(1,1), column1 NVARCHAR(max));
GO
 
CREATE CLUSTERED INDEX cx_SmallMemoryGrant on SmallMemoryGrant(Id);
GO
 
INSERT INTO SmallMemoryGrant
SELECT top 10 'A'
FROM sys.messages
 
INSERT INTO SmallMemoryGrant
SELECT top 10 'B'
FROM sys.messages
 
INSERT INTO SmallMemoryGrant
SELECT top 10 'C'
FROM sys.messages

--Run query once to see the memory grant size, get actual execution plan
SELECT * FROM SmallMemoryGrant
ORDER BY column1

Great! Now, running our first workload of BigMemoryGrant eight times, and one execution of the SmallMemoryGrant.

Moral of the post: Query memory and how it fits into semaphores

So, while all the big memory grant queries are waiting for space in the big memory semaphore, the small memory grant query can run since there’s a separate semaphore for it.

I hope this was useful! Stay tuned for more in this memory grant series.

Update 2019-03-25:

I recently learned that my testing in this post may have been incorrect. The formula is supposed to still be applied at 25%. I’ll be working on this page over the next few days to get more accurate testing.

When you build a brand new shiny SQL Server, you want to get a lot of memory so your queries run faster, right? More memory for your data pages in the buffer pool and more memory for all the queries that keep sorting data in the database instead of the application.

However, I was just talking with a consultant friend, and she says that some servers are still running on 16 GB of memory out in Prod! At least that means this demo will be relevant, since that’s how much my demo laptop has.

There’s a supposed to be a formula for max memory per query

Of course there’s a formula for it! I pulled the formula from the link, but when I started testing I saw different behavior on SQL Server 2016+. Let’s check the resource governor settings:

select * from sys.resource_governor_external_resource_pools

The formula in the link references 25%, but that was in an older version of SQL. I believe the formula on my SQL Server is

(Maximum SQL Server memory * 90%) * 20%

What fun would it be without a demo or two? Let’s dive right in. Here’s my current SQL Server settings.

So, on my machine, we’ll expect to get a max grant of (10 GB * .90) *.20 = 1.8 GB. Here’s my table for a big memory grant.

CREATE TABLE BigMemoryGrant (Id INT IDENTITY(1,1), column1 NVARCHAR(max));
GO

CREATE CLUSTERED INDEX cx_BigMemoryGrant on BigMemoryGrant(Id);
GO

INSERT INTO BigMemoryGrant
SELECT 'A'
FROM sys.messages

INSERT INTO BigMemoryGrant
SELECT 'B'
FROM sys.messages

INSERT INTO BigMemoryGrant
SELECT 'C'
FROM sys.messages

If you’re wondering why I picked nvarchar(max), check out the other posts in this series! Anyway, here’s our query to request a lot of memory.

SELECT * FROM BigMemoryGrant
ORDER BY column1

So we predicted 1.8 GB based on our formula, and we got a max grant just over 1.8 GB, that’s close enough for me!

I hope this was useful. If you add memory to your SQL Server, expect to see higher memory grants. Stay tuned!

Why the heck did we make all our columns varchar? That’s a hypothetical question, please.

But now we have to pay for our decisions. Consider a query that has to do some sorting. Let’s make a table that needs some sorting.

The varchar sort

CREATE TABLE Names_Varchar
    (Id INT IDENTITY(1,1),
    Names VARCHAR(MAX) NOT NULL,
    SortValue int);

CREATE CLUSTERED INDEX cx_Names_Varchar on Names_Varchar(Id);

Add two scoops of data from StackOverflow 2010, and bake at 350 degrees for 30 minutes.

INSERT INTO Names_Varchar (Names)
SELECT DisplayName FROM StackOverflow2010.dbo.Users
--(299611 row(s) affected)

Sprinkle on some SortValues for flavor.

UPDATE Names_Varchar
SET SortValue = Id/100;

I’m doing this so I have some variations in SortValues per DisplayName. The names aren’t in any particular order, so this gives an element of randomness. Now we’re finally ready to sort and serve our query.

SELECT Names,SortValue FROM Names_Varchar
ORDER BY SortValue

Here’s your execution plan, as you requested.

That’s a lot of desired memory, 1,493,120 KB aka 1.4 GB, but there was only
25 MB used in the sort. So why was SQL Server so far off the right estimate? It’s the data types we picked.

Blaming the data types

What’s the biggest value in the Names column?

SELECT MAX(LEN(Names)) FROM Names_Varchar
--36

Let’s right size this and try again.

Names with varchar(100)

CREATE TABLE Names_Varchar_100
    (Id INT IDENTITY(1,1),
    Names varchar(100) NOT NULL,
    SortValue int);

CREATE CLUSTERED INDEX cx_Names_Varchar_100 on Names_Varchar_100(Id);

Load in the same data, etc.

INSERT INTO Names_Varchar_100(Names,SortValue)
SELECT Names, SortValue
FROM Names_Varchar

And send that sort off again, let’s see how much memory was granted.

Desired and Granted Memory are at 38 MB, and Used Memory is at 18 MB. That’s much closer.

Moral of the post

I don’t recommend blindly changing your varchar max values. I wanted to point out that there’s better ways to pick data types. The difference between max and 100 in this case was fairly dramatic, over 1 GB of memory extra.

Just make the right decision for your data types. Thanks for reading, stay tuned.