SQL Server Developer Center >
SQL Server Forums
>
SQL Server Database Engine
>
VAS Memory Fragmented
VAS Memory Fragmented
- We are using SQL Server 2008 Standard Edition with SP1 on a 32bit server. It runs on Windows Server 2008 Standard and has 4 gigs of RAM.
Max server memory (MB) is set at 2000
Min server memory (MB) is set at 1500
AWE is not enabled
For the past month, i have seen the following error twice on the server:
FAIL_VIRTUAL_RESERVE 1048576
I have been watching the server since and I've noticed that the total avail memory and max free size has been slowly declining during the week. I ran the following query to check that:
With VASummary(Size,Reserved,Free) AS
(SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
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 VASummary
WHERE Free <> 0
Here is the DBCC MemoryStatus output right when the error happened:
Memory Manager KB
---------------------------------------- ----------
VM Reserved 1682680
VM Committed 485912
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
2009-11-02 02:00:05.86 spid70
Memory node Id = 0 KB
---------------------------------------- ----------
VM Reserved 1679864
VM Committed 483208
AWE Allocated 0
MultiPage Allocator 12232
SinglePage Allocator 14528
2009-11-02 02:00:05.86 spid70
Memory node Id = 32 KB
---------------------------------------- ----------
VM Reserved 1728
VM Committed 1672
AWE Allocated 0
MultiPage Allocator 1600
SinglePage Allocator 14528
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1296
MultiPage Allocator 1424
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 1654432
VM Committed 458872
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 504
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 136
MultiPage Allocator 72
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- ----------
VM Reserved 1336
VM Committed 1336
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 104
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- ----------
VM Reserved 1920
VM Committed 1920
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1056
MultiPage Allocator 2928
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 504
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 128
MultiPage Allocator 344
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 40
MultiPage Allocator 16
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SNI (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SNI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 40
MultiPage Allocator 32
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 584
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 4224
VM Committed 4224
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 104
MultiPage Allocator 64
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2072
MultiPage Allocator 5280
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SOSNODE (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 1520
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2088
MultiPage Allocator 6800
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 96
SM Committed 96
SinglePage Allocator 0
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 40
MultiPage Allocator 96
2009-11-02 02:00:05.86 spid70
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1040
MultiPage Allocator 232
2009-11-02 02:00:05.86 spid70
CACHESTORE_PHDR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_XPROC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
2009-11-02 02:00:05.86 spid70
CACHESTORE_STACKFRAMES (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
2009-11-02 02:00:05.86 spid70
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
2009-11-02 02:00:05.86 spid70
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4176
MultiPage Allocator 440
2009-11-02 02:00:05.86 spid70
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 272
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 368
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 240
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
USERSTORE_SXC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 56
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
USERSTORE_SXC (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 64
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 64
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 568
MultiPage Allocator 56
2009-11-02 02:00:05.86 spid70
OBJECTSTORE_SNI_PACKET (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 56
2009-11-02 02:00:05.86 spid70
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 568
MultiPage Allocator 112
2009-11-02 02:00:05.86 spid70
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 400
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 768
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
OBJECTSTORE_LOCK_MANAGER (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 792
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
Buffer Pool Value
---------------------------------------- ----------
Committed 56704
Target 204776
Database 42661
Dirty 30812
In IO 0
Latched 52
Free 12226
Stolen 1817
Reserved 0
Visible 204776
Stolen Potential 192720
Limiting Factor 11
Last OOM Factor 0
Page Life Expectancy 804516
2009-11-02 02:00:05.86 spid70
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 2178785280
Available Virtual Memory 39092224
Available Paging File 4636872704
Working Set 750129152
Percent of Committed Memory in WS 100
Page Faults 1107202
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 1
2009-11-02 02:00:05.86 spid70
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 1
TotalPages 163
InUsePages 104
2009-11-02 02:00:05.86 spid70
Global Memory Objects Pages
---------------------------------------- ----------
Resource 178
Locks 99
XDES 34
SETLS 4
SE Dataset Allocators 8
SubpDesc Allocators 4
SE SchemaManager 230
SE Column Metadata Cache 346
SQLCache 43
Replication 2
ServerGlobal 27
XP Global 2
SortTables 2
2009-11-02 02:00:05.86 spid70
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 146177
Current Max 146177
Future Max 146177
Physical Max 146177
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2009-11-02 02:00:05.86 spid70
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 7693
Current Max 7693
Future Max 7693
2009-11-02 02:00:05.86 spid70
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 1344634880
Target Memory 1285734400
Last Notification 1
Timeout 6
Early Termination Factor 5
2009-11-02 02:00:05.86 spid70
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
2009-11-02 02:00:05.86 spid70
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
2009-11-02 02:00:05.86 spid70
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2009-11-02 02:00:05.86 spid70
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 0
Reserved Limit 192721
2009-11-02 02:00:05.86 spid70
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 1816
Predicted 38360
Private Target 0
Private Limit 0
Total Target 194537
Total Limit 194537
OOM Count 0
2009-11-02 02:00:05.86 spid70
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 1043
Rate -3402
Target Allocations 157220
Future Allocations 0
Overall 164140
Last Notification 1
2009-11-02 02:00:05.86 spid70
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 773
Rate -22
Target Allocations 156950
Future Allocations 0
Overall 164140
Last Notification 1
2009-11-02 02:00:05.86 spid70
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 164140
Future Allocations 36544
Overall 164140
Last Notification 1
There are no 3rd party backups used on the server. Can someone tell me what is causing VAS to be fragmented? Is there a way I can tell what is causing the fragmentation and using up VAS? I have a feeling it might be Ad Hoc queries but how do I confirm that? There is also a job that runs every 15 mins that uses a linked query to update a table, could that be causing it too?
All Replies
You are likely running into the problem that is detailed in the following PSS blog post:
If you use linked server queries, you need to read this….
Make sure that you have applied the CU3 to SP1 on SQL Server 2008.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!- I will give that a try. The odd thing is that we've had that job run back in august and it uses linked servers and we haven't had any problems until recently. I've also been watching the server and running the query to check the VAS memory. The memory doesn't seem to go down after the job runs. If the linked server was an issue, wouldn't I see the decrease in memory each time the job runs?
Could the other problem be Ad-Hoc queries? I ran the following query and the number for Adhoc is pretty high compared to everything else:
select count(objtype), objtype
from sys.dm_exec_cached_plans
group by objtype
Results:
447 Adhoc
4 Check
64 Prepared
69 Proc
3 Trigger
1 UsrTab
76 View
- Your plan cache is limited in size by the system, and based on the numbers in your MEMORYSTATUS DUMP its not something I suspect as part of the issue:
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-11-02 02:00:05.86 spid70
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1040
MultiPage Allocator 232
Your dump shows about 1.5MB of cache space usage. Keep in mind that most plans in cache are 8KB or less in size. You need to be looking for a VAS consumer, so check for things like ADO.NET connection network packet sizes > 8000 bytes, OLE Automation calls (sp_OACreate), heavy XML processing (either XQuery or OPENXML), linked servers, SQLCLR, etc. One thing I'd make note of is that you have a 1MB allocation failure which is by default the Minimum Memory per Query setting, so you have a query attempting to execute that can't get a VAS allocation.
You can check network packet sizes being used with:
select c.session_id, c.connect_time, c.net_transport, c.protocol_type, c.protocol_version, c.net_packet_size, c.client_net_address, c.client_tcp_port, s.original_login_name, s.host_name from sys.dm_exec_connections as c join sys.dm_exec_sessions as s on c.session_id = s.session_id where net_packet_size > 8000
Your other thing to do if this is a repeating event is to start a server side trace to capture the statements starting events, and the Exception and Error Log events and then when the error triggers, use correlation backwards from the exception in the trace to find the statement that was submitted and acutally caused the failure. From there you might find a repeating occurence or problemattic query, but I don't think it would be that.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem! - Jonathan,
I have checked the network connections and I don't see anything over 8KB. SQLCLR isn't enabled so I know that isn't the case here. I have read somewhere that another consumer of MTL is queries where the query plan is greater than 8KB. Could that be the case here?
I'm sorry, I'm still kinda new here and trying to learn at the same time so please bear with me. I have been looking at the server everyday and checking the VAS. At the beginning of the week, it looks like VAS will slowly decrease through the week, until the end of the week where there is less activity on that server. I can tell when that error will come up because I see the VAS go down really low the max free size will go down to less than 7mb.
Is there a way to tell what is causing the VAS to be consumed? - Kaitlyn,
My apologies for the delay but I was flying home from PASS Summit this weekend. Since the drop is predictable, yes you can do a lot to check what is consuming the VAS. You can start by running DBCC MEMORYSTATUS when the available VAS drops low. You can also query sys.dm_os_memory_clerks to get a more filtered output:
select top 10 type, SUM(single_pages_kb) as total_single_pages_kb, SUM(multi_pages_kb) as total_multi_pages_kb, SUM(virtual_memory_reserved_kb) as total_virtual_memory_reserved_kb, SUM(virtual_memory_committed_kb) as total_virtual_memory_committed_kb, SUM(awe_allocated_kb) as total_awe_allocated_kb, SUM(shared_memory_reserved_kb) as total_shared_memory_reserved_kb, SUM(shared_memory_committed_kb) as total_shared_memory_committed_kb from sys.dm_os_memory_clerks group by type order by SUM(multi_pages_kb) desc select top 10 type, SUM(single_pages_kb) as total_single_pages_kb, SUM(multi_pages_kb) as total_multi_pages_kb, SUM(virtual_memory_reserved_kb) as total_virtual_memory_reserved_kb, SUM(virtual_memory_committed_kb) as total_virtual_memory_committed_kb, SUM(awe_allocated_kb) as total_awe_allocated_kb, SUM(shared_memory_reserved_kb) as total_shared_memory_reserved_kb, SUM(shared_memory_committed_kb) as total_shared_memory_committed_kb from sys.dm_os_memory_clerks group by type order by SUM(virtual_memory_committed_kb) desc
You will want to look at both of the above queries to see your high VAS consumers. You are likely looking for a multi_pages_kb consumer but it is possible that you have another virtual_memory_committed_kb consumer other than the buffer pool. If you run these queries, post the output and we can help you interpret it.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem! - Since, the Multi Page Allocator for a single clerk is not that high, I'm speculating that the problem is not due a memory requested through a clerk and the query above will not help in your problem much.
However, a failed to reserve request for 1MB is quite high because the MTL region is only a few hundred MB in size (approx around 384MB in size). You can use the query mentioned in the blog post below to find out the largest free block in the MTL during the problem period:
http://troubleshootingsql.spaces.live.com/blog/cns!B15BEF95FF768C33!231.entry
From the discussion thread above, it looks like you are facing a VAS fragmentation issue. You can use the elimination method by moving each of the MTL consumers (mentioned by Jon earlier in this thread) that are active on your instance, out-of-process and monitoring the situation.
In case you want to debug this further, I would suggest you open a ticket with SQL support. They can capture diagnostic data and help you try and pin point the culprit here.
In case, you want to do neither of the above, then you can add the -g switch with (-g384) along with the /3GB switch to increase the MTL region (and not affect the BPool region) to give more breathing room in the MTL region. This is however a workaround and would just prolong the time taken by the issue to re-occurr.
For "The memory doesn't seem to go down after the job runs. If the linked server was an issue, wouldn't I see the decrease in memory each time the job runs?":
SQL Server is not designed to release the memory once it has acquired it. It will only back off it's memory allocations only if it feels that there is an internal memory pressure or the OS sends a low memory notification to the SQL instance. If this is an issue related to fragmentation, then everytime the job runs you will not see this issue. Assuming that linked servers are the culprit here because I didn't see you mention any other possible MTL consumers in the threading. You will start with a non-fragmentated MTL region and as the jobs runs, the MTL region would get fragmented. Once you reach a level of fragmentation where there is no contiguous block available to satisfy the memory requests larger than 8KB, then you will run into this issue.
Now the other question is does this problem fix itself without having to restart the SQL Service? If yes, then this is not a fragmentation issue only as there might be a large consumer of the MTL which is asking for memory allocations greater than 8KB without going through the SQL Memory Clerks.
Could you provide the details of the lined server that is being used on your server?
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL Since, the Multi Page Allocator for a single clerk is not that high, I'm speculating that the problem is not due a memory requested through a clerk and the query above will not help in your problem much.
However, a failed to reserve request for 1MB is quite high because the MTL region is only a few hundred MB in size (approx around 384MB in size).
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
Amit,
1MB is the minimum Query Memory configuration for all SQL Server 2005 installations. The script in the first post of this thread already has the max contiguous space information in it. Christian Bolton (former CSS member) blogged that script two years ago (http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx)
select name, value_in_use from sys.configurations where name = 'min memory per query (KB)'
I have become suspect of the MemoryStatus dumps from the errorlog after a FAILED_VIRTUAL_RESERVE like this because they never show the VAS consumers, and it looks like it is occuring post SHRINK notification of the clerks. I could be wrong here, but I have had better luck looking at the output before the error rather than after the fact.
The other thing to look at is how many concurrent connections are there to SQL Server at the time that this occurs.
select program_name, host_name, count(*) from sys.dm_exec_sessions where is_user_process = 1 group by program_name, host_name order by 3 desc
If you have enough concurrent connections, it would be concievable that you could get into VAS pressure though I haven't seen it myself.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!- Thanks for the help and insight, it is very helpful. Here are the results on the two queries you asked me to run Jonathan. Note, at the time when I ran these queries, here are the VAS numbers:
Total avail mem, KB Max free size, KB
-------------------- --------------------
83576 7920
select top 10
type,
SUM(single_pages_kb) as total_single_pages_kb,
SUM(multi_pages_kb) as total_multi_pages_kb,
SUM(virtual_memory_reserved_kb) as total_virtual_memory_reserved_kb,
SUM(virtual_memory_committed_kb) as total_virtual_memory_committed_kb,
SUM(awe_allocated_kb) as total_awe_allocated_kb,
SUM(shared_memory_reserved_kb) as total_shared_memory_reserved_kb,
SUM(shared_memory_committed_kb) as total_shared_memory_committed_kb
from sys.dm_os_memory_clerks
group by type
order by SUM(multi_pages_kb) desc
RESULTS:
MEMORYCLERK_SOSNODE 2040 6800 0 0 0 0 0
MEMORYCLERK_SQLSTORENG 1272 2976 1408 1408 0 0 0
MEMORYCLERK_SQLGENERAL 1112 1424 0 0 0 0 0
CACHESTORE_SQLCP 39312 592 0 0 0 0 0
MEMORYCLERK_SQLBUFFERPOOL 0 504 1654432 453752 0 0 0
USERSTORE_SCHEMAMGR 3888 456 0 0 0 0 0
CACHESTORE_OBJCP 14688 384 0 0 0 0 0
MEMORYCLERK_SQLSERVICEBROKER 120 344 0 0 0 0 0
OBJECTSTORE_SNI_PACKET 808 112 0 0 0 0 0
MEMORYCLERK_XE 40 96 0 0 0 0 0
select top 10
type,
SUM(single_pages_kb) as total_single_pages_kb,
SUM(multi_pages_kb) as total_multi_pages_kb,
SUM(virtual_memory_reserved_kb) as total_virtual_memory_reserved_kb,
SUM(virtual_memory_committed_kb) as total_virtual_memory_committed_kb,
SUM(awe_allocated_kb) as total_awe_allocated_kb,
SUM(shared_memory_reserved_kb) as total_shared_memory_reserved_kb,
SUM(shared_memory_committed_kb) as total_shared_memory_committed_kb
from sys.dm_os_memory_clerks
group by type
order by SUM(virtual_memory_committed_kb) desc
RESULTS:
MEMORYCLERK_SQLBUFFERPOOL 0 504 1654432 453752 0 0 0
MEMORYCLERK_XE_BUFFER 0 0 4224 4224 0 0 0
OBJECTSTORE_LOCK_MANAGER 888 0 4096 4096 0 0 0
MEMORYCLERK_SQLSTORENG 1272 2976 1408 1408 0 0 0
MEMORYCLERK_SQLUTILITIES 72 0 120 120 0 0 0
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT 32 0 0 0 0 0 0
OBJECTSTORE_SERVICE_BROKER 400 0 0 0 0 0 0
CACHESTORE_BROKERRSB 8 0 0 0 0 0 0
OBJECTSTORE_SNI_PACKET 808 112 0 0 0 0 0
CACHESTORE_XPROC 120 0 0 0 0 0 0
I have noticed that VAS has gone down from 141 MB to 84 over the weekend.
Now the other question is does this problem fix itself without having to restart the SQL Service? If yes, then this is not a fragmentation issue only as there might be a large consumer of the MTL which is asking for memory allocations greater than 8KB without going through the SQL Memory Clerks.
No, the problem does not go away, we would have to do a restart of the SQL Service everytime.
The linked server that we use is the SQLOLDB provider. That linked server points to another SQL 2008 Server. I tried setting it to run out of process but it came back with an error saying that was not supported.
Here are the results for the concurrent connections:
Microsoft SQL Server Management Studio - Query DSDATATEAM01 12
Microsoft SQL Server Management Studio DSDATATEAM01 4
DatabaseMail - SQLAGENT - Id<3152> FWWEBSQL01 1
Microsoft SQL Server Management Studio FWWEBSQL01 1
SQLAgent - Email Logger FWWEBSQL01 1
SQLAgent - Generic Refresher FWWEBSQL01 1
SQLAgent - Job invocation engine FWWEBSQL01 1
SQLAgent - TSQL JobStep (Job 0xFBFF5D35F808A143B67E30C6C5A6D88A : Step 1) FWWEBSQL01 1
.Net SqlClient Data Provider Web Track 1
The top 2 are my open queries that I have running to troubleshoot this issue.
Thanks for all the help, please let me know what you think of this or if you need any more info.
- SQL reacting to SHRINK notifications would result in an incorrect MPA values only if one of the Memory Clerks was called to allocate the Multi Pages (>8KB). If it was Min Query Memory, then I would expect SQLQueryExec to show some allocations for both MPA and SPA as the Memory Status dump in the Errorlog would have been done as soon as an OOM condition was hit. And if the minimum query execution memory was not obtained then all query executions after the Failed to Reserve error would fail. Ideally all query execution memory is allocated from the Buffer Pool as most of the Query Exection memory is used for hashing/sorting operations. However, there is always a possibility that the query execution memory could be from MPA allocations. In such a scenario, you are right that if a Shrink notification is received prior to the OOM condition, then the DBCC MemoryStatus output might not reflect the true picture if a Clerk is the culprit. And yes, VAS consumers are not mentioned from MTL perspective unless the memory allocation request came from a Memory Clerk.
Unfortunately, a Failed to Reserve error is always encountered by the victim. So, the determination of the culprit causing the fragementation would need monitoring.
Looking at the Max Free Size output, I see that the largest free block is only 7MB in size. It would be interesting to note if the Max Free Size value reduces by a substantial amount everytime a linked server query is executed that brings in a large amount of data.
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL SQL reacting to SHRINK notifications would result in an incorrect MPA values only if one of the Memory Clerks was called to allocate the Multi Pages (>8KB). If it was Min Query Memory, then I would expect SQLQueryExec to show some allocations for both MPA and SPA as the Memory Status dump in the Errorlog would have been done as soon as an OOM condition was hit. And if the minimum query execution memory was not obtained then all query executions after the Failed to Reserve error would fail. Ideally all query execution memory is allocated from the Buffer Pool as most of the Query Exection memory is used for hashing/sorting operations. However, there is always a possibility that the query execution memory could be from MPA allocations. In such a scenario, you are right that if a Shrink notification is received prior to the OOM condition, then the DBCC MemoryStatus output might not reflect the true picture if a Clerk is the culprit. And yes, VAS consumers are not mentioned from MTL perspective unless the memory allocation request came from a Memory Clerk.
Unfortunately, a Failed to Reserve error is always encountered by the victim. So, the determination of the culprit causing the fragementation would need monitoring.
Looking at the Max Free Size output, I see that the largest free block is only 7MB in size. It would be interesting to note if the Max Free Size value reduces by a substantial amount everytime a linked server query is executed that brings in a large amount of data.
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
I am trying to reproduce this so I can find out for myself but perhaps you have the answer immediately. Can't you query the ring buffers for the OOM and then work back through the Notifications to find the allocations happening? I am sure that I have seen this done before but I can't find it, and I have to recreate a OOM before I can look at it myself. Working on a nasty CLR leak combined with the linked server issue to prompt the OOM currently just so I can repro this later. It should be something like:
SELECT CAST(record as XML) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_OOM'
From there the standard notifications up to may be able to show the actual problem occuring.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!- When I ran the query, nothing was returned. Should I run it when the error occurs? I have a feeling it will probably occur tonight since this is the current VAS available:
Total avail mem, KB Max free size, KB
-------------------- --------------------
70452 7752
As you can see when i started this thread, it has been slowly declining.
Can't you query the ring buffers for the OOM and then work back through the Notifications to find the allocations happening?
How would I work back through the notifications to find the allocations that would point to the OOM?
As for the linked server, when it executes, it doesn't always return huge chunks of data. Sometimes it doesn't update anything at all. - Yeah, you'd have to run that after the OOM failure. The question in bold was actually back to Amit at Microsoft to see if he knows whether the ring buffers would work to identify the problem better. When the problem occurs run the following before restarting SQL:
select ring_buffer_type, timestamp, cast(record as xml) as record from sys.dm_os_ring_buffers where ring_buffer_type IN ('RING_BUFFER_BUFFER_POOL', 'RING_BUFFER_OOM')
These are both ring buffers for OOM conditions.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem! - Great! Thanks again! I will run it when the error occurs and post the results.
- After recreating the OOM issue a number of different ways it seems like the following should help you identify the major consumer???
Post the top 5-10 rows from this output back here.SELECT type, SUM (pages_allocated_count * page_size_in_bytes)/1024 as 'Current KB Used', SUM (max_pages_allocated_count * page_size_in_bytes)/1024 as 'Max KB Used' FROM sys.dm_os_memory_objects GROUP BY type ORDER BY 3 DESC;
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem! Sorry about the late reply. Had a look at the thread just now. Looked up the Ring Buffer OOM record. There won't be much information there other than the action (Commit/Reserve) and the amount of resources requested. This Ring Buffer type would be a good way to track how many times you have hit an OOM condition in the past. The Ring Buffer Pool entry would track only the BPool statitistics. So, this wouldn't be of much help either.
Jonathan's query above should help you identify the top consumers provided one of the SQL memory clerks. This however will not return information about memory objects allocated by SQL Server. Eg. OLE DB Heaps, XProcs etc. In such scenarios, we would have to take a memory dump during the problem period and inspect the heaps and memory structures or perform more invasive debugging like attaching a leak tracker similar to the one mentioned in the below KB:
http://support.microsoft.com/default.aspx/kb/919790
However, this is a very invasive process and can degrade performance because leak tracking basically means attaching a hook to your process and tracking each and every memory allocation request. Which is why we suggest an elimination approach or using the Max Free Size query to determine a drop in the largest contiguous block and using educated guesses narrow down on the culprit. This is a always a difficult problem to troubleshoot when one of the Memory Clerks is not the culprit!
Another query that can help here is:
select
sum(multi_pages_kb)/1024 as OS_Memory_in_Mb from sys.dm_os_memory_clerks
select
* from sys.dm_os_memory_clerks where multi_pages_kb > 0 order by multi_pages_kb desc
You could also use Virtual Bytes and Private Bytes of the Process Object for sqlservr.exe under Perfmon to track when the Private Bytes and Virtual Bytes are increasing drastically and co-relate that with what is running on the SQL instance during that time maybe with a sys.dm_exec_requests output or a server side profiler trace.
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQLAfter recreating the OOM issue a number of different ways it seems like the following should help you identify the major consumer???
Post the top 5-10 rows from this output back here.SELECT
type ,
SUM (pages_allocated_count * page_size_in_bytes)/1024 as 'Current KB Used' ,
SUM (max_pages_allocated_count * page_size_in_bytes)/1024 as 'Max KB Used'
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY 3 DESC ;
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!
Here is the output from the query above:
MEMOBJ_XSTMT 18424 20008
MEMOBJ_COMPILE_ADHOC 14152 14152
MEMOBJ_STATEMENT 13632 13632
MEMOBJ_PARSE 9280 9280
MEMOBJ_EXECUTE 8136 8136
MEMOBJ_METADATADB 6768 6776
MEMOBJ_QUERYEXECCNTXTFORSE 6752 6752
MEMOBJ_SOSNODE 6520 6552
MEMOBJ_SECOLMETACACHE 3448 3608
MEMOBJ_SOSDEADLOCKMONITORRINGBUFFER 2080 2080
Here is the current VAS reading:
Total avail mem, KB Max free size, KB
-------------------- --------------------
48276 3912
- Run it again and order by 2 desc:
SELECT type, SUM (cast(pages_allocated_count as bigint) * page_size_in_bytes)/1024 as 'Current KB Used', SUM (cast(max_pages_allocated_count as bigint) * page_size_in_bytes)/1024 as 'Max KB Used' FROM sys.dm_os_memory_objects GROUP BY type ORDER BY 2 DESC;
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem! Run it again and order by 2 desc:
SELECT
type ,
SUM (cast (pages_allocated_count as bigint ) * page_size_in_bytes)/1024 as 'Current KB Used' ,
SUM (cast (max_pages_allocated_count as bigint ) * page_size_in_bytes)/1024 as 'Max KB Used'
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY 2 DESC ;
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!
Here is the top 10 result:
MEMOBJ_XSTMT 18472 20056
MEMOBJ_COMPILE_ADHOC 14248 14248
MEMOBJ_STATEMENT 13632 13632
MEMOBJ_PARSE 9280 9280
MEMOBJ_EXECUTE 8176 8176
MEMOBJ_QUERYEXECCNTXTFORSE 6768 6768
MEMOBJ_METADATADB 6768 6776
MEMOBJ_SOSNODE 6520 6552
MEMOBJ_SECOLMETACACHE 3464 3608
MEMOBJ_SOSDEADLOCKMONITORRINGBUFFER 2080 2080- The Max Free Size is 3912. What was the allocation size failure reported in the Failed To Reserve message?
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL - The Failed to Reserve error hasn't occured yet. But we are getting these errors in the logs:
The description for Event ID '1073758876' in Source 'MSSQLSERVER' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them. The following information is part of the event:'SQLServerAgent Monitor: SQLServerAgent has terminated unexpectedly.'
Message
SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: <local machine>]
I have notice my connections to the sql server is not working, it takes a really long time and simple queries won't even connect. But if I log onto the server itself to run the queries, then it's fine. Do the errors above have anything to do with the memory issues? I will need to reboot the server soon since I can't seem to run any queries. - The error code 0xc0000000 indicates a low resource condition and not necessarily a failed to reserve error as opposed to SQL 2000 instances. If you had a failed to reserve issue on SQL, then you wouldn't be able to log in at all into the instance without using DAC.
Could you look at the Mem To Leave consumers mentioned by Jon earlier in the thread and mention which all are applicable in your case other than linked servers?
Also, what is the Max Worker Threads setting in the sp_configure output.
In case, it's only linked servers and their settings cannot be changed, then you would probably have to add -g384 to your startup parameters to increase the MemToLeave region to suit your contiguous memory needs. In such a scenario, without analyzing a memory dump or using any other forms of debugging, it would be difficult to pin point the culprit.
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL - The only MTL consumers we have are the linked server and possibly query plans that are greater than 8KB. We don't use CLR, there are no extended procedure calls and no network packets greater than 8kb.
I will up the MemToLeave paramter to 384 but unless we find what's causing it to keep going down, wouldn't the error come back eventually? Is there a way to get a memory dump of this when the error occurs? - Yes, -g384 is a workaround. At this point, I don't see MTL exhaustion from the data that you have provided recently. I only see a low resource condition.
An OOM memory dump can be generated using trace flag 8004. Turning on/off this trace flag does not require a service restart. However, a mini-dump wouldn't be useful and a filtered dump would be required to make any headway with the analysis. You would require CSS assistance to analyze the memory dump.
Ref: http://technet.microsoft.com/en-us/library/cc627395.aspx
If it's not a VAS fragmentation issue, then -g384 might help you but the drawback of this is the additional 128 MB would be taken from your Buffer Pool which might be tolerable. You would have to monitor your server to determine if it's favorable or not. Also, is the max worker threads value the default and what is the number of processors on the server?
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL - I will raise the MTL to 384 and monitor the server.
The max worker threads is set at 0 and there are 4 CPUs on the server. - Then it's not an issue with excessive Max Worker Threads eating up the MTL either because with an x86 server with less than or equal to 4 processors, the number of worker threads is 256.
Please monitor the server and update it if there are any further occurrences of the problem.
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL Amit,
If I read the information in the thread correctly, the Available VAS is dropping as well as the Max Contiguous Block size which would seem more like a leak of some kind (linked server is still a key suspect) though it could be a missing sp_xml_removedocument call, sp_OADestroy call, or other leak occuring.
Kaitlyn,
One thing I haven't asked is what do you use for database backups, and how often are backups occuring? Do you use a maintenance plan, or naitive TSQL commands in a custom script, or do you have a 3rd party backup tool like Netbackup, Litespeed, SQLBackup, etc managing your backups? I ask because third party tools are notorious for using configuration settings that "boost" backup performance like larger network packet sizes, custom extended stored procedures loaded into SQL Server, or high max transfer sizes that use VAS and can lead to VAS fragmentation.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!- I have added the -g384 parameter and will be watching the server. I have seen it decrease in VAS already but not by much. Is there anything else I can monitor if I see the VAS decreasing again?
Amit,
If I read the information in the thread correctly, the Available VAS is dropping as well as the Max Contiguous Block size which would seem more like a leak of some kind (linked server is still a key suspect) though it could be a missing sp_xml_removedocument call, sp_OADestroy call, or other leak occuring.
Kaitlyn,
One thing I haven't asked is what do you use for database backups, and how often are backups occuring? Do you use a maintenance plan, or naitive TSQL commands in a custom script, or do you have a 3rd party backup tool like Netbackup, Litespeed, SQLBackup, etc managing your backups? I ask because third party tools are notorious for using configuration settings that "boost" backup performance like larger network packet sizes, custom extended stored procedures loaded into SQL Server, or high max transfer sizes that use VAS and can lead to VAS fragmentation.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!
Jonathan,
We are just using a maintenance plan for the backups. We only have a full backup scheduled nightly at 10pm. I know that we don't use any 3rd party tools for the backup. I do think it's either the linked server job or Adhoc queries that's causing the VAS to drop consistently, i just don't know how to prove that. Is there a way to tell? Something I can run or trace to prove that it might be the linked server or too many Adhoc queries?- If I understand correctly from the thread Kaitlyn has mentioned that other than linked servers she doesn't have any other MTL consumer. Kaitlyn can you confirm this? I have listed out the common MTL consumers @ http://troubleshootingsql.spaces.live.com/blog/cns!B15BEF95FF768C33!232.entry
If you have a lot of Plans consuming MTL space, you should be able to confirm this using sys.dm_exec_cached_plans DMV. Fragmentation of MTL is most common when OleDb heaps are used and there are large plans in the Proc Cache which consume MTL space. For the other consumers, it's relatively easier to identify the actual root cause without taking a memory dump.
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL - Amit, that is correct, the only MTL consumers are the linked server and AdHoc queries that are greater than 8KB.
I ran the following query:
select * from sys.dm_exec_cached_plans
where size_in_bytes > 8192
order by size_in_bytes desc
The biggest one I see is and Adhoc query where the size in bytes is 1310720. What should I be looking for in the dm_exec_cahced_plans view? - You could check the number of plans that are above 8KB and see if there are any plans which are very large in size. Since, it's just 1+MB at the moment for the largest plan, I don't think this is the problem. You can keep monitoring and find out if you eventually have any large query plans which consume a few MB and what is the frequency of such plans.
The DBCC MEMORYSTATUS dump that you pasted above didn't have any OBJCP or SQLCP clerks showing high MPA usage. But as Jon mentioned if the notification was received early on regarding the VAS crunch, then SQL would start flushing the plans to make room in case these plans were not being used.
Currently, it looks like it could be large plans or linked servers. You would have to keep monitoring the largest contiguous block size and the drop in the same when there's a large query plan in the cache or a linked server query is executed which fetches a large amount of data.
This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
The biggest one I see is and Adhoc query where the size in bytes is 1310720. What should I be looking for in the dm_exec_cahced_plans view?
Take the plan handle from that large plan and use it with sys.dm_exec_sql_text() to get the sql statement being executed. Then see if you can simplify the query or rewrite it to reduce the size of the plan. If you'd like help with that, you can shoot me an email through my blog and I'll reply with my email address and you can send me the query. Its going to be to large to post back to the forum.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!- Thanks for the help. I will keep monitoring the server to see if VAS holds steady. So far, i haven't seen it change much but I'll keep watching it.
Jonathan, I took a look at the query that was over 1mb and it looks like it's using a linked server to do an update. I wonder if that's the cause. The usecounts column shows 103, does that mean that's how many times the plan has been used? - hi,
we had VAS fragmentation issue, in our case, we were using linkeds ervers and DLLs, dont know what caused
the only thing that fixed the issue was to include the startup with -g 384
thanks - So I've been monitoring the VAS memory and it has gone way done again. Here are the current values:
Total avail mem, KB Max free size, KB
-------------------- --------------------
80396 7736
I ran a DBCC Memorystatus and I did not see anything jump out, the MPA values are very low. I have also applied the CU3 that Jonathan mentioned early on but that does not seem to help.
Any ideas on what else I can look for or what to trace in order to see what's consuming VAS? So I've been monitoring the VAS memory and it has gone way done again. Here are the current values:
Total avail mem, KB Max free size, KB
-------------------- --------------------
80396 7736
Did it slowly drop to this point or did it have a sudden drastic change that made it drop?
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!- I believe it was a slow drop over the weekend. When I checked last friday, it had more than 10000Kbs free. I just checked again and it's down to this:
Total avail mem, KB Max free size, KB
-------------------- --------------------
73572 4044
