September 10, 2012 at 6:46 am
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
September 10, 2012 at 8:40 am
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.
September 10, 2012 at 9:39 am
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
September 10, 2012 at 1:09 pm
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
September 10, 2012 at 1:21 pm
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
September 10, 2012 at 1:47 pm
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
September 10, 2012 at 10:36 pm
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
typeMem, Mb
CACHESTORE_SQLCP145.07
CACHESTORE_OBJCP22.57
MEMORYCLERK_SOSNODE18.99
CACHESTORE_PHDR12.68
OBJECTSTORE_SNI_PACKET10.42
MEMORYCLERK_SQLUTILITIES9.85
MEMORYCLERK_SQLSTORENG6.37
MEMORYCLERK_SQLGENERAL5.47
USERSTORE_DBMETADATA4.98
USERSTORE_SCHEMAMGR4.07
September 10, 2012 at 11:17 pm
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
September 10, 2012 at 11:37 pm
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
September 11, 2012 at 12:11 am
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
September 11, 2012 at 12:33 am
TotalServerMemoryMB - 3270.125000
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.
for this reason enable LPIM in x64 operating system.
SQL service accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. also memory may boost performance when paging memory to disk is expected.
September 11, 2012 at 1:23 am
ananda.murugesan (9/11/2012)
TotalServerMemoryMB - 3270.125000
Thank you. This confirms what was mentioned before. At present there are no indicators that anything out of the ordinary is occurring in terms of how much memory SQL Server is using on your server.
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.
for this reason enable LPIM in x64 operating system.
SQL service accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. also memory may boost performance when paging memory to disk is expected.
There is a great debate about this in the SQL Serevr community. Some prefer to enable lpim on x64 Windows Server 2008 R2 running SQL Server 2008 R2 by default, and others prefer (including myself) to leave lpim disabled unless the database server exhibits specific behavior that signals there may be a benefit to enabling lpim.
Here is a good primer on the debate and one of many articles and my own experience that helped shape my opinion: Great SQL Server Debates: Lock Pages in Memory (12 December 2011) by Jonathan Kehayias[/url]
When SQL Server approaches 7.5 GB do you start to see system instability or messages entering the SQL Server Error Log signifying the server is under memory pressure? If not then you should be happy with your server configuration. If so, please post those messages here.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 11, 2012 at 1:51 am
Thanks for reply
I do have a question though.
Server OS’s are x64, and even though I have set the value for LPIM with the service account, I have never seen a message in the SQL log stating “Using locked pages for buffer pool” on any of the servers. Did I miss something?
I have noticed before enable LPIM in this box, In Task manger showing sqlservr.exe consuming 6.4 GB constantly and exceeds max memory setting. periviously I have setting max memory 5 GB, but sql server is not using max level. after that I have enabled LPIM and setting max memory 6114 MB.
Performance Tap in task manager why 7.5 GB consuming physical memory using after restarted SQL services, it was totaly reduced and now comsuming 4.55 GB only...thats what I have mention first post memory leak sql 2k8 r2.
OS level (Win 2008 R2 64 bit)there is no service pack installed, It is RTM. Does SP is require to be installed on OS side?
thanks
September 11, 2012 at 2:48 am
ananda.murugesan (9/11/2012)
Performance Tap in task manager why 7.5 GB consuming physical memory using after restarted SQL services, it was totaly reduced and now comsuming 4.55 GB only...thats what I have mention first post memory leak sql 2k8 r2.
There is no memory leak.
SQL uses as much memory as it needs up to max server memory + a bit for the non-buffer memory. All that the 4.5 GB now tells us is that SQL has only needed 4.5 GB of memory since the restart. Everything you've shown so far shows a SQL Server that's working fine and as expected.
Again, don't use Task Manager to show SQL's memory usage, it shows incorrectly (fault of Task Manager, not SQL Server) if locked pages is enabled.
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
September 11, 2012 at 3:19 am
Thanks
one final clarifications, still I am not getting conclusion about LPIM setting.
1.SQL SERVER 2008 on top of Windows 2008 enterprise edition both x86 and x64
2.SQL SERVER 2008 R2 on top of Windows 2008 R2 enterprise edition both x86 and x64
Which & when condition needs to be enable LPIM for both environments?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply