SQL Server memory benchmarking - how to fill up memory? Recently I was asked by my VMWare colleague how to cause a (non-production and bare install) SQL Server to use up the memory of a machine.

The solution I found in this forum post does not cause the SQL Server process to use up memory but instead SSMS due to it's output. 

As SSMS is only 32 bit, it crashes after about 4 GB, so this is not useful for a  server with 600 GB memory...
The solution I came up with is a slight extension of the forum post I mentioned before (first make sure that your max server memory is set appropriately!):

DECLARE @sometable table (id uniqueidentifier)

insert into @sometable
SELECT NEWID()
FROM sys.columns A
CROSS JOIN sys.columns B
CROSS JOIN sys.columns C


Indeed you can see that SQL server starts to consume memory when running this query, and of course this does not cause SSMS to fail as there is only the usual "rows affected" output