Strings Were A Mistake
While working with a client recently, we found that someone, at some point in time, probably during the original migration from Access, had chosen CHAR columns rather than VARCHAR columns.
Okay, fine. How bad could it be?
Bad enough that… A whole bunch of columns that only had a single character in them were stored in CHAR(1000) columns.
Which was wildly inflating memory grants, and causing all sorts of nasty issues.
Table create script it at the end of the post!
Del Granto
To show how you much different grants are for char vs varchar, I need to give you a little background on sort memory grant estimates.
When the optimizer estimates how much memory it’ll need, the calculation is based on the number and width of the rows that will have to get sorted.
For variable length string columns, it estimates that half the number of bytes will be filled. So if you have a varchar(100) column it’ll estimate 50 bytes will be filled for every row, and for an nvarchar(100) column it’ll estimate that 100 bytes will be filled for every row, because unicode characters are stored as double-byte to account for accent marks, etc.
So, yes, identically sized varchar and nvarchar columns will have different memory grant requirements.
And yes, identically sized (n)char and (n)varchar columns will also have different memory grant requirements.
Granto Loco
Let’s take these two queries, and let memory grant feedback right-size the grants for these two queries:
DECLARE
@c char(1000);
SELECT
@c = m.char_col
FROM dbo.murmury AS m
ORDER BY m.some_date DESC;
DECLARE
@v varchar(1000);
SELECT
@v = m.varchar_col
FROM dbo.murmury AS m
ORDER BY m.some_date DESC;
Here are the fully adjusted and stabilized grants:
Around 9GB vs 441MB. All because those CHAR columns are padded out with 999 empty characters.
So hey, if you need a reason to review schema for char column sanity, this just might be it.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Scripto
DROP TABLE IF EXISTS dbo.murmury;
SELECT
ISNULL
(
x.n,
0
) AS id,
DATEADD
(
MINUTE,
x.n,
'20210101'
) AS some_date,
CONVERT
(
char(1000),
LEFT(x.text, 1)
) AS char_col,
CONVERT
(
varchar(1000),
LEFT(x.text, 1)
) AS varchar_col
INTO dbo.murmury
FROM
(
SELECT TOP (1000 * 5000)
ROW_NUMBER() OVER
(
ORDER BY
1/0
) AS n,
m.text
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) AS x;
ALTER TABLE dbo.murmury
ADD CONSTRAINT pk_mr PRIMARY KEY CLUSTERED(id);
