SQL Server memory benchmarking - how to fill up 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