Query memory grants part 2: Varchars and sorting

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.