Great Question, You
During my (sold out, baby!) Madison precon, one attendee asked a great question while we were talking about memory grants.
Turns out, if you use the SQL Variant datatype, the memory grants function a lot like they do for any long string type.
From the documentation, which hopefully won’t move or get deleted:
sql_variant can have a maximum length of 8016 bytes. This includes both the base type information and the base type value. The maximum length of the actual base type value is 8,000 bytes.
Since the optimizer needs to plan for your
laziness indecisiveness lack of respect for human life inexperience, you can end up getting some rather enormous memory grants, regardless of the type of data you store in variant columns.
Ol’ Dirty Demo
Here’s a table with a limited set of columns from the Users table.
CREATE TABLE dbo.UserVariant ( Id SQL_VARIANT, CreationDate SQL_VARIANT, DisplayName SQL_VARIANT, Orderer INT IDENTITY ); INSERT dbo.UserVariant WITH(TABLOCKX) ( Id, CreationDate, DisplayName ) SELECT u.Id, u.CreationDate, u.DisplayName FROM dbo.Users AS u
In all, about 2.4 million rows end up in there. In the real table, the Id column is an integer, the CreationDate column is a DATETIME, and the DisplayName column is an NVARCHAR 40.
Sadly, no matter which column we select, the memory grant is the same:
SELECT TOP (101) uv.Id FROM dbo.UserVariant AS uv ORDER BY uv.Orderer; SELECT TOP (101) uv.CreationDate FROM dbo.UserVariant AS uv ORDER BY uv.Orderer; SELECT TOP (101) uv.DisplayName FROM dbo.UserVariant AS uv ORDER BY uv.Orderer; SELECT TOP (101) uv.Id, uv.CreationDate, uv.DisplayName FROM dbo.UserVariant AS uv ORDER BY uv.Orderer;
It’s also the maximum memory grant my laptop will allow: about 9.6GB.
As if there aren’t enough reasons to avoid sql_variant, here’s another one.
Thanks for reading.