Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Help!... Memory Use on Windows 2008 R2, SQL Server 2005 x64. The physical is 98% Expand / Collapse
Author
Message
Posted Thursday, March 15, 2012 10:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:16 PM
Points: 17, Visits: 120
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....
Post #1267628
Posted Thursday, March 15, 2012 11:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1267704
Posted Thursday, March 15, 2012 1:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1267796
Posted Thursday, March 15, 2012 1:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:16 PM
Points: 17, Visits: 120
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.
Post #1267798
Posted Thursday, March 15, 2012 1:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:16 PM
Points: 17, Visits: 120
[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
Post #1267802
Posted Thursday, March 15, 2012 1:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1267816
Posted Thursday, March 15, 2012 1:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1267820
Posted Thursday, March 15, 2012 1:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:16 PM
Points: 17, Visits: 120
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...
Post #1267831
Posted Thursday, March 15, 2012 2:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1267837
Posted Thursday, March 15, 2012 2:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:16 PM
Points: 17, Visits: 120
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!!!!
Post #1267848
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse