Month: January 2019

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.