March 15, 2012 at 10:05 am
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 Reserved8475640
VM Committed2392600
AWE Allocated0
Reserved Memory1024
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....
March 15, 2012 at 11:30 am
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
March 15, 2012 at 1:29 pm
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
March 15, 2012 at 1:32 pm
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.
March 15, 2012 at 1:34 pm
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_SQLCP1049.37
CACHESTORE_PHDR30.23
CACHESTORE_OBJCP20.23
MEMORYCLERK_SQLGENERAL14.54
MEMORYCLERK_SOSNODE8.77
USERSTORE_SCHEMAMGR5.14
OBJECTSTORE_LOCK_MANAGER3.54
MEMORYCLERK_SQLSTORENG2.97
OBJECTSTORE_SNI_PACKET2.54
USERSTORE_DBMETADATA2.32
March 15, 2012 at 1:42 pm
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
March 15, 2012 at 1:46 pm
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
March 15, 2012 at 1:54 pm
opc.three (3/15/2012)
Run this one too please and post the results, this one includes multi-page allocations:
-- top 10 consumers of memorySELECT 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_SQLOPTIMIZER1.95
CACHESTORE_PHDR30.23
CACHESTORE_XMLDBTYPE0.01
CACHESTORE_EVENTS0.02
USERSTORE_OBJPERM0.40
USERSTORE_TOKENPERM0.37
MEMORYCLERK_SQLSTORENG15.59
CACHESTORE_XPROC0.05
OBJECTSTORE_SNI_PACKET2.66
CACHESTORE_BROKERRSB0.01
Thanks...
March 15, 2012 at 2:01 pm
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 memorySELECT 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_SQLOPTIMIZER1.95
CACHESTORE_PHDR30.23
CACHESTORE_XMLDBTYPE0.01
CACHESTORE_EVENTS0.02
USERSTORE_OBJPERM0.40
USERSTORE_TOKENPERM0.37
MEMORYCLERK_SQLSTORENG15.59
CACHESTORE_XPROC0.05
OBJECTSTORE_SNI_PACKET2.66
CACHESTORE_BROKERRSB0.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
March 15, 2012 at 2:14 pm
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 memorySELECT 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_SQLOPTIMIZER1.95
CACHESTORE_PHDR30.23
CACHESTORE_XMLDBTYPE0.01
CACHESTORE_EVENTS0.02
USERSTORE_OBJPERM0.40
USERSTORE_TOKENPERM0.37
MEMORYCLERK_SQLSTORENG15.59
CACHESTORE_XPROC0.05
OBJECTSTORE_SNI_PACKET2.66
CACHESTORE_BROKERRSB0.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...
[font="Courier New"]
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
[/font]
Thanks!!!!
March 15, 2012 at 2:22 pm
From your original post:
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%....
VM reserved means just that, reserved, not the same as used (committed). It just says that is the upper bound of what could be used.
Physical memory is 2.3 GB, of the 4GB you have have allowed it. This means that your instance has not received enough requests for data that would have caused SQL Server to load 4GB of data from disk, i.e. your instance is either underutilized for the hardware its on or the buffer pool has not completely warmed up.
It does not appear that SQL Server is your issue. What else is using memory on the server?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 15, 2012 at 2:43 pm
opc.three (3/15/2012)
From your original post: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%....
VM reserved means just that, reserved, not the same as used (committed). It just says that is the upper bound of what could be used.
Physical memory is 2.3 GB, of the 4GB you have have allowed it. This means that your instance has not received enough requests for data that would have caused SQL Server to load 4GB of data from disk, i.e. your instance is either underutilized for the hardware its on or the buffer pool has not completely warmed up.
It does not appear that SQL Server is your issue. What else is using memory on the server?
Hi.
This server is dedicated to SQL Server... no other applications are running on the server... just a site of IIS is hosting on this server. But... the procces of the IIS is just Using 200 MB (max) according to the task administrator.
I don't understand this situation!!...
March 15, 2012 at 2:48 pm
Antivirus software, other utility type software?
March 15, 2012 at 3:13 pm
Please run this from a PowerShell prompt and post the results:
Get-WMIObject Win32_Process | Select Name,@{Name="WorkingSetSize(MB)";Expression={"{0:N1}" -f($_.WorkingSetSize/1mb)}} | Sort-Object Name
This will show us the list of all processes running on your server and how much memory each is using.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 15, 2012 at 3:29 pm
opc.three (3/15/2012)
Please run this from a PowerShell prompt and post the results:Get-WMIObject Win32_Process | Select Name,@{Name="WorkingSetSize(MB)";Expression={"{0:N1}" -f($_.WorkingSetSize/1mb)}} | Sort-Object Name
This will show us the list of all processes running on your server and how much memory each is using.
Thanks... the result...
[font="Courier New"]Name WorkingSetSize(MB)
---- ------------------
admdat.exe 30.4
AgPkiMon.exe 5.0
amswmagt.exe 1.1
Browse.exe 23.7
CAF.exe 9.7
cam.exe 1.1
CapturaPBX.exe 3.6
casdscsvc.exe 1.4
casplitegent.exe 2.1
ccApp.exe 0.7
ccApp.exe 0.6
ccnfAgent.exe 15.3
ccsmagtd.exe 4.3
ccSvcHst.exe 3.4
cfFTPlugin.exe 0.9
cfnotsrvd.exe 1.0
cfsmsmd.exe 1.1
cmd.exe 0.4
cmd.exe 0.4
conhost.exe 0.3
conhost.exe 0.3
conhost.exe 5.1
conhost.exe 0.3
conhost.exe 0.3
conhost.exe 5.2
CSAMPmux.exe 1.3
csrss.exe 2.1
csrss.exe 0.2
csrss.exe 5.4
csrss.exe 1.9
dm_primer.exe 0.4
dwm.exe 0.4
dwm.exe 3.6
explorer.exe 33.9
explorer.exe 16.0
inetinfo.exe 1.9
jusched.exe 6.5
jusched.exe 0.6
lic98Service.exe 0.4
LogonUI.exe 0.2
LogWatNT.exe 0.9
lsass.exe 21.4
lsm.exe 3.4
msdtc.exe 1.1
MsDtsSrvr.exe 2.5
msftesql.exe 1.0
msmdsrv.exe 6.7
perfmon.exe 24.0
powershell.exe 58.4
powershell.exe 37.2
ProtectionUtilSurrogate.exe 4.9
ProtectionUtilSurrogate.exe 11.3
rcHost.exe 4.8
rdpclip.exe 6.1
rdpclip.exe 2.0
regsvr32.exe 0.4
RtaAgent.exe 1.6
Rtvscan.exe 5.3
services.exe 5.5
Servidor.exe 23.5
Smc.exe 6.8
SmcGui.exe 5.0
SmcGui.exe 6.2
smss.exe 0.5
SMSvcHost.exe 1.2
snmp.exe 3.7
splwow64.exe 2.7
spoolsv.exe 8.5
SQLAGENT90.EXE 4.4
sqlbrowser.exe 1.9
sqlservr.exe 1,988.2
sqlwriter.exe 1.8
svchost.exe 9.9
svchost.exe 4.3
svchost.exe 2.6
svchost.exe 5.7
svchost.exe 0.7
svchost.exe 1.8
svchost.exe 0.4
svchost.exe 0.3
svchost.exe 5.6
svchost.exe 3.9
svchost.exe 9.4
svchost.exe 6.6
svchost.exe 45.5
svchost.exe 15.3
svchost.exe 7.8
System 0.1
System Idle Process 0.0
taskeng.exe 6.4
taskhost.exe 6.3
taskhost.exe 3.2
taskmgr.exe 10.9
UnivAgent.exe 8.9
vmtoolsd.exe 4.4
VMUpgradeHelper.exe 0.3
VMwareTray.exe 5.8
VMwareTray.exe 2.1
VSSVC.exe 2.1
w3wp.exe 224.7
w3wp.exe 23.9
wininit.exe 0.1
winlogon.exe 4.8
winlogon.exe 0.5
winlogon.exe 0.1
WmiApSrv.exe 1.3
WmiPrvSE.exe 13.3
WmiPrvSE.exe 5.7
wscript.exe 0.4
wscript.exe 0.4
wscript.exe 0.4
wscript.exe 0.6
wscript.exe 0.6
wscript.exe 0.6
wuauclt.exe 0.6[/font]
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply