SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 memory leak
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

crayon1234
Starting Member

3 Posts

Posted - 09/10/2012 :  23:19:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2058 Posts

Posted - 09/11/2012 :  09:24:57  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

crayon1234
Starting Member

3 Posts

Posted - 09/11/2012 :  23:20:23  Show Profile  Reply with Quote
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.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2058 Posts

Posted - 09/12/2012 :  01:48:29  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

crayon1234
Starting Member

3 Posts

Posted - 09/12/2012 :  20:09:39  Show Profile  Reply with Quote
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.
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 09/13/2012 :  02:27:25  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 2.23 seconds. Powered By: Snitz Forums 2000