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


Memory Leak SQL 2008 R2 64 bit?


Memory Leak SQL 2008 R2 64 bit?

Author
Message
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6841 Visits: 3501
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
MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5173 Visits: 15346
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224731 Visits: 46321
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, MVP, M.Sc (Comp Sci)
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


Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39692 Visits: 14412
@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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224731 Visits: 46321
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, MVP, M.Sc (Comp Sci)
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


Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39692 Visits: 14412
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
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6841 Visits: 3501
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

Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39692 Visits: 14412
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
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6841 Visits: 3501
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39692 Visits: 14412
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
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