Jeremy Carter

The dmv sys.dm_os_buffer_descriptors gives a detailed look into how the buffer pages in SQL server are being used. Aside from just satisfying a morbid curiosity into where the memory is going, the details can be a helpful troubleshooting tool. When I first look at a server I need some way of deciding where I should concentrate my tuning efforts and get the biggest bang for the buck. One way I decide where to start is by looking at the memory usage of each db. The breakdown can also be very handy providing information to use in determining sql server consolidations projects or helping decide when it is time to break that db out to its own server.

At a high level we can tell the sql process is using x amount of ram and the following script lets you start to break that down to individual databases. This script takes sys.dm_os_buffer_descriptors and formats the output so that each database is listed with the size of the clean, dirty, and total buffers.

SELECT
[DatabaseName],
	ISNULL([Dirty],'0') AS [Dirty],
	ISNULL([Clean],'0') AS [Clean],
	ISNULL([Total],'0') AS [Total]
FROM(
SELECT
  (CASE WHEN ([database_id] = 32767) THEN 'Resource Database'
		ELSE ISNULL(DB_NAME (database_id),'Total')
	END) AS 'DatabaseName',
  (CASE WHEN ([is_modified] = 1) THEN 'Dirty'
		WHEN ([is_modified] = 0) THEN 'Clean'
		ELSE 'Total'
	END) AS 'State',
  COUNT (*)/128 AS 'SizeInMB'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified] WITH CUBE
) AS SourceTable
PIVOT(SUM([SizeInMB]) FOR [State] IN (Clean, Dirty, Total)) AS PivotTable
ORDER BY [DatabaseName]

The results will look something like this:

Welcome back to part 2 of our series on memory. Hopefully you have all had a good week and you don’t have any more fires to put out. So now that we can get down to business we previously learned in part 1 why sql server is using all available memory and what we can do about it.  Today in part 2 we are going to explore the second memory related question that I am always being asked.

Question #2 – Why does task manager only show sql is using a few hundred MB?

In the same breath of the question they then state that task manager also shows something is using most of the ram, and they think it is sql server but are not sure.  While you can probably be safe in saying that it is indeed sql using this “missing” memory there are ways we can verify it. The easiest way is to run the following query inside of sql server.

select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] 
from  sys.dm_os_memory_clerks

If you were paying careful attention to the query above you will notice it returns how much memory is allocated using something called AWE. What is AWE you ask? It stands for Address Windowing Extensions. This is part of what allows us to use more memory than is typically allowed in a 32bit environment. The key to the disparity in task manager is that any memory that is used with AWE is not assigned as part of the Process Private Bytes. In task manager we are seeing all of the sql server process memory except the bufferpool which is the only cache that can take advantage of AWE.

OK so that explains the disparity in a 32bit environment but what if you see this in a 64bit environment? Again the answer has to do with AWE and this is where people start to really get confused. Most people think that AWE is only for 32bit. On the surface you are correct as the setting is not needed for 64bit and in fact the setting is ignored by SQL Server. However the SQL Server team at Microsoft found a nice side benefit of using the AWE api. It turns out that any memory that is allocated using the AWE api is locked and cannot be paged out which gives some performance benefits. Thus the term and feature “Lock Pages in Memory” was born.

Well I am getting older but we aren’t going to talk about that! And hopefully your memory hasn’t gone for a swim like the picture to the left, I can’t help with that. So what am I talking about? Well it seems that I get asked some variance of this question a couple of times each week. Generally speaking there are two variations of the question and I will address the first question in part 1 of this two part series.

Question #1 – Why is SQL Server using all of the available memory?

The answer to this one is pretty simple. SQL Server is incredibly memory hungry! SQL has its own memory manager and will try and grab as much memory as it can, even to the point of starving the OS or other processes that dare run on the same hardware.

So that brings us to the follow-up question I hear, “Well then why is it using all of the memory even during non-peak times?” Again this is a pretty quick answer. Once SQL Server allocates its memory resources it is loath to give them up. After all what could possibly be more important than SQL caching every bit of data it can? (Pun intended ) On a more serious side it takes resources and time to allocate the memory and if SQL needed the memory once, it will probably need it again. In the end it’s just a matter of efficiency to let SQL Server hang on to the memory and allocate it how it sees fit.

Finally we need to address how we limit SQL Server’s lust for memory. Luckily Microsoft has given us two configuration options for doing just that. The first and IMHO most important is max server memory. By default max server memory is set to 2147483647 Megabytes! Now obviously this max setting is a bit of a dream and is not ideal. Generally speaking if the server is dedicated to the sql engine and has 16GB or less of ram then I set max server memory to the amount of physical ram minus 2GB. So if the server has 16GB then I will configure max server memory to 14GB. If the server has more than 16GB then I typically use the amount of physical ram minus 4GB. So if the server has 64GB of ram I would set a max of 60GB. If the server is not dedicated to the sql engine then make sure you take those other features and programs into account. Leave plenty of room for the others so everyone can get along and play nice together.

One thing that I have noticed is that the more ram you have, it seems the more you need to leave available. This phenomenon can be explained by a quirk you should keep in the back of your mind. Unlike what the setting name implies, know that max server memory does not limit the entire sql process; it only limits the buffer pool. So don’t be surprised if you look at the SQL Server process and notice it is using more than the max limit you set. Generally the more ram you have, the more processors you will also have, which means more threads are allocated by default and each of those threads is using memory outside the buffer pool. In addition if you like to use linked servers, SQL CLR, and other features they will get their memory from outside the buffer pool.

So now that you have done some quick calculations in your head, here is how you configure the max server memory using TSQL.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
--set the max to 4GB
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

The second setting available to us is minimum server memory. Generally speaking I don’t mess with this much. Typically the only time I do is if there are multiple instances of sql running on the same server. However there are other schools of thought that say you should set the min server memory setting to the same amount as max server memory. Either way it does pay to understand the behavior of the setting so that you can make your own informed decision.

Should the server experience memory pressure, sql server will either decide on its own to give up memory or it will be forced to do so. The min server memory option allows us to specify a minimum amount of memory sql will not go below. Again this setting is only enforced for the buffer pool and has its own quirks. Many people mistakenly believe that this setting means sql will grab at least this much memory and never drop below it. However sql does not grab the minimum amount until it is actually needed. So if sql never goes above the minimum then the minimum will not apply.

Once you have made your decision, this is how you configure the min server memory setting using TSQL.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
--set the min to 1GB
sp_configure 'min server memory', 1024;
GO
RECONFIGURE;
GO

*Edited: Here is the link to part 2.