SQL query to fill RAM?

Question

Post reply

SQL query to fill RAM?

jpSQLDude

SSCrazy

Points: 2571
More actions
April 28, 2014 at 10:44 pm

#292839

I work for a federal government client who restricts us to using virtual machines for dev work. Now the VM team has gotten MUCH more aggressive with "right-sizing" (which always means down-sizing) our virtual machines. I have a Dev server with 8 GB RAM and I need that much when developing, but I don't develop all the time. So I want it to look like it needs all that RAM before they bust me down to 1 GB, which they will do.
How can I get SQL Server to use up all the memory I've allocated to it? Is there a relatively easy query to do this?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Set MIN and MAX memory to the desired number

😎

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'min server memory (MB)', N'4096000'

GO

EXEC sys.sp_configure N'max server memory (MB)', N'4096000'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

GO

jpSQLDude SSCrazy Points: 2571 More actions · Answer 2

Thanks Eirikur, but I am looking to fill up the memory that is already allocated to this SQL Instance. Say you adjust the memory to 4GB as you've show, what query or queries would you run to fill up all that memory, like you were doing performance testing, etc.?

Roman Vinnik SSC Enthusiast Points: 191 More actions · Answer 3

Just a shot in the dark...

What if you run a bunch of inserts into a dummy table (you can work out how may) with a BEGIN TRANSACTION but no commit. That should fill up your log.

When you start working, execute the commit (or a rollback), when you stop working, re-run the inserts?

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 4

If all you're looking for is to gobble up some memory...

SELECT NEWID()

FROM sys.columns A

CROSS JOIN sys.columns B

CROSS JOIN sys.columns C

That should chomp up a good amount. Just ran it on a spare server that's using 7.15GB of RAM currently, and after a minute, I'd used about 300MB more RAM. If this doesn't eat enough, you can just add more CROSS JOINs. Keep an eye on it, though! This will produce a rather massive number of rows in its current form, and it's bound to cause a bit of operational trouble if anything else is running at the same time. Since it's a dev server, though, I'd guess planning around it is simple enough:-).

- 😀

Eric M Russell SSC Guru Points: 125522 More actions · Answer 5

Eirikur Eiriksson (4/29/2014)
Set MIN and MAX memory to the desired number
😎
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'min server memory (MB)', N'4096000'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'4096000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

If you assign minimum 4 GB to SQL Server, then at the OS level at least that much RAM will appear reserved. Find out from the VM administration team what method they're using to determine how much RAM is being used by each instance. I doubt the server admins are logging into each SQL Server instance and querying how much of the data buffer pool is actually used.

Table scanning and temp tables will hog memory, so if you think it's "helpful", you can do something like the following at the end of each day.

select * into #T from BigTable;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

jpSQLDude SSCrazy Points: 2571 More actions · Answer 6

Thanks Eric but when I set the Min and Max memory, it actually does not change anything at all. I am looking in Task Manager, at the sqlservr.exe process. The value in the column Memory (Private Working Set) does not change.

jpSQLDude SSCrazy Points: 2571 More actions · Answer 7

hisakimatama (4/29/2014)
If all you're looking for is to gobble up some memory...
SELECT NEWID()
FROM sys.columns A
CROSS JOIN sys.columns B
CROSS JOIN sys.columns C
That should chomp up a good amount. Just ran it on a spare server that's using 7.15GB of RAM currently, and after a minute, I'd used about 300MB more RAM. If this doesn't eat enough, you can just add more CROSS JOINs. Keep an eye on it, though! This will produce a rather massive number of rows in its current form, and it's bound to cause a bit of operational trouble if anything else is running at the same time. Since it's a dev server, though, I'd guess planning around it is simple enough:-).

Thanks hisakimatama, this is just what I was looking for.

When I run it and look in Task Manager, at the sqlservr.exe process, the value in the column Memory (Private Working Set) quickly cranks up and fills the amount of memory I allocated to this SQL Server instance. Of course it kept on running then crashed out Management Studio! But now it looks to the VMware team like this SQL Server needs that amount of RAM -- exactly what I need.

So here's what I did: I made a little table, and have the results of this query insert into that table. Then I do a truncate table and a shrink database, and I saved this all into a Job and will run it a few times per day. All of this gives the server a great workout and makes it look like I need all the resources allocated to it.

Yes it is unfortunate that I have to go thru all this to keep a semi-robust Dev VM, but if you've ever worked for the Federal Goverment you'd know you have to do silly things like this all the time! If you don't believe me, look at this video that 6+ million people have watched -- it is like this every day!

The Expert

https://www.youtube.com/watch?v=BKorP55Aqvg

(If you haven't ever worked for the goverment you will think this is insane. Right!)

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 8

You should stop looking at task manager for ram used, SQL Server lies to task manager. As well, the statistic for "Active Memory" in VSphere is not accurate.

"Task Manager is a dirty, filthy liar." -Brent Ozar

http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory

"This counter only describes the volume of memory pages that have been touched over the last sampling period. It does not describe if they are the same memory pages or different memory pages between sampling periods." - Mark Achtemichuk

https://blogs.vmware.com/vsphere/2013/10/understanding-vsphere-active-memory.html

A better alternative is

DBCC MEMORYSTATUS

http://msdn.microsoft.com/en-us/library/ms176018.aspx

Or something like VMMAP

http://technet.microsoft.com/en-us/sysinternals/dd535533