Controlling Sql Server Memory Usage

Since Sql Server 2008 launched, we’ve had an excellent tool called Resource Governor, which has allowed us to control how much system resources were being used. This isn’t limited to resources used by the SQL Server instance itself , but you can actually  create resource pools, which are then linked to applications or even users.

I don’t want to go into details of how to set all of this up – for that you can simply go here.

The Limitation

What I did find out the other day was quite interesting though. I had a customer who was running a DBCC CHECKDB command as part of a batch script. This was checking the integrity of a very large database , and this one command would use up all the RAM on the server.

The DBA had enabled Resource Governor ,created the Resource Pool correctly, and set the maximum memory to be used to an amount lower than the system maximum. So this shouldn’t be happening.

When scanning the documentation around Resource Governor ,the following extract was found :

“Examples in SQL Server 2008 that are not managed by the Resource Governor are extended stored procedures, linked servers, COM objects, and certain SQLCLR objects (depending on garbage collection). In addition, the Resource Governor does not manage memory used by the buffer pool for data pages”

A Potential Solution

So the DBCC command falls outside the scope of what Resource Governor was controlling ( from my understanding ). However , we still had the older option of memory limits available to us , the‘max server memory’ setting. This allows you to set the maximum amount of server memory an instance of SQL Server can utilize. Its very easy to set up as well , an example would be :

sp_configure  ‘max server memory’ , 4096

RECONFIGURE
GO

Keep in Mind

When you set this limit, SQL Server will not use all of it immediatly, but gradually. If you are running multiple instances of SQL Server on a machine , you can use this setting inside every instance to control memory usage, in conjunction with the min server memory setting.

Just keep in mind the following,  from MSDN : “Note that the max server memory option only limits the size of the SQL Server buffer pool, and does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components.”

Business Intelligence TV

On YouTube you’ll now find a dedicated channel with videos on Business Intelligence. Check it out here :

Business Intelligence TV

WARNING : Some videos may contain scenes of seriously dodgy humour…