SQL Server Available Internal Memory

Last Post 02 Jun 2011 06:26 AM by rerichards. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Resolved
rerichards
New Member
New Member

--
01 Jun 2011 07:35 AM
We are running SQL 2008 Enterprise 64 bit.

We have 32GB physical RAM with 27GB Max Server Memory.

We are experiencing external memory pressure, with less than 200 available MB, at times. I know which processes are consuming the external memory.

I would like to reduce Max Server Memory to allow more memory for the external processes and was wondering if there is a DMV script or Perfmon counter that tells how much internal memory is available?

In other words, I do not want to just randomly give memory to the OS without being confident that there is available internal memory to give the OS.

Is there is script or Perfmon counter that shows how much is memory is actively in use (not simply allocated) but the amount in use, so as to gauge that I am not going to be starving an internal process by giving to an external process?
gunneyk
New Member
New Member

--
01 Jun 2011 04:09 PM
What exactly do you mean by Internal Memory? This DMV gives overal health type info:

SELECT * FROM sys.dm_os_sys_info

The Page Life Expectancy counter might be your best bet for how well SQL Server is using memory. If it gets too low you will do more physical I/O.

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE N'%Buffer Manager%'
AND counter_name = N'Page life expectancy';

And this will show object level usage

-- Database level Buffer Cache Contents
SELECT TOP 100
obj.[name] AS [Table Name],
i.[name] AS [Index Name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB,
obj.data_compression_desc
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS [name]
,index_id ,allocation_unit_id, object_id, p.data_compression_desc
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS [name]
,index_id, allocation_unit_id, object_id, p.data_compression_desc
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc], obj.data_compression_desc
ORDER BY [Table Name]

But the bottom line is that it is hard to tell what memory is actually actively being used. I would use the PLE counter as a reference.
rerichards
New Member
New Member

--
01 Jun 2011 09:11 PM
What I am trying to say is this:

If SQL Server (PETER in this instance) has been given 27GB to operate on and the OS (PAUL in this instance) has been given 5GB to operate with, and the OS needs more memory, how can I be confident that robbing PETER to pay PAUL is not going to have a negative affect on PETER?
gunneyk
New Member
New Member

--
02 Jun 2011 04:18 AM
There really isn't any way to truly determine this at runtime that I know of. There are two main aspects to this. The first is what impact you will have if you don't give the OS (or really other apps) the memory they need to run. You will cause paging at the very least which is never good for performance of anything on the box. The other aspect is how much memory does SQL Server need. That is going to be different at any given time depending on the load. If SQL Server does not have Lock Pages In Memory enabled it will dynamically adjust to give the OS requested memory. However it might not be as fast as needed. The memory that it will trim will be areas of the buffer pool that are least used just as it would do internally if more memory was needed for say the procedure cache. So again PLE is probably going to be your best relative indication of how much memory SQL Server can give up before serious performance occurs due to lack of cached pages. But again keep in mind that starving the OS may have a bigger impact overall than not giving memory back. The bottom line is that it will totaly depend on all the factors at that given time as to exactly how any loss in memory will affect it.
I always recommend givign the OS or other apps as much memory as they need at peak by adjusting the MAX Memory down to allow for that. You might need to add more memory to the box to get those numbers right though. Or consider moving those other processes off the SQL Server box which is the best practice anyway when possible.
rerichards
New Member
New Member

--
02 Jun 2011 06:26 AM
Thanks gunneyk. The answer you provided was one I assumed. Thanks for the help.
You are not authorized to post a reply.

Acceptable Use Policy