From: Uri Dimant on
Hi
What does the below return
SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name = 'Total Server Memory (KB)'

OR counter_name = 'Target Server Memory (KB)';

SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name = 'Page life expectancy'

AND object_name = 'SQLServer:Buffer Manager';

--sql server uses the memory

select

CONVERT(VARCHAR,CAST(bpool_committed *8 AS MONEY),1)AS [SIZE],

bpool_committed,bpool_commit_target

from

sys.dm_os_sys_info



Tony has geat articke for the subject , it looks like your query are not
cached

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07/07/procedure-cache-tuning-sizing-from-1gbyte-to-768kbytes-increase-the-size-of-usable-data-cache.aspx





"M Bourgon" <bourgon(a)gmail.com> wrote in message
news:4cc52204-812e-45af-8c88-4617ab3fee59(a)v14g2000yqb.googlegroups.com...
> Recently (and, yes, finally) we upgraded our cluster from SP2 to SP3.
> In the past week we've had to restart the node twice due to memory
> conditions. Before I call MS, I'd like to see if I can figure out
> what's happening.
>
> SQL Server 2005, SP2, Active/Active cluster (the other active isn't
> seeing anything like this, but it has different traffic), 32gb of RAM,
> -g384, Max memory 29696, AWE on.
>
> We ran 2 queries when we saw the memory conditions: one to get VAS
> size, and DBCC MEMORYSTATUS (full results are posted below)
>
> At its worst (aka before we restarted and were getting tons of
> "insufficient memory" messages), it was down to 628k available memory,
> and 84k max contiguous, according to this query:
>
> WITH VAS_Summary
> AS (
> SELECT
> Size = VAS_Dump.Size,
> Reserved =
> SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0)
> WHEN 0 THEN 0
> ELSE 1
> END),
> Free =
> SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0)
> WHEN 0 THEN 1
> ELSE 0
> END)
> FROM
> (
> SELECT
> CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],
> region_allocation_base_address [Base]
> FROM sys.dm_os_virtual_address_dump
> WHERE region_allocation_base_address <> 0
> GROUP BY region_allocation_base_address
> UNION
> SELECT
> CONVERT(VARBINARY, region_size_in_bytes) [Size],
> region_allocation_base_address [Base]
> FROM sys.dm_os_virtual_address_dump
> WHERE region_allocation_base_address = 0x0
> ) AS VAS_Dump
> GROUP BY Size
> )
> SELECT
> SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB],
> CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB]
> FROM VAS_Summary
> WHERE Free <> 0
>
>
> Here are the results from the MEMORYSTATUS. I'm unsure at which point
> this one was taken, but it was during the time we were very low on
> memory.
>
> Would it help if I posted a post-restart DBCC?
>
> Memory Manager KB
> ------------------------------ --------------------
> VM Reserved 1781136
> VM Committed 428224
> AWE Allocated 27590656
> Reserved Memory 1024
> Reserved Memory In Use 0
>
> (5 row(s) affected)
>
> Memory node Id = 0 KB
> ------------------------------ --------------------
> VM Reserved 1776976
> VM Committed 424216
> AWE Allocated 27590656
> MultiPage Allocator 97560
> SinglePage Allocator 82320
>
> (5 row(s) affected)
>
> MEMORYCLERK_SQLGENERAL (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 12048
> MultiPage Allocator 68016
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLBUFFERPOOL (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved
> 1498456
> VM Committed
> 273752
> AWE Allocated
> 27590656
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 0
> MultiPage Allocator 7000
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLQUERYEXEC (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 24
> MultiPage Allocator 32
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLOPTIMIZER (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 1592
> MultiPage Allocator 88
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLUTILITIES (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 1080
> VM Committed 1080
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 264
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLSTORENG (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 12672
> VM Committed 12672
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 5048
> MultiPage Allocator 12384
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 3096
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLCLR (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved
> 152000
> VM Committed 31824
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 1488
> MultiPage Allocator 1208
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLSERVICEBROKER (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 168
> MultiPage Allocator 208
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> MEMORYCLERK_SNI (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 296
> MultiPage Allocator 16
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> MEMORYCLERK_BHF (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 9528
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLQERESERVATIONS (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 14144
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> MEMORYCLERK_HOST (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 128
> MultiPage Allocator 144
>
> (7 row(s) affected)
>
> MEMORYCLERK_SOSNODE (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 10728
> MultiPage Allocator 8272
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 24
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_OBJCP (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 16528
> MultiPage Allocator 16
>
> (7 row(s) affected)
>
> CACHESTORE_SQLCP (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 1016
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_PHDR (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 8
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_XPROC (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 24
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_TEMPTABLES (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 64
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> CACHESTORE_BROKERTBLACS (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 208
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> CACHESTORE_CLRPROC (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 8
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_SYSTEMROWSET (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 288
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_SCHEMAMGR (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 5800
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_DBMETADATA (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 576
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_TOKENPERM (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 1656
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_OBJPERM (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 2168
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_SXC (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 600
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> OBJECTSTORE_SNI_PACKET (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 5400
> MultiPage Allocator 48
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> OBJECTSTORE_LOCK_MANAGER (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 4096
> VM Committed 4096
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 2904
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> Buffer Distribution Buffers
> ------------------------------ -----------
> Stolen 6652
> Free 8438
> Cached 4719
> Database (clean) 3168875
> Database (dirty) 260036
> I/O 83
> Latched 29
>
> (7 row(s) affected)
>
> Buffer Counts Buffers
> ------------------------------ --------------------
> Committed 3448832
> Target 3448832
> Hashed 3428864
> Stolen Potential 132339
> External Reservation 560
> Min Free 1024
> Visible 153088
> Available Paging File 792364
>
> (8 row(s) affected)
>
> Procedure Cache Value
> ------------------------------ -----------
> TotalProcs 48
> TotalPages 2206
> InUsePages 1086
>
> (3 row(s) affected)
>
>
> Global Memory Objects Buffers
> ------------------------------ --------------------
> Resource 613
> Locks 366
> XDES 212
> SETLS 8
> SE Dataset Allocators 16
> SubpDesc Allocators 8
> SE SchemaManager 724
> SQLCache 62
> Replication 9
> ServerGlobal 29
> XP Global 2
> SortTables 1523
>
> (12 row(s) affected)
>
>
> Query Memory Objects Value
> ------------------------------ -----------
> Grants 5
> Waiting 0
> Available (Buffers) 107501
> Maximum (Buffers) 109269
> Limit 109269
> Next Request 0
> Waiting For 0
> Cost 0
> Timeout 0
> Wait Time 0
> Last Target 115020
>
> (11 row(s) affected)
>
> Small Query Memory Objects Value
> ------------------------------ -----------
> Grants 0
> Waiting 0
> Available (Buffers) 5751
> Maximum (Buffers) 5751
> Limit 5751
>
> (5 row(s) affected)
>
> Optimization Queue Value
> ------------------------------ --------------------
> Overall Memory 1005060096
> Target Memory 967606272
> Last Notification 1
> Timeout 6
> Early Termination Factor 5
>
> (5 row(s) affected)
>
> Small Gateway Value
> ------------------------------ --------------------
> Configured Units 32
> Available Units 32
> Acquires 0
> Waiters 0
> Threshold Factor 250000
> Threshold 250000
>
> (6 row(s) affected)
>
> Medium Gateway Value
> ------------------------------ --------------------
> Configured Units 8
> Available Units 8
> Acquires 0
> Waiters 0
> Threshold Factor 12
>
> (5 row(s) affected)
>
> Big Gateway Value
> ------------------------------ --------------------
> Configured Units 1
> Available Units 1
> Acquires 0
> Waiters 0
> Threshold Factor 8
>
> (5 row(s) affected)
>
> MEMORYBROKER_FOR_CACHE Value
> -------------------------------- --------------------
> Allocations 5627
> Rate -33767
> Target Allocations 116843
> Future Allocations 0
> Last Notification 1
>
> (5 row(s) affected)
>
> MEMORYBROKER_FOR_STEAL Value
> -------------------------------- --------------------
> Allocations 5638
> Rate -4365
> Target Allocations 118116
> Future Allocations 0
> Last Notification 1
>
> (5 row(s) affected)
>
> MEMORYBROKER_FOR_RESERVE Value
> -------------------------------- --------------------
> Allocations 1998
> Rate -4795
> Target Allocations 122688
> Future Allocations 27317
> Last Notification 1
>
> (5 row(s) affected)
>
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>


From: TheSQLGuru on
1) your max memory is way too high. start with 27GB and go down from there
if you have problems.

2) what were the EXACT messages you were getting?

3) 32bit or 64 bit? Anything else running on the box?

4) troubleshooting memory issues is a VERY deep and complex subject. I
recommend you get a performance tuning pro on board to assist you.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"M Bourgon" <bourgon(a)gmail.com> wrote in message
news:4cc52204-812e-45af-8c88-4617ab3fee59(a)v14g2000yqb.googlegroups.com...
> Recently (and, yes, finally) we upgraded our cluster from SP2 to SP3.
> In the past week we've had to restart the node twice due to memory
> conditions. Before I call MS, I'd like to see if I can figure out
> what's happening.
>
> SQL Server 2005, SP2, Active/Active cluster (the other active isn't
> seeing anything like this, but it has different traffic), 32gb of RAM,
> -g384, Max memory 29696, AWE on.
>
> We ran 2 queries when we saw the memory conditions: one to get VAS
> size, and DBCC MEMORYSTATUS (full results are posted below)
>
> At its worst (aka before we restarted and were getting tons of
> "insufficient memory" messages), it was down to 628k available memory,
> and 84k max contiguous, according to this query:
>
> WITH VAS_Summary
> AS (
> SELECT
> Size = VAS_Dump.Size,
> Reserved =
> SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0)
> WHEN 0 THEN 0
> ELSE 1
> END),
> Free =
> SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0)
> WHEN 0 THEN 1
> ELSE 0
> END)
> FROM
> (
> SELECT
> CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],
> region_allocation_base_address [Base]
> FROM sys.dm_os_virtual_address_dump
> WHERE region_allocation_base_address <> 0
> GROUP BY region_allocation_base_address
> UNION
> SELECT
> CONVERT(VARBINARY, region_size_in_bytes) [Size],
> region_allocation_base_address [Base]
> FROM sys.dm_os_virtual_address_dump
> WHERE region_allocation_base_address = 0x0
> ) AS VAS_Dump
> GROUP BY Size
> )
> SELECT
> SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB],
> CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB]
> FROM VAS_Summary
> WHERE Free <> 0
>
>
> Here are the results from the MEMORYSTATUS. I'm unsure at which point
> this one was taken, but it was during the time we were very low on
> memory.
>
> Would it help if I posted a post-restart DBCC?
>
> Memory Manager KB
> ------------------------------ --------------------
> VM Reserved 1781136
> VM Committed 428224
> AWE Allocated 27590656
> Reserved Memory 1024
> Reserved Memory In Use 0
>
> (5 row(s) affected)
>
> Memory node Id = 0 KB
> ------------------------------ --------------------
> VM Reserved 1776976
> VM Committed 424216
> AWE Allocated 27590656
> MultiPage Allocator 97560
> SinglePage Allocator 82320
>
> (5 row(s) affected)
>
> MEMORYCLERK_SQLGENERAL (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 12048
> MultiPage Allocator 68016
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLBUFFERPOOL (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved
> 1498456
> VM Committed
> 273752
> AWE Allocated
> 27590656
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 0
> MultiPage Allocator 7000
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLQUERYEXEC (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 24
> MultiPage Allocator 32
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLOPTIMIZER (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 1592
> MultiPage Allocator 88
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLUTILITIES (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 1080
> VM Committed 1080
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 264
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLSTORENG (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 12672
> VM Committed 12672
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 5048
> MultiPage Allocator 12384
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 3096
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLCLR (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved
> 152000
> VM Committed 31824
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 1488
> MultiPage Allocator 1208
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLSERVICEBROKER (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 168
> MultiPage Allocator 208
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> MEMORYCLERK_SNI (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 296
> MultiPage Allocator 16
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> MEMORYCLERK_BHF (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 9528
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLQERESERVATIONS (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 14144
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> MEMORYCLERK_HOST (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 128
> MultiPage Allocator 144
>
> (7 row(s) affected)
>
> MEMORYCLERK_SOSNODE (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 10728
> MultiPage Allocator 8272
>
> (7 row(s) affected)
>
> MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 24
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_OBJCP (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 16528
> MultiPage Allocator 16
>
> (7 row(s) affected)
>
> CACHESTORE_SQLCP (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 1016
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_PHDR (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 8
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_XPROC (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 24
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_TEMPTABLES (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 64
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> CACHESTORE_BROKERTBLACS (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 208
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> CACHESTORE_CLRPROC (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 8
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> CACHESTORE_SYSTEMROWSET (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 288
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_SCHEMAMGR (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 5800
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_DBMETADATA (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 576
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_TOKENPERM (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 1656
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_OBJPERM (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 2168
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> USERSTORE_SXC (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 600
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> OBJECTSTORE_SNI_PACKET (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 0
> VM Committed 0
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 5400
> MultiPage Allocator 48
>
> (7 row(s) affected)
>
> 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
>
> (7 row(s) affected)
>
> OBJECTSTORE_LOCK_MANAGER (Total) KB
> ----------------------------------------------------------------
> --------------------
> VM Reserved 4096
> VM Committed 4096
> AWE Allocated 0
> SM Reserved 0
> SM Commited 0
> SinglePage Allocator 2904
> MultiPage Allocator 0
>
> (7 row(s) affected)
>
> Buffer Distribution Buffers
> ------------------------------ -----------
> Stolen 6652
> Free 8438
> Cached 4719
> Database (clean) 3168875
> Database (dirty) 260036
> I/O 83
> Latched 29
>
> (7 row(s) affected)
>
> Buffer Counts Buffers
> ------------------------------ --------------------
> Committed 3448832
> Target 3448832
> Hashed 3428864
> Stolen Potential 132339
> External Reservation 560
> Min Free 1024
> Visible 153088
> Available Paging File 792364
>
> (8 row(s) affected)
>
> Procedure Cache Value
> ------------------------------ -----------
> TotalProcs 48
> TotalPages 2206
> InUsePages 1086
>
> (3 row(s) affected)
>
>
> Global Memory Objects Buffers
> ------------------------------ --------------------
> Resource 613
> Locks 366
> XDES 212
> SETLS 8
> SE Dataset Allocators 16
> SubpDesc Allocators 8
> SE SchemaManager 724
> SQLCache 62
> Replication 9
> ServerGlobal 29
> XP Global 2
> SortTables 1523
>
> (12 row(s) affected)
>
>
> Query Memory Objects Value
> ------------------------------ -----------
> Grants 5
> Waiting 0
> Available (Buffers) 107501
> Maximum (Buffers) 109269
> Limit 109269
> Next Request 0
> Waiting For 0
> Cost 0
> Timeout 0
> Wait Time 0
> Last Target 115020
>
> (11 row(s) affected)
>
> Small Query Memory Objects Value
> ------------------------------ -----------
> Grants 0
> Waiting 0
> Available (Buffers) 5751
> Maximum (Buffers) 5751
> Limit 5751
>
> (5 row(s) affected)
>
> Optimization Queue Value
> ------------------------------ --------------------
> Overall Memory 1005060096
> Target Memory 967606272
> Last Notification 1
> Timeout 6
> Early Termination Factor 5
>
> (5 row(s) affected)
>
> Small Gateway Value
> ------------------------------ --------------------
> Configured Units 32
> Available Units 32
> Acquires 0
> Waiters 0
> Threshold Factor 250000
> Threshold 250000
>
> (6 row(s) affected)
>
> Medium Gateway Value
> ------------------------------ --------------------
> Configured Units 8
> Available Units 8
> Acquires 0
> Waiters 0
> Threshold Factor 12
>
> (5 row(s) affected)
>
> Big Gateway Value
> ------------------------------ --------------------
> Configured Units 1
> Available Units 1
> Acquires 0
> Waiters 0
> Threshold Factor 8
>
> (5 row(s) affected)
>
> MEMORYBROKER_FOR_CACHE Value
> -------------------------------- --------------------
> Allocations 5627
> Rate -33767
> Target Allocations 116843
> Future Allocations 0
> Last Notification 1
>
> (5 row(s) affected)
>
> MEMORYBROKER_FOR_STEAL Value
> -------------------------------- --------------------
> Allocations 5638
> Rate -4365
> Target Allocations 118116
> Future Allocations 0
> Last Notification 1
>
> (5 row(s) affected)
>
> MEMORYBROKER_FOR_RESERVE Value
> -------------------------------- --------------------
> Allocations 1998
> Rate -4795
> Target Allocations 122688
> Future Allocations 27317
> Last Notification 1
>
> (5 row(s) affected)
>
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>


From: M Bourgon on
On Apr 26, 11:13 am, "TheSQLGuru" <kgbo...(a)earthlink.net> wrote:
> 1) your max memory is way too high.  start with 27GB and go down from there
> if you have problems.
>
> 2) what were the EXACT messages you were getting?
>
> 3) 32bit or 64 bit?  Anything else running on the box?
>
> 4) troubleshooting memory issues is a VERY deep and complex subject.  I
> recommend you get a performance tuning pro on board to assist you.

1) Interesting. Why? 29 gives 3gb for the OS, which should be plenty
2) We were getting several in the error logs. We got:
There is insufficient system memory to run this query. (Error: 701,
Severity: 17, State: 123.)
AppDomain 623 (clr_code_name.dbo[runtime].1043) is marked for unload
due to memory pressure.
..NET Framework execution was aborted by escalation policy because of
out of memory.
SQL Trace failed to send event notification. The server has run out of
memory. The same send failure may not be reported in the future.

Also were seeing :
SLEEP_MSDBSTARTUP for the wait type, even though the MSDB had
obviously been up.

3) Sorry, thought I'd included it. 32bit, and nothing else running on
the box except for Quest (agent only). We think Spotlight may be
having its own issue with MSDB (service broker messages that keep
growing the file about 1gb per day). We're currently investigating
that with them. Yes, it's possible it's related, but that issue was
happening prior to the upgrade.

Additionally, we were getting the "marked for unload due to memory
pressure" prior to the upgrade, but we hadn't had issues where we had
to restart the server to fix.
From: TheSQLGuru on
There are a lot of issues here. CLR and query hash/sort memory, among other
things, has to sit down in lower memory. That is part of your problem. 32
bit is NOT GOOD for memory stuff in this day and age. There are things you
can do, such as -g startup parameter. /PAE and AWE on? Lock pages in
memory enabled??

3GB for the OS just ins't enough. There are lots of other things running on
the typical server (even one dedicated to SQL Server) that also need memory.
In this case though it may not be buffer pool that is the issue but rather
memtoleave and lower memory (2GB for the user space).

There are also some bugs that could be at play here. What SP/hotfix?

Again I recommend you get a professional to assist. Deep, complex stuff and
likely multiple issues are simply impossible to effectively troubleshoot and
deal with via newsgroup posts. There are a number of independent
consultants on this forum that could help, and there is always Microsoft if
you feel better going to them.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"M Bourgon" <bourgon(a)gmail.com> wrote in message
news:10e6de9f-3dae-4d7f-b906-3d9df5b5f56b(a)q13g2000vbm.googlegroups.com...
On Apr 26, 11:13 am, "TheSQLGuru" <kgbo...(a)earthlink.net> wrote:
> 1) your max memory is way too high. start with 27GB and go down from there
> if you have problems.
>
> 2) what were the EXACT messages you were getting?
>
> 3) 32bit or 64 bit? Anything else running on the box?
>
> 4) troubleshooting memory issues is a VERY deep and complex subject. I
> recommend you get a performance tuning pro on board to assist you.

1) Interesting. Why? 29 gives 3gb for the OS, which should be plenty
2) We were getting several in the error logs. We got:
There is insufficient system memory to run this query. (Error: 701,
Severity: 17, State: 123.)
AppDomain 623 (clr_code_name.dbo[runtime].1043) is marked for unload
due to memory pressure.
..NET Framework execution was aborted by escalation policy because of
out of memory.
SQL Trace failed to send event notification. The server has run out of
memory. The same send failure may not be reported in the future.

Also were seeing :
SLEEP_MSDBSTARTUP for the wait type, even though the MSDB had
obviously been up.

3) Sorry, thought I'd included it. 32bit, and nothing else running on
the box except for Quest (agent only). We think Spotlight may be
having its own issue with MSDB (service broker messages that keep
growing the file about 1gb per day). We're currently investigating
that with them. Yes, it's possible it's related, but that issue was
happening prior to the upgrade.

Additionally, we were getting the "marked for unload due to memory
pressure" prior to the upgrade, but we hadn't had issues where we had
to restart the server to fix.