Sql-server – Query Plans not retained insufficient memory errors

execution-planmemorysql-server-2005

We have been experiencing memory issues with SQL Server.

We first realised we had a problem when we started getting timeouts and login errors:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)

Looking into event viewer on our sqlbox, we noticed a multitude of insufficient memory errors:

There is insufficient system memory to run this query.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

The only immediate warning prior to this was the following message:

AppDomain 119 (Alerts.dbo[runtime].118) unloaded.

About twenty minutes prior to this, we had a number of perf related messages and errors:

info:

The Microsoft Operations Manager Agent on this computer received new rules and configuration settings from its MOM Server.
Management Group: GGC

warning:

The configuration information of the performance library "C:\WINDOWS\system32\aspperf.dll" for the "ASP" service does not match the trusted performance library information stored in the registry. The functions in this library will not be treated as trusted.

error:

The Microsoft Operations Manager performance provider could not access performance counters on computer blah-blah-blah. Microsoft Operations Manager will not monitor performance counters on this computer until they become available.

info:

The Microsoft Operations Manager successfully loaded performance counters on computer blah-blah-blah after previous failure(s) and will start monitoring them.

I doubt the above perf alerts/errors had anything to do with the two hours of "insufficient memory exceptions, but I have included the messages just in case.

Finally, after two hours of red memory errors, the following info message heralded the end of the insufficient memory alerts:

SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

So freeprocache was called by our DBA at some point. Despite eventually fixing the insufficient memory exceptions, we noticed that our execution plans were still not being stored. This 'issue' has now continued for 3 whole days, meaning that apps using queries with complex plans are facing sever performance difficulties. There are points where the plans start to get taken again, but they don't ever tend to stay in the cache for long.

I'm wondering if anyone could help with pinpointing the area of concern.

Part A represents the system when the query plans are being kept (plans being retained, but only for an hour or so), and Part B represents when the plans are not being cached at all (checking dm_exec_query_stats)

PART A

DBCC MemoryStatus results:

Memory Manager   KB 
VM Reserved 1828768
VM Committed    269928
AWE Allocated   13762560
Reserved Memory 1024
Reserved Memory In Use  0

Memory node Id = 0   KB 
VM Reserved 1824608
VM Committed    265920
AWE Allocated   13762560
MultiPage Allocator 50776
SinglePage Allocator    656568

 MEMORYCLERK_SQLGENERAL (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   14672
 MultiPage Allocator    11144

MEMORYCLERK_SQLBUFFERPOOL (Total)    KB 
 VM Reserved    1620024
 VM Committed   137272
 AWE Allocated  13762560
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   0
 MultiPage Allocator    3624

MEMORYCLERK_SQLQUERYEXEC (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   80
 MultiPage Allocator    32

MEMORYCLERK_SQLOPTIMIZER (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   5568
 MultiPage Allocator    88

MEMORYCLERK_SQLUTILITIES (Total)     KB 
 VM Reserved    360
 VM Committed   360
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   664
 MultiPage Allocator    0

MEMORYCLERK_SQLSTORENG (Total)   KB 
 VM Reserved    17792
 VM Committed   17792
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   11200
 MultiPage Allocator    13040

MEMORYCLERK_SQLCONNECTIONPOOL (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   4504
 MultiPage Allocator    0

MEMORYCLERK_SQLCLR (Total)   KB 
 VM Reserved    126336
 VM Committed   54816
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1296
 MultiPage Allocator    2872

MEMORYCLERK_SQLSERVICEBROKER (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   176
 MultiPage Allocator    192

MEMORYCLERK_SQLHTTP (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

MEMORYCLERK_SNI (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   432
 MultiPage Allocator    16

MEMORYCLERK_FULLTEXT (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

MEMORYCLERK_SQLXP (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

MEMORYCLERK_BHF (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   720
 MultiPage Allocator    0

MEMORYCLERK_SQLQERESERVATIONS (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   37896
 MultiPage Allocator    0

MEMORYCLERK_HOST (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   224
 MultiPage Allocator    96

MEMORYCLERK_SOSNODE (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16008
 MultiPage Allocator    9136

CACHESTORE_OBJCP (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   297080
 MultiPage Allocator    4448

CACHESTORE_PHDR (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16400
 MultiPage Allocator    0

CACHESTORE_XPROC (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   112
 MultiPage Allocator    0

CACHESTORE_TEMPTABLES (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   48
 MultiPage Allocator    0

CACHESTORE_NOTIF (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_VIEWDEFINITIONS (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_XMLDBTYPE (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_XMLDBELEMENT (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_XMLDBATTRIBUTE (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_STACKFRAMES (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   0
 MultiPage Allocator    8

CACHESTORE_BROKERTBLACS (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   296
 MultiPage Allocator    0

CACHESTORE_BROKERKEK (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERDSH (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERUSERCERTLOOKUP (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERRSB (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERREADONLY (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   32
 MultiPage Allocator    0

CACHESTORE_BROKERTO (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_EVENTS (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_SYSTEMROWSET (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   3104
 MultiPage Allocator    0

USERSTORE_SCHEMAMGR (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   9592
 MultiPage Allocator    144

USERSTORE_DBMETADATA (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   6800
 MultiPage Allocator    0

USERSTORE_TOKENPERM (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   752
 MultiPage Allocator    0

USERSTORE_OBJPERM (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   6072
 MultiPage Allocator    0

USERSTORE_SXC (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   2232
 MultiPage Allocator    0

OBJECTSTORE_LBSS (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   472
 MultiPage Allocator    0

OBJECTSTORE_SNI_PACKET (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   7640
 MultiPage Allocator    48

OBJECTSTORE_SERVICE_BROKER (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   256
 MultiPage Allocator    0

OBJECTSTORE_LOCK_MANAGER (Total)     KB 
 VM Reserved    4096
 VM Committed   4096
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   2584
 MultiPage Allocator    0

Buffer Distribution Buffers
Stolen  11432
Free    283
Cached  75066
Database (clean)    1609444
Database (dirty)    24091
I/O 0
Latched 4

Buffer Counts   Buffers
Committed   1720320
Target  1720320
Hashed  1633540
Stolen Potential    89382
External Reservation    308
Min Free    712
Visible 185344
Available Paging File   2268071

Procedure Cache Value
TotalProcs  6240
TotalPages  71392
InUsePages  407

Global Memory Objects   Buffers
Resource    1127
Locks   326
XDES    204
SETLS   8
SE Dataset Allocators   16
SubpDesc Allocators 8
SE SchemaManager    1194
SQLCache    749
Replication 126
ServerGlobal    27
XP Global   2
SortTables  1523

Query Memory Objects    Value
Grants  2
Waiting 0
Available (Buffers) 78932
Maximum (Buffers)   83833
Limit   83833
Next Request    0
Waiting For 0
Cost    0
Timeout 0
Wait Time   0
Last Target 88245

Small Query Memory Objects  Value
Grants  0
Waiting 0
Available (Buffers) 4410
Maximum (Buffers)   4410
Limit   4410

Optimization Queue  Value
Overall Memory  1216954368
Target Memory   556490752
Last Notification   1
Timeout 6
Early Termination Factor    5

Small Gateway   Value
Configured Units    32
Available Units 32
Acquires    0
Waiters 0
Threshold Factor    250000
Threshold   250000

Medium Gateway  Value
Configured Units    8
Available Units 8
Acquires    0
Waiters 0
Threshold Factor    12

Big Gateway Value
Configured Units    1
Available Units 1
Acquires    0
Waiters 0
Threshold Factor    8

MEMORYBROKER_FOR_CACHE  Value
Allocations 75056
Rate    15
Target Allocations  136125
Future Allocations  0
Last Notification   1

MEMORYBROKER_FOR_STEAL  Value
Allocations 6869
Rate    8
Target Allocations  67931
Future Allocations  0
Last Notification   1

MEMORYBROKER_FOR_RESERVE    Value
Allocations 4737
Rate    1844
Target Allocations  94128
Future Allocations  33074
Last Notification   1

The available memory and largest free contiguous block:

Total avail Mem, KB   Max free size, KB
17828                  4148

PART B:

DBCC MemoryStatus

Memory Manager   KB 
VM Reserved 1823056
VM Committed    264192
AWE Allocated   13762560
Reserved Memory 1024
Reserved Memory In Use  0

Memory node Id = 0   KB 
VM Reserved 1818896
VM Committed    260184
AWE Allocated   13762560
MultiPage Allocator 41672
SinglePage Allocator    90880

MEMORYCLERK_SQLGENERAL (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   9456
 MultiPage Allocator    12416

MEMORYCLERK_SQLBUFFERPOOL (Total)    KB 
 VM Reserved    1620024
 VM Committed   137272
 AWE Allocated  13762560
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   0
 MultiPage Allocator    3624

MEMORYCLERK_SQLQUERYEXEC (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   88
 MultiPage Allocator    0

MEMORYCLERK_SQLOPTIMIZER (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   11344
 MultiPage Allocator    88

MEMORYCLERK_SQLUTILITIES (Total)     KB 
 VM Reserved    600
 VM Committed   600
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   672
 MultiPage Allocator    0

MEMORYCLERK_SQLSTORENG (Total)   KB 
 VM Reserved    17856
 VM Committed   17856
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   11408
 MultiPage Allocator    13040

MEMORYCLERK_SQLCONNECTIONPOOL (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   2648
 MultiPage Allocator    0

MEMORYCLERK_SQLCLR (Total)   KB 
 VM Reserved    126336
 VM Committed   54816
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1296
 MultiPage Allocator    2872

MEMORYCLERK_SQLSERVICEBROKER (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   184
 MultiPage Allocator    192

MEMORYCLERK_SQLHTTP (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

MEMORYCLERK_SNI (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   488
 MultiPage Allocator    16

MEMORYCLERK_FULLTEXT (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

MEMORYCLERK_SQLXP (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

MEMORYCLERK_QSRANGEPREFETCH (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   392
 MultiPage Allocator    0

MEMORYCLERK_BHF (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   456
 MultiPage Allocator    0

MEMORYCLERK_SQLQERESERVATIONS (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   21512
 MultiPage Allocator    0

MEMORYCLERK_HOST (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   224
 MultiPage Allocator    96

MEMORYCLERK_SOSNODE (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   14904
 MultiPage Allocator    8992

CACHESTORE_OBJCP (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   3816
 MultiPage Allocator    0

CACHESTORE_SQLCP (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   328
 MultiPage Allocator    0

CACHESTORE_PHDR (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1760
 MultiPage Allocator    0

CACHESTORE_XPROC (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_TEMPTABLES (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_NOTIF (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_VIEWDEFINITIONS (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_XMLDBTYPE (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_XMLDBELEMENT (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_XMLDBATTRIBUTE (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_STACKFRAMES (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   0
 MultiPage Allocator    8

CACHESTORE_BROKERTBLACS (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   304
 MultiPage Allocator    0

CACHESTORE_BROKERKEK (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERDSH (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERUSERCERTLOOKUP (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERRSB (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_BROKERREADONLY (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   32
 MultiPage Allocator    0

CACHESTORE_BROKERTO (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   8
 MultiPage Allocator    0

CACHESTORE_EVENTS (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   16
 MultiPage Allocator    0

CACHESTORE_SYSTEMROWSET (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   744
 MultiPage Allocator    0

USERSTORE_SCHEMAMGR (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   9560
 MultiPage Allocator    144

USERSTORE_DBMETADATA (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   2960
 MultiPage Allocator    0

USERSTORE_TOKENPERM (Total)  KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1064
 MultiPage Allocator    0

USERSTORE_OBJPERM (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   6144
 MultiPage Allocator    0

USERSTORE_SXC (Total)    KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   88
 MultiPage Allocator    0

OBJECTSTORE_LBSS (Total)     KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   160
 MultiPage Allocator    0

OBJECTSTORE_SNI_PACKET (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   7464
 MultiPage Allocator    48

OBJECTSTORE_SERVICE_BROKER (Total)   KB 
 VM Reserved    0
 VM Committed   0
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   256
 MultiPage Allocator    0

OBJECTSTORE_LOCK_MANAGER (Total)     KB 
 VM Reserved    4096
 VM Committed   4096
 AWE Allocated  0
 SM Reserved    0
 SM Commited    0
 SinglePage Allocator   1680
 MultiPage Allocator    0

Buffer Distribution Buffers
Stolen  8130
Free    3291
Cached  4433
Database (clean)    1685517
Database (dirty)    18929
I/O 4
Latched 16

Buffer Counts   Buffers
Committed   1720320
Target  1720320
Hashed  1704528
Stolen Potential    162293
External Reservation    1538
Min Free    1024
Visible 185344
Available Paging File   2256907

Procedure Cache Value
TotalProcs  11
TotalPages  813
InUsePages  137

Global Memory Objects   Buffers
Resource    1141
Locks   213
XDES    225
SETLS   8
SE Dataset Allocators   16
SubpDesc Allocators 8
SE SchemaManager    1190
SQLCache    80
Replication 126
ServerGlobal    27
XP Global   2
SortTables  1523

Query Memory Objects    Value
Grants  1
Waiting 0
Available (Buffers) 129608
Maximum (Buffers)   132297
Limit   132297
Next Request    0
Waiting For 0
Cost    0
Timeout 0
Wait Time   0
Last Target 139260

Small Query Memory Objects  Value
Grants  0
Waiting 0
Available (Buffers) 6963
Maximum (Buffers)   6963
Limit   6963

Optimization Queue  Value
Overall Memory  1216954368
Target Memory   1142743040
Last Notification   1
Timeout 6
Early Termination Factor    5

Small Gateway   Value
Configured Units    32
Available Units 28
Acquires    4
Waiters 0
Threshold Factor    250000
Threshold   250000

Medium Gateway  Value
Configured Units    8
Available Units 8
Acquires    0
Waiters 0
Threshold Factor    12
Threshold   23807146

Big Gateway Value
Configured Units    1
Available Units 1
Acquires    0
Waiters 0
Threshold Factor    8

MEMORYBROKER_FOR_CACHE  Value
Allocations 4347
Rate    -840
Target Allocations  136775
Future Allocations  0
Last Notification   1

MEMORYBROKER_FOR_STEAL  Value
Allocations 6929
Rate    -702
Target Allocations  139495
Future Allocations  0
Last Notification   1

MEMORYBROKER_FOR_RESERVE    Value
Allocations 2689
Rate    -60
Target Allocations  148554
Future Allocations  33074
Last Notification   1

Memory left:

Total avail Mem, KB Max free size, KB
18228          2580

PART A and PART B are both taken at points where memory is low. The difference is that with B, it seems that the query plans are not being retained for any period of time at all (where in A they are being held for an hour or so).

I'm hoping that someone can look at the memory statuses and possibly point me in the direction of where the problem resides.

Also, we are on SQL Server 2005 Server Pack 3.

UPDATE

OK, I was looking at the MemoryStatus above, and noticed that the object cache store was at 297MB in PART A. Is this high for running on 32 bit? Will this not consume the majority of VAS?

I wanted to take a look at this in a bit more detail:

So, I've been running this query:

--Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP)
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS [Plan Cache Size(GB)]
FROM sys.dm_os_memory_cache_counters 
WHERE 
type = 'CACHESTORE_SQLCP'
OR 
type = 'CACHESTORE_OBJCP'

It seems that this is cycling every two minutes or so, with the Stores being flushed every tim it stats to rise above 200MB. The majority of this (as in 180MB) is in CACHESTORE_OBJCP.

Would I be right in thinking I can use the following query to then analyse the object cache?

select  TOP 8000
 objtype,
    usecounts, 
 p.size_in_bytes/1024 'IN KB',
 LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY 'IN KB' DESC

The above query, taken at around about the highest point in CACHE MB, returns around 1500 objects. More or Less, the top 25 in terms of size seem to be triggers, with the sum size 65MB.
Is this normal? Am I barking up the wrong tree?

Best Answer

I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted.

This query from Jonathan Kehayias here will give you an idea of what is using memory in the memtoleave area:

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks 
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

The post also explains memtoleave well.

Whether the size of the caches you asked about is a problem depends on a number of things. Since this is a 32-bit instance, one of the more relevant things to consider is whether the allocations for those caches are allocated using the single-page allocator, which uses the buffer pool, or the multi-page allocator, which prior to SQL Server 2012 doesn't use the buffer pool and instead consumes memory in the memtoleave area. You have about 13 GB allocated to the buffer pool, so a few hundred MB allocated to this or that isn't necessarily going to cause a problem. However, a few hundred MB in the memtoleave area is enough to cause some of the errors you listed.

Kehayias' post explains these concepts as well as your options for solving the problem if it is indeed memtoleave exhaustion.

I would focus mainly on what is starving the memtoleave area, as well as what overall memory availability in Windows looks like, along with general measures of memory availability in SQL Server such as page life expectancy.

I would also consider whether the problem helps make a business case for migrating to a 64-bit version of SQL Server, as this avoids memtoleave issues unless the whole server is simply running out of memory. Understanding and managing SQL Server's memory usage is significantly simpler on 64-bit builds, especially on SQL Server 2012 where all allocations go through the buffer pool. You may also want to consider applying SP4 as there are a number of known bugs in SP3.

Related Question