Memory grants 7: DISTINCT (Distinct Sort)

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!