ananda.murugesan (9/10/2012)

---

OS - Windows 2008 R2 64 bit (no service pack installed on operating system level)
SQL - SQL SERVER 2008 R2 64 bit (SP 2 installed)


One of the dedicated sql server 2008 R2 machine

Total Server memory - 8 GB
Max memory setting 6400 MB

Task manager allways showing - 124 MB by using sqlservr.exe

In task manager, performance tab,physical memory using 7.5 GB,

after restarted SQL SERVER services physical memory usages will be reduced and
currently using 3.5 GB in overall physical memory usages.
after 10 5 days again increased 7.5 GB in physical memory usages. (80 % used)

what could be issues in SQL server side?
also after connecting SQL SERVER Instance not showing Green color, but an instance & database are working fine.

thanks

Get-WMIObject Win32_Process | Select Name,@{Name="WorkingSetSize(MB)";Expression={"{0:N1}" -f($_.WorkingSetSize/1mb)}} | Sort-Object Name

opc.three (9/10/2012)

---

I think the OP is saying memory used by sqlservr.exe per Task Manager (count on Processes Tab should include buffer pool for x64 SQL) is only 124MB but Task Manager Performance Tab is reporting 7.5GB of the 8GB of physical memory is used. Further to that, the majority of the 7.5GB is unnaccounted for in the Processes Tab.

I have seen this scenario reported before and the OP never got to the bottom of it: http://www.sqlservercentral.com/Forums/Topic1267628-146-1.aspx

Name WorkingSetSize(MB)
---- ------------------
ccSvcHst.exe 4.5
ccSvcHst.exe 16.4
cissesrv.exe 1.8
cmd.exe 0.2
cmd.exe 0.2
cmd.exe 0.2
cmd.exe 0.2
cmd.exe 0.2
conhost.exe 0.3
conhost.exe 0.4
conhost.exe 0.4
conhost.exe 2.8
conhost.exe 4.4
conhost.exe 3.0
cpqnimgt.exe 4.8
cpqrcmc.exe 1.7
cpqteam.exe 3.8
cqmghost.exe 6.0
cqmgserv.exe 2.6
cqmgstor.exe 2.9
csrss.exe 5.8
csrss.exe 2.4
csrss.exe 0.2
dwm.exe 4.8
explorer.exe 45.1
fdhost.exe 5.6
fdlauncher.exe 3.9
hpsmhd.exe 8.6
hpsmhd.exe 0.4
hpwmistor.exe 1.1
LogonUI.exe 0.4
lsass.exe 14.4
lsm.exe 3.7
msdtc.exe 1.1
MsDtsSrvr.exe 2.9
powershell.exe 59.5
rdpclip.exe 7.0
rotatelogs.exe 0.2
rotatelogs.exe 0.4
rotatelogs.exe 0.4
rotatelogs.exe 0.2
services.exe 9.4
Smc.exe 8.0
smhstart.exe 1.2
smss.exe 0.5
snmp.exe 4.2
spoolsv.exe 8.3
SQLAGENT.EXE 19.9
sqlservr.exe 136.5
sqlwriter.exe 1.6
Ssms.exe 62.0
svchost.exe 6.3
svchost.exe 5.3
svchost.exe 1.4
svchost.exe 2.8
svchost.exe 45.6
svchost.exe 9.9
svchost.exe 2.3
svchost.exe 10.9
svchost.exe 1.9
svchost.exe 11.2
svchost.exe 5.9
svchost.exe 16.4
sysdown.exe 0.9
System 0.3
System Idle Process 0.0
taskhost.exe 6.0
vcagent.exe 5.3
wininit.exe 0.3
winlogon.exe 0.2
winlogon.exe 5.2
WmiPrvSE.exe 6.0
WmiPrvSE.exe 10.8
WmiPrvSE.exe 13.5
WmiPrvSE.exe 16.7
WmiPrvSE.exe 138.3

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 ;

type Mem, Mb

CACHESTORE_SQLCP 145.07
CACHESTORE_OBJCP 22.57
MEMORYCLERK_SOSNODE 18.99
CACHESTORE_PHDR 12.68
OBJECTSTORE_SNI_PACKET 10.42
MEMORYCLERK_SQLUTILITIES 9.85
MEMORYCLERK_SQLSTORENG 6.37
MEMORYCLERK_SQLGENERAL 5.47
USERSTORE_DBMETADATA 4.98
USERSTORE_SCHEMAMGR 4.07

PhysicalMemoryGB - 8.00

BufferPoolCommittedMemoryGB - 3.13

BufferPoolTargetMemoryGB - 5.97

MinServerMemoryGB - 0.00

MaxServerMemoryGB - 5.97

ananda.murugesan (9/10/2012)

---

Thanks for your valuable reply. I hope, i am going to correct path for fixing memory related issues.

As per memory overivew result in production server as below

For the Lock page memory enable
---------------------------------
Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems,

so as per server installed OS x64.