memory leak - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

memory leak

Author

Topic

crayon1234
Starting Member

3 Posts

Posted - 2012-09-10 : 23:19:27

Hi I am having trouble with a memory leak on a prod sql server. Over the period of a few weeks, the memory creeps up until the server becomes unresponsive and applications are unable to connect to the databases and we have to reboot the server. I dont beleive sql is necessarily the cause as I have capped the memory and the sql perfom counters look fine. But I cannot tell what is using the memory

Windows Server 2008 R2 Enterprise
Virtual
64-bit
2 vcpu 20GB Ram

SQL Server 2008 R2 RTM
Enterprise Edition

Max Memory 12288 (12GB)
Lock Pages in memory is switched on

Only SQL Server service and reporting services are running however there are a couple of small scheduled tasks that run executables, plus ingres drivers on this server. Also CLR is enabled in sql server.
Also this has symmantec as the antivirus and I notice that after a reboot of a server, the cpu for smc.exe and also for system process creeps up over time until the server needs a reboot (2-3 weeks) Not sure if this is a cause or a symptom though.
We have tried shutting down everything on the server except core OS services however even with all that shut down including sql, the server still holds a heap of memory as in use. Any ideas where I can find what's using the memory? Resource monitor does not indicate where the memory is in use

I know some of you will say to patch the OS and sql and yes that is an option we are exploring but it is a massive task and other similar servers are at the same level and are doing ok.

I think I have actually given SQL Server more memory than it needs as the page life expectancy and buffer cache hit ration look ridiculously healthy. Below is dbcc memorystatus output. I think the SQL Server is ok and not involved in the memory leak

Memory Manager KB
---------------------------------------- -----------
VM Reserved 21394016
VM Committed 324752
Locked Pages Allocated 12927616
Reserved Memory 1024
Reserved Memory In Use 0

(5 row(s) affected)

Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 21390368
VM Committed 321216
Locked Pages Allocated 12927616
MultiPage Allocator 213432
SinglePage Allocator 2420992

(5 row(s) affected)

Memory node Id = 64 KB
---------------------------------------- -----------
VM Reserved 2560
VM Committed 2504
Locked Pages Allocated 0
MultiPage Allocator 2416
SinglePage Allocator 2420992

(5 row(s) affected)

MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 6576
MultiPage Allocator 11656

(7 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 21020672
VM Committed 32768
Locked Pages Allocated 12885120
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 1592

(7 row(s) affected)

MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 808
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 29168
MultiPage Allocator 896

(7 row(s) affected)

MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- -----------
VM Reserved 240
VM Committed 240
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 536
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- -----------
VM Reserved 42496
VM Committed 0
Locked Pages Allocated 42496
SM Reserved 0
SM Committed 0
SinglePage Allocator 12200
MultiPage Allocator 36432

(7 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 93752
MultiPage Allocator 4392

(7 row(s) affected)

MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 280
MultiPage Allocator 544

(7 row(s) affected)

MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SNI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 544
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_SNI (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16

(7 row(s) affected)

MEMORYCLERK_SNI (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 544
MultiPage Allocator 32

(7 row(s) affected)

MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 192
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_BHF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 624
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- -----------
VM Reserved 4224
VM Committed 4224
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_HOST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 30472
MultiPage Allocator 15608

(7 row(s) affected)

MEMORYCLERK_SOSNODE (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 2336

(7 row(s) affected)

MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 30480
MultiPage Allocator 17944

(7 row(s) affected)

MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 48
SM Committed 48
SinglePage Allocator 0
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 0

(7 row(s) affected)

MEMORYCLERK_XE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 264
MultiPage Allocator 456

(7 row(s) affected)

CACHESTORE_OBJCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 215120
MultiPage Allocator 20352

(7 row(s) affected)

CACHESTORE_SQLCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1844536
MultiPage Allocator 73760

(7 row(s) affected)

CACHESTORE_PHDR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 56208
MultiPage Allocator 80

(7 row(s) affected)

CACHESTORE_XPROC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 72
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 128
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_NOTIF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_STACKFRAMES (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8

(7 row(s) affected)

CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16

(7 row(s) affected)

CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 152
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_EVENTS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8592
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 240
MultiPage Allocator 0

(7 row(s) affected)

CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 64
MultiPage Allocator 120

(7 row(s) affected)

USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 36648
MultiPage Allocator 1704

(7 row(s) affected)

USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 12040
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 11160
MultiPage Allocator 45320

(7 row(s) affected)

USERSTORE_OBJPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2624
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2784
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0

(7 row(s) affected)

USERSTORE_SXC (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2792
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 56
MultiPage Allocator 256

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 12160
MultiPage Allocator 56

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 56

(7 row(s) affected)

OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 12160
MultiPage Allocator 112

(7 row(s) affected)

OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 424
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- -----------
VM Reserved 32768
VM Committed 32768
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 75024
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- -----------
VM Reserved 32768
VM Committed 32768
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 75048
MultiPage Allocator 0

(7 row(s) affected)

OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 0) KB
------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0

(7 row(s) affected)

Buffer Pool Value
---------------------------------------- -----------
Committed 1572864
Target 1572864
Database 761773
Dirty 13243
In IO 0
Latched 0
Free 508467
Stolen 302624
Reserved 0
Visible 1572864
Stolen Potential 1191596
Limiting Factor 17
Last OOM Factor 0
Page Life Expectancy 38477

(14 row(s) affected)

Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 1735827456
Available Virtual Memory 8773822717952
Available Paging File 8068472832
Working Set 335884288
Percent of Committed Memory in WS 100
Page Faults 22911202
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0

(10 row(s) affected)

Procedure Cache Value
---------------------------------------- -----------
TotalProcs 17677
TotalPages 276266
InUsePages 3358

(3 row(s) affected)

Global Memory Objects Pages
---------------------------------------- -----------
Resource 862
Locks 9381
XDES 270
SETLS 2
SE Dataset Allocators 4
SubpDesc Allocators 2
SE SchemaManager 2120
SE Column Metadata Cache 2673
SQLCache 4858
Replication 2
ServerGlobal 50
XP Global 2
SortTables 3691

(13 row(s) affected)

Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 810483
Current Max 810483
Future Max 810483
Physical Max 1139283
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0

(11 row(s) affected)

Small Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 42657
Current Max 42657
Future Max 42657

(5 row(s) affected)

Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 1061212
Current Max 1061212
Future Max 1061212
Physical Max 1126087
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0

(11 row(s) affected)

Small Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 55853
Current Max 55853
Future Max 55853

(5 row(s) affected)

Optimization Queue (internal) Value
---------------------------------------- --------------------
Overall Memory 10328047616
Target Memory 7471472640
Last Notification 1
Timeout 6
Early Termination Factor 5

(5 row(s) affected)

Small Gateway (internal) Value
---------------------------------------- -----------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000

(6 row(s) affected)

Medium Gateway (internal) Value
---------------------------------------- -----------
Configured Units 2
Available Units 2
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1

(6 row(s) affected)

Big Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1

(6 row(s) affected)

Optimization Queue (default) Value
---------------------------------------- --------------------
Overall Memory 10328047616
Target Memory 7598825472
Last Notification 1
Timeout 6
Early Termination Factor 5

(5 row(s) affected)

Small Gateway (default) Value
---------------------------------------- -----------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000

(6 row(s) affected)

Medium Gateway (default) Value
---------------------------------------- -----------
Configured Units 2
Available Units 2
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1

(6 row(s) affected)

Big Gateway (default) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1

(6 row(s) affected)

Memory Pool Manager Pages
---------------------------------------- -----------
Reserved Current 0
Reserved Limit 1191596

(2 row(s) affected)

Memory Pool (internal) Pages
---------------------------------------- -----------
Allocations 5793
Predicted 5793
Private Target 0
Private Limit 0
Total Target 1494220
Total Limit 1494220
OOM Count 0

(7 row(s) affected)

MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- -----------
Allocations 3776
Rate 0
Target Allocations 913804
Future Allocations 0
Overall 1260748
Last Notification 1

(6 row(s) affected)

MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- -----------
Allocations 2017
Rate -1
Target Allocations 912045
Future Allocations 0
Overall 1260748
Last Notification 1

(6 row(s) affected)

MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 910028
Future Allocations 0
Overall 1260748
Last Notification 1

(6 row(s) affected)

Memory Pool (default) Pages
---------------------------------------- -----------
Allocations 296831
Predicted 578399
Private Target 0
Private Limit 0
Total Target 1494220
Total Limit 1494220
OOM Count 0

(7 row(s) affected)

MEMORYBROKER_FOR_CACHE (default) Pages
---------------------------------------- -----------
Allocations 279269
Rate 46
Target Allocations 1189343
Future Allocations 0
Overall 1260748
Last Notification 1

(6 row(s) affected)

MEMORYBROKER_FOR_STEAL (default) Pages
---------------------------------------- -----------
Allocations 17562
Rate 1
Target Allocations 927591
Future Allocations 0
Overall 1260748
Last Notification 1

(6 row(s) affected)

MEMORYBROKER_FOR_RESERVE (default) Pages
---------------------------------------- -----------
Allocations 0
Rate -1
Target Allocations 1191549
Future Allocations 281521
Overall 1260748
Last Notification 1

(6 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-09-11 : 09:24:57

If you think you're experiencing a memory leak you can check the "\Process(process_name)\Private Bytes" Perf counter - which rises steadily during a memory leak

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

crayon1234
Starting Member

3 Posts

Posted - 2012-09-11 : 23:20:23

Thanks for the quick reply. I have checked the private bytes in the past and there is nothing that is holding a large amount. The total does not get anywhere near to the amount of ram on the server even when adding in the amount of lock memory sql server has.

Also during a reboot of this server, I have tried first shutting down all the non core services. I can see sql drop down its lock memory once that service is shutdown. I then shutdown all non essential services however the server still indicates that 4-5GB of memory is in use but nothing in private bytes indicate that. Its like the memory does not get released by the system somehow.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-09-12 : 01:48:29

are you getting any other messages , such as Low virtual memory etc? how long does it take for the problem to appear , after you've rebooted?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

crayon1234
Starting Member

3 Posts

Posted - 2012-09-12 : 20:09:39

Given the server 20GB ram. Restricted sql to 12GB. The memory takes about 3 weeks to consume the entire 20GB and at that time, people cannot log in to the server, sql connections cant be made. I have left my account logged in and I try to launch things liek notepad.exe and get not enough resources error message

I have put perfmon counters on. The sql memory looks fine and probably has more assigned than it needs. The avail memory counter you can see it steadily decrease over the 3 weeks. The cpu for system and smc.exe steadily rise over the 3 weeks. Not sure if that is just a symptom though. Nothing in the private bytes indicates its holding the memory.

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-13 : 02:27:25

Are you logging private bytes or Working Set for all processes? You should see a steady increase in these counters for the process that is leaking memory.

-Chad

nilesh29
Starting Member

1 Post

Posted - 2015-02-05 : 06:33:33

Did you guys got it resolved? I am in same issue right now.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-02-09 : 01:40:05

nilesh29, can you be more specific? Are there any particular big memory consumers ? Is it a Physical or VM? You'll need to post some extra information

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources