SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help!... Memory Use on Windows 2008 R2, SQL Server 2005 x64. The physical is 98%


Help!... Memory Use on Windows 2008 R2, SQL Server 2005 x64. The physical is 98%

Author
Message
juanc.aguirre
juanc.aguirre
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 158
Hello to everyone, first... sorry for my english... I'm from Colombia.

I have a situation that i can't understand, a SQL Server 2005 x64 standard edition is installed on a Windows 2008 R2 Standard edition.

select @@version
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: )


Actually this server have installed 8 GB of RAM, and the settings for the instance is the next:

select name, value
from sys.configurations
where name like '%memory (MB)%'

min server memory (MB) 0
max server memory (MB) 4096


The problem is... that the use of the Physical Memory for the server has come to 97% or 98%... but the sqlserver.exe process on the task manager show only 2.3 GB used.

Now... the DBCC MEMORYSTATUS command has the next results

dbcc memorystatus

VM Reserved 8475640
VM Committed 2392600
AWE Allocated 0
Reserved Memory 1024


My question is... Why if I have the max memory limited to 4 GB... the VM Reserved shows 8 GB... and the physical memory used by the process is just 2.3 GB ????... and... why the physical memory of the server is used to 98%....

NOTE: No others aplications or services are installed on this server... this server is dedicated for SQL Server.

Now... all the Servers Administrators are calling to me... they are worried for this consume... if this server is restarted, just take a few minutes for that the physical memory comes to 98% of use.


Thanks!!!!!! for the help....
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30153 Visits: 9730
The max memory setting is just for the buffer pool. If SQL Server needs more resources for other things, it will use it.

Do your databases have any CLR objects in them? Those can eat a lot of RAM if not managed correctly.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18997 Visits: 14398
What does this return on your instance:

-- top 10 consumers of memory
SELECT TOP 10
type,
CAST(SUM(single_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [SPA Mem, Mb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC ;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
juanc.aguirre
juanc.aguirre
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 158
Hello... thanks for you quick answer, so... I really don't know if any CLR object exist on the DataBase, the application was not designed by me.

The question is... is this relationated with the version of the OS and the SQL Server, or if they are 32 or 64 bits. I already have another servers with the same hardware configuration, but the problem with the memory is not present on those.
juanc.aguirre
juanc.aguirre
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 158
[b]opc.three
What does this return on your instance:

-- top 10 consumers of memory
SELECT TOP 10
type,
CAST(SUM(single_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [SPA Mem, Mb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC ;


Thanks... this is the result...

CACHESTORE_SQLCP 1049.37
CACHESTORE_PHDR 30.23
CACHESTORE_OBJCP 20.23
MEMORYCLERK_SQLGENERAL 14.54
MEMORYCLERK_SOSNODE 8.77
USERSTORE_SCHEMAMGR 5.14
OBJECTSTORE_LOCK_MANAGER 3.54
MEMORYCLERK_SQLSTORENG 2.97
OBJECTSTORE_SNI_PACKET 2.54
USERSTORE_DBMETADATA 2.32
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18997 Visits: 14398
Do you use Linked Servers heavily on this particular instance?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18997 Visits: 14398
Run this one too please and post the results, this one includes multi-page allocations:

-- top 10 consumers of memory
SELECT TOP 10
type,
CAST(SUM(single_pages_kb+multi_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [Mem, Mb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb+multi_pages_kb) DESC ;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
juanc.aguirre
juanc.aguirre
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 158
opc.three (3/15/2012)
Run this one too please and post the results, this one includes multi-page allocations:

-- top 10 consumers of memory
SELECT TOP 10
type,
CAST(SUM(single_pages_kb+multi_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [Mem, Mb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb+multi_pages_kb) DESC ;



Hello... there's no server link on that instance.

and... the result of the query is

MEMORYCLERK_SQLOPTIMIZER 1.95
CACHESTORE_PHDR 30.23
CACHESTORE_XMLDBTYPE 0.01
CACHESTORE_EVENTS 0.02
USERSTORE_OBJPERM 0.40
USERSTORE_TOKENPERM 0.37
MEMORYCLERK_SQLSTORENG 15.59
CACHESTORE_XPROC 0.05
OBJECTSTORE_SNI_PACKET 2.66
CACHESTORE_BROKERRSB 0.01


Thanks...
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18997 Visits: 14398
juanc.aguirre (3/15/2012)
opc.three (3/15/2012)
Run this one too please and post the results, this one includes multi-page allocations:

-- top 10 consumers of memory
SELECT TOP 10
type,
CAST(SUM(single_pages_kb+multi_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [Mem, Mb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb+multi_pages_kb) DESC ;



Hello... there's no server link on that instance.

and... the result of the query is

MEMORYCLERK_SQLOPTIMIZER 1.95
CACHESTORE_PHDR 30.23
CACHESTORE_XMLDBTYPE 0.01
CACHESTORE_EVENTS 0.02
USERSTORE_OBJPERM 0.40
USERSTORE_TOKENPERM 0.37
MEMORYCLERK_SQLSTORENG 15.59
CACHESTORE_XPROC 0.05
OBJECTSTORE_SNI_PACKET 2.66
CACHESTORE_BROKERRSB 0.01


Thanks...

That does not look right, did you include the ORDER BY when you ran it?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
juanc.aguirre
juanc.aguirre
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 158
opc.three (3/15/2012)
juanc.aguirre (3/15/2012)
opc.three (3/15/2012)
Run this one too please and post the results, this one includes multi-page allocations:

-- top 10 consumers of memory
SELECT TOP 10
type,
CAST(SUM(single_pages_kb+multi_pages_kb) / 1024.0 AS DECIMAL(10, 2)) AS [Mem, Mb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb+multi_pages_kb) DESC ;



Hello... there's no server link on that instance.

and... the result of the query is

MEMORYCLERK_SQLOPTIMIZER 1.95
CACHESTORE_PHDR 30.23
CACHESTORE_XMLDBTYPE 0.01
CACHESTORE_EVENTS 0.02
USERSTORE_OBJPERM 0.40
USERSTORE_TOKENPERM 0.37
MEMORYCLERK_SQLSTORENG 15.59
CACHESTORE_XPROC 0.05
OBJECTSTORE_SNI_PACKET 2.66
CACHESTORE_BROKERRSB 0.01


Thanks...

That does not look right, did you include the ORDER BY when you ran it?


Yea... you're right

the correct result is...


CACHESTORE_SQLCP 1029.78
CACHESTORE_PHDR 30.23
MEMORYCLERK_SOSNODE 24.59
CACHESTORE_OBJCP 19.91
MEMORYCLERK_SQLGENERAL 18.13
MEMORYCLERK_SQLSTORENG 15.63
OBJECTSTORE_LOCK_MANAGER 5.75
USERSTORE_SCHEMAMGR 5.00
OBJECTSTORE_SNI_PACKET 3.04
USERSTORE_DBMETADATA 2.26



Thanks!!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search