SQL Server 2008R2 using more memory

Question

Post reply

SQL Server 2008R2 using more memory

SQListic

SSCertifiable

Points: 6904
More actions
July 30, 2012 at 11:41 am

#261736

Hi Experts,
got a request from systems team that in one of our prod system sql server using 7.25GB of 8GB memory.
i used follwing queries frm msdn: after executing queries i see results.. but dont know how to proceed from there..
Thanks all!
A. Returning cached page count for each database
The following example returns the count of pages loaded for each database.
Copy
SELECT count(*)AS cached_pages_count
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
B. Returning cached page count for each object in the current database
The following example returns the count of pages loaded for each object in the current database.
Copy
SELECT count(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
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
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC;

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

that's normal and by design.

SQL tries to keep everything in memory in order to better serve multiple requests for the same data.

unless you tell it otherwise, SQL uses all the memory available.

on an 8 gig machine, you probably want to limit SQL to 7 gig, leaving 1 gig for the operating system. if you have other processes on that server besides SQL, you might want to limit SQL even more to accompdate some memory for those otehr processes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQListic SSCertifiable Points: 6904 More actions · Answer 2

Thanks for the reply:

We dont have any other applications running on this server other than sql server.

By reducing max memory

Does it reduce peroformance?

Is it is the only option to reduce memory?

Is this is natural on a prod boc to use more than 90% memory?

can we clear some buffer cached pages, not all?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Sqlism (7/30/2012)
Does it reduce peroformance?

Not unless you reduce it to a stupidly low value

Is it is the only option to reduce memory?
Is this is natural on a prod boc to use more than 90% memory?

If you don't set max server memory, you're telling SQL it can use up to the default, which is 2000 TB of memory. Now if you have 2000 TB of memory, that's OK, if you don't, it can result in OS memory starvation and even if it doesn't the SQL instance sits in this permanent 'allocate memory, release memory, allocate memory, release memory' loop

can we clear some buffer cached pages, not all?

You can, but that's not going to reduce SQL's memory allocation (it'll just result in free pages in the buffer pool) and in general it's better to leave buffer pool management to SQL

Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQListic SSCertifiable Points: 6904 More actions · Answer 4

Wow thanks a ton!

Really u taught me a lot things here.

Great!

Jared SSC Guru Points: 61793 More actions · Answer 5

Here is a quick guide I use for max server memory when I forget the settings... Not: This is not exact and may differ from machine to machine, but is a good start. http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

Jared
CE - Microsoft