Cleveland DBA

SQL Server administration in Cleveland, OH

How is SQL Server using all that memory, anyway?

I want to stick with the subject of memory because I think it’s probably the most misunderstood (and sometimes downright mysterious) component of SQL Server. I know I, for one, could benefit from a better understanding of its inner workings. So today I’d like to share that little bit of code I mentioned last week, that I find very handy for getting an overall picture of how memory is being used by a SQL instance. Personally I run it as a complete script, but I’ll break it down here and provide a little description of each section. I should say, too, that I didn’t write any of this from scratch. For most of it I started with code found here, here, and here, and modified it to suit.

So, without further ado, let’s get started. The first section provides an big-picture look at how SQL is currently allocating memory between the database page cache, procedure cache, and miscellaneous memory usage. It also provides the total memory usage, and I added in the maximum available workspace memory.

--HOW MUCH MEMORY IS MY SQL SERVER USING?

declare @plan_cache_size float, @obj_data_size float , @avail_workspace_size float

-- plan cache size

select @plan_cache_size = (cntr_value*8)/1024.0 FROM sys.dm_os_performance_counters
	WHERE object_name like '%Plan Cache%' and counter_name = 'Cache Pages' and instance_name = '_Total'

-- DATABASE PAGE CACHE (PAGES, INCLUDING INDEXES)

select @obj_data_size = (cntr_value*8)/1024.0  FROM sys.dm_os_performance_counters
	WHERE object_name like '%Buffer Manager%' and counter_name like 'Database pages%'

-- Maximum workspace available for sorts, hashes, etc

select @avail_workspace_size = (cntr_value)/1024.0 FROM sys.dm_os_performance_counters
	WHERE object_name like '%Memory Manager%' and counter_name = 'Maximum Workspace Memory (KB)'

-- CACHE TOTALS
select   @obj_data_size [Database_Page_Cache_MB]
 , @plan_cache_size [Procedure_Cache_MB]
 , [Misc_Memory_Usage_MB]
 , [Misc_Memory_Usage_MB] + @obj_data_size + @plan_cache_size [Total_Memory_Usage_MB]
 , @avail_workspace_size [Maximum_Workspace_MB]
from
(
 select sum(cntr_value)/1024.0 [Misc_Memory_Usage_MB]
 from sys.dm_os_performance_counters
 where object_name like '%memory%'
 and (
   counter_name like '%Connection Memory (KB)%'
   or
   counter_name like '%Granted Workspace Memory (KB)%'
   or
   counter_name like '%Lock Memory (KB)%'
   or
   counter_name like '%Optimizer Memory (KB)%'
   or
   counter_name like '%SQL Cache Memory (KB)%'
 )
) x
GO

The output:

The next section uses sys.dm_os_memory_cache_counters to examine how the procedure cache is being broken down.

-- How the Procedure cache is being used

SELECT  TOP 6
	LEFT([name], 20) as [name],
	LEFT([type], 20) as [type],
	([single_pages_kb] + [multi_pages_kb])/1024 AS cache_mb,
	[entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC

The output:

The third part of the script goes deeper into the procedure cache and displays the top 25 cached plans by size. Because it displays some application code, I’m not going to include the results here.

-- Top cached plans by size

select top 25
cacheobjType
, ObjType
, (pagesUsed * 8192)/1024.0/1024.0 [space_used_MB]
, db_name(dbid) [database_name]
, object_name(objid, dbid) [object_name]
, [sql]
from master..syscacheobjects (nolock)
order by pagesUsed desc
GO

And, finally, the last section shows us how much of the buffer pool is being utilized by each database, including the dirty and clean page counts, sorted by total memory.

-- Buffer Pool Memory Per DB

SELECT
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount,
SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,
count(*)AS TotalPageCount,
cast(count(*) * 8192.0 / (1024.0 * 1024.0) as decimal(8,2)) as BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY TotalPageCount desc
GO

And the output:

And there you have it. I hope you find this useful!

May 2, 2011 Posted by | General, SQL Tuning, Troubleshooting | , , , | 1 Comment

Available Workspace Memory

My research into the Resource Governor question and its answer brought up another question for me:

SELECT * FROM sys.dm_exec_query_resource_semaphores

Where does that total_memory_kb number for the default pool actually come from? After all, I have a max server memory of 26000MB for the instance, so why 18175MB?  The BOL description for this field wasn’t very helpful.  Googling for total_memory_kb didn’t really yield me much.  Most of what I found was unrelated to this particular DMV.

So I started looking at the sys.dm_os_performance_counters, and there I finally found something that looked promising.

 select cntr_value FROM sys.dm_os_performance_counters 
WHERE object_name like '%Memory Manager%' and counter_name = 'Maximum Workspace Memory (KB)' 

If you add up the 2 total_memory_kb values for the default pool, you get the maximum workspace memory. Woot!

That still didn’t tell me how that particular value was derived, but it gave me something better to search on. And that search returned this article at SQLMag.com which says (emphasis mine):

The SQL Server: Memory Manager object includes the following workspace-related PerfMon counters: Memory Grants Pending, which tells you the number of waiting queries; Memory Grants Outstanding, which counts the number of queries that have grants and are currently executing; Granted Workspace Memory (KB), which measures the total amount of grants SQL Server has made to running queries; and Maximum Workspace Memory (KB), which measures the maximum amount of memory SQL Server is currently willing to devote to workspace-memory grants. In general, SQL Server is able to devote between 25 and 75 percent of the buffer pool’s virtual memory to workspace memory. The value varies based on the demand for memory for other uses (such as database page cache and plan cache). If demand is low, SQL Server devotes a larger fraction of memory to workspace memory needs; if demand is high, SQL Server devotes a smaller fraction to workspace memory.

So there you have it. I feel better, don’t you feel better?

That entire article is worth reading for a better understanding of SQL Server memory. In fact, it helped me put together a bit of code to show me how memory is being used by SQL Server. More on that in a future post (I’ve got to give you a reason to keep coming back, right?).

April 28, 2011 Posted by | General, SQL Tuning, Troubleshooting | , | Leave a comment

Aha!

I found the answer to my Resource Governor quandary in this outstanding article, Understanding SQL Server Memory Grant, on MSDN.  The juicy bit:

The server calculates the memory limit for one query. By default, this is 25% (20% on 32bit SQL 2005) of total query memory (which is set by memory broker as about 90% of server memory). This per-query limit helps to prevent one query from dominating the whole server. This percentage is configurable on SQL 2008.

And how is it configurable, you ask?  By using Resource Governor to override it.

Really though, read the article, it’s extremely informative.  I’d like to give the author a great big hug.

April 25, 2011 Posted by | Features, Troubleshooting | , , | 1 Comment

Resource Governor causing memory problem??

So.  Last week I blogged about the experiment I was running with Resource Governor for one of our newly upgraded development databases.  I had enabled Resource Governor and created a procedure to categorize sessions, but I hadn’t set any limits and everyone was sharing the same default pool.  The plan, if you’ll recall, was to monitor the Resource Governor stats to get a feel for the resources different apps were using.

This week, users have been doing a lot of testing in this environment.  A developer called me on Wednesday morning and said that users were complaining of very slow performance.  So I took a look and saw several sessions waiting on resource_semaphores.  Now, I’ve been supporting this application’s database for years now and I’ve never seen any memory waits, so this got my attention quick.  One of the sessions that was running was one of the reports that I’d enabled Resource Governor to address in the first place.  A look at sys.dm_exec_query_memory_grants told me this query was requesting almost 19GB of memory (or, put another way, all of the available memory).  What the…?

I captured the query (and asked the user to kill his report) and ran the query in our production (SQL 2005) database.  In that environment it was only requesting 7GB of memory.  Still a lot, but do-able in a system with 40GB available.  The query plans were slightly different between the 2 environments, but updating stats didn’t change that at all.  I decided to use Resource Governor as it was intended, and I set a 40% memory grant limit for the Reports workload group.  I re-ran the query and this time it was only requesting 7GB of RAM.

The immediate problem was remediated, but it niggled at the back of my brain; why had the query requested that much memory in the first place?  Thursday morning I got another call.  This time, when I checked the system, a different application was requesting all of the available memory.  I could have set a restriction for that workload group, too, but this was just treating the symptoms.  There had to be a root cause.

I started searching the Great Gazoogle for known memory issues in SQL 2008, but found nothing that explained why queries were suddenly using a lot more memory than they did in SQL 2005.  Think.  Think.  Think.  What if Resource Governor is the problem?  So I ran a series of tests:  with Resource Governor disabled, with it enabled and the memory limit set to 40%, and enabled with the memory limit set to 100% (or unlimited).  I also ran it in our 2005 environment just for comparison purposes, and I monitored sys.dm_exec_query_memory_grants for each test.  Here are the very interesting results.

Again I say:  what the…?  With Resource Governor disabled the query only uses 4.8GB of memory.  Enabling Resource Governor actually appears to cause the query to request more memory.  It almost looks like, rather than being a limit, the memory grant % is telling the query how much to use (putting ideas into its head).  I know that can’t be the case, but that’s how it looks.  I thought maybe I’d misunderstood the settings, that maybe I’d mis-configured Resource Governor, but I don’t think I did.

Memory Grant %
Specify the maximum amount of memory that a single request can take from the pool. Range is 0 to 100.

And technically, that’s what SQL Server did.  I said “this group can use 100% of the memory,” and it gave that query 100% of the memory.  Or tried to, anyway.  But if it really needed all that memory, why doesn’t it ask for it with Resource Governor disabled?

So far I haven’t been able to find any bugs to explain this behavior.  And a post to SQLServerCentral’s forums hasn’t yielded any responses.  I guess for now I’m going to leave it disabled and keep searching for an explanation.  If anyone out there has any insight, please post a comment.  I’d really appreciate it.

April 22, 2011 Posted by | Features, Troubleshooting | , , , | 1 Comment