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 12»»

Memory Leak SQL 2008 R2 64 bit? Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 6:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
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
Post #1356701
Posted Monday, September 10, 2012 8:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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


Max server memory only applies to the SQL buffer pool. There are only parts of SQL server which will use an "undefined" amount of memory which will vary depending on the usage.
Post #1356788
Posted Monday, September 10, 2012 9:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
From what you describe, no issues, normal expected behaviour.

Haven't we had several memory-related discussions already on this?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1356838
Posted Monday, September 10, 2012 1:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
@GilaMonster, @MysteryJimbo,

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

@ananda.murugesan, did I describe the scenario you're reporting or am I missing something?

Either way, could you please run this from a PowerShell prompt on the affected server 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
Post #1356972
Posted Monday, September 10, 2012 1:21 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
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


He's running locked pages in memory and Task Manager does not report memory allocated using the physical alloc that SQL uses when locked pages is enabled.
Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/, page 148

Task manager should never be used to check SQL's memory because it only shows memory allocated via VirtualAlloc, not AllocateUserPhysicalPages and hence gives completely incorrect results when locked pages is enabled (or AWE on 32-bit)

The reliable way to check SQL's memory usage is with the TotalServerMemory perfmon counter.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1356979
Posted Monday, September 10, 2012 1:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
Of course! I did not even think to ask if lpim was enabled. Most people do not turn that on with x64 without a reason. That must be it.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1356989
Posted Monday, September 10, 2012 10:36 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
Thank you for replay

Yes, I have enabled Lock page Memory in Local policy setting.

Now Performance Tap showing 4.55 GB after SQL services restarted on yesterday..

Sqlservr.exe using 103 MB for current working set task.

Powershell Result
-----------------
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


Top 10 memory consumer

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 ;

result


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
Post #1357131
Posted Monday, September 10, 2012 11:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
Thanks for posting some results but now that we know you have lock pages in memory (lpim) enabled the process list is not going to tell us anything of much use. What was your reasoning behind enabling lpim by the way?

Also, what does this say?

-- memory overview
SELECT CAST(physical_memory_in_bytes / (1024.0 * 1024.0 * 1024.0) AS DECIMAL(20, 2)) AS PhysicalMemoryGB,
CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS BufferPoolCommittedMemoryGB,
CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS BufferPoolTargetMemoryGB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'min server memory (MB)'
) AS MinServerMemoryGB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
) AS MaxServerMemoryGB,
(
SELECT cntr_value / 1024.0
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)'
) AS TotalServerMemoryMB
FROM sys.dm_os_sys_info;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1357138
Posted Monday, September 10, 2012 11:37 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
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


PhysicalMemoryGB - 8.00

BufferPoolCommittedMemoryGB - 3.13

BufferPoolTargetMemoryGB - 5.97

MinServerMemoryGB - 0.00

MaxServerMemoryGB - 5.97


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.

Thnaks



Post #1357144
Posted Tuesday, September 11, 2012 12:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
ananda.murugesan (9/10/2012)
Thanks for your valuable reply. I hope, i am going to correct path for fixing memory related issues.

All we have established so far is that your SQL Server eventually uses all the memory you have allowed it to use per the configuration 'max server memory (MB)' which is by design. To which other 'memory related issues' are you referring?

As per memory overivew result in production server as below


PhysicalMemoryGB - 8.00

BufferPoolCommittedMemoryGB - 3.13

BufferPoolTargetMemoryGB - 5.97

MinServerMemoryGB - 0.00

MaxServerMemoryGB - 5.97

Thank you, but you forgot to post the value from the overview I was most interested in: TotalServerMemoryMB. Please post it.


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,

Yes, that is correct, lpim is off by default but you have it turned on and you still have not told us why you enabled it.

so as per server installed OS x64.

Sorry I did not understand this. What are you meaning to relay here?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1357150
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse