August 9, 2013 at 11:15 am
I have a SQL instance that starting getting the "insufficient system memory" issue. Here is the server information:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
Jun 17 2011 00:57:23
Copyright (c) Microsoft Corporation
Standard Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1) (Hypervisor)
System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
Physical Memory: 16GB
Min Memory: 0GB
Max Memory: 12GB
Error: 701, Severity: 17, State: 123.
There is insufficient system memory in resource pool 'internal' to run this query.
Here is the really strange issue: task manager os only showing 5GB being used. Don't know if that has anything to do with anything.
When I look at sys.dm_os_process_memory I see the process_virtual_memory_low flag is 1.
total_virtual_address_space_kb = 2,047MB
virtual_address_space_committed_kb = 1,810MB
memory_utilization_percentage = 100
virtual_address_space_available_kb = 28MB
process_virtual_memory_low = 1
According to this there is VAS pressure. However, I have no idea how to correct it. Changing the SQL queries isn't an option for two reasons: 1) it is a vendor app and 2) nothing changed prior to this issue occurring and the application has been running for a year without problems.
Below is the MEMORYSTATUS results. Does anyone have any idea what to do to fix this?
Memory Manager KB
---------------------------------------- -----------
VM Reserved 1790320
VM Committed 1751256
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 1787504
VM Committed 1748552
AWE Allocated 0
MultiPage Allocator 55640
SinglePage Allocator 104448
(5 row(s) affected)
Memory node Id = 32 KB
---------------------------------------- -----------
VM Reserved 1728
VM Committed 1672
AWE Allocated 0
MultiPage Allocator 1600
SinglePage Allocator 104448
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24040
MultiPage Allocator 10120
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 1658304
VM Committed 1658304
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 504
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1384
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- -----------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 304
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- -----------
VM Reserved 14912
VM Committed 14912
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 34376
MultiPage Allocator 32280
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1576
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 38592
VM Committed 8472
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1336
MultiPage Allocator 568
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 176
MultiPage Allocator 344
(7 row(s) affected)
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
(7 row(s) affected)
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 240
MultiPage Allocator 16
(7 row(s) affected)
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
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 240
MultiPage Allocator 32
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 240
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3072
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 328
MultiPage Allocator 32
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 11288
MultiPage Allocator 10184
(7 row(s) affected)
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
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 11304
MultiPage Allocator 11704
(7 row(s) affected)
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
(7 row(s) affected)
MEMORYCLERK_XE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4664
MultiPage Allocator 128
(7 row(s) affected)
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 216
MultiPage Allocator 16
(7 row(s) affected)
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 616
MultiPage Allocator 232
(7 row(s) affected)
CACHESTORE_PHDR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 440
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 64
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
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
(7 row(s) affected)
CACHESTORE_CLRPROC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 40
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 280
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 144
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 12192
MultiPage Allocator 936
(7 row(s) affected)
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 912
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2624
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1360
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 208
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 216
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 104
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2040
MultiPage Allocator 56
(7 row(s) affected)
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
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2040
MultiPage Allocator 112
(7 row(s) affected)
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
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- -----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2416
MultiPage Allocator 0
(7 row(s) affected)
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
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- -----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2440
MultiPage Allocator 0
(7 row(s) affected)
Buffer Pool Value
---------------------------------------- -----------
Committed 204760
Target 204760
Database 189898
Dirty 18887
In IO 0
Latched 2
Free 1734
Stolen 13128
Reserved 378
Visible 204760
Stolen Potential 181015
Limiting Factor 11
Last OOM Factor 0
Last OS Error 0
Page Life Expectancy 8427
(15 row(s) affected)
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 11782029312
Available Virtual Memory 30199808
Available Paging File 29535764480
Working Set 1950310400
Percent of Committed Memory in WS 100
Page Faults 318585640
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 1
(10 row(s) affected)
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 8
TotalPages 454
InUsePages 26
(3 row(s) affected)
Global Memory Objects Pages
---------------------------------------- -----------
Resource 3321
Locks 305
XDES 127
SETLS 4
SE Dataset Allocators 8
SubpDesc Allocators 4
SE SchemaManager 631
SE Column Metadata Cache 945
SQLCache 55
Replication 2
ServerGlobal 29
XP Global 2
SortTables 3674
(13 row(s) affected)
Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 146163
Current Max 146163
Future Max 146163
Physical Max 146163
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
(11 row(s) affected)
Small Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 3
Waiting 0
Available 7308
Current Max 7692
Future Max 7692
(5 row(s) affected)
Optimization Queue (internal) Value
---------------------------------------- -----------
Overall Memory 1344528384
Target Memory 1269694464
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway (internal) Value
---------------------------------------- -----------
Configured Units 16
Available Units 12
Acquires 4
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway (internal) Value
---------------------------------------- -----------
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 12
Threshold 26451968
(6 row(s) affected)
Big Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
(6 row(s) affected)
Memory Pool Manager Pages
---------------------------------------- -----------
Reserved Current 384
Reserved Limit 181468
(2 row(s) affected)
Memory Pool (internal) Pages
---------------------------------------- -----------
Allocations 13438
Predicted 49594
Private Target 0
Private Limit 0
Total Target 194522
Total Limit 194522
OOM Count 0
(7 row(s) affected)
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- -----------
Allocations 2990
Rate -919
Target Allocations 147918
Future Allocations 0
Overall 164127
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- -----------
Allocations 10064
Rate -1321
Target Allocations 154992
Future Allocations 0
Overall 164127
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- -----------
Allocations 384
Rate 0
Target Allocations 164127
Future Allocations 36540
Overall 164127
Last Notification 1
(6 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
...
August 9, 2013 at 3:35 pm
It seems that you have not enabled AWE. Which means that you are only able to use 2GB or memory.
First check your BOOT.INI, and verify that you have the /PAE switch. If not add it.
Verify the setting of AWE with
sp_configure 'awe enabled'
and change to 1 if necessary:'
EXEC sp_configure 'awe enabled'
RECONFIGURE
You need to restart SQL Server. If you had to change BOOT.INI, you need to restart Windows.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 10, 2013 at 8:52 am
In addition to the 32 bit SQL Server constraint, I also note that you are way behind on updates too. You are on base SP1 (2500) and the current build is SP2 CU8 (build 4286).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 12, 2013 at 6:53 am
Erland Sommarskog (8/9/2013)
It seems that you have not enabled AWE. Which means that you are only able to use 2GB or memory.
Does this still matter in Windows 2008? I thought it was a Windows 2003 thing. It is using 5GB of RAM. I'm a little fuzzy on the whole AWE thing in Windows 2008 because I don't set the /PAE on any of my servers (and some of them are large transactional systems with more than 64GB of RAM) and haven't had any problems.
TheSQLGuru (8/10/2013)
In addition to the 32 bit SQL Server constraint, I also note that you are way behind on updates too. You are on base SP1 (2500) and the current build is SP2 CU8 (build 4286).
I'm one of those that likes to wait to install service packs unless they have a definite fix to something that is broken, not minor fixes to some obsure functionality. Been burned too many times by the Microsoft switcharoo when they release service packs (keeping the same version number for different binaries). Yes, SP2 has been out for more than a year, but I'm still gun-shy unless I see a definite need.
We did find the answer, though. The SQL instance was only using 5GB (I thought that was weird).
In the Task Manager, in the Performance tab, there is the Physical Memory (MB) area. In this area there is Total, Cached, Available, and Free. For some reason on this server the VM had 10GB cached. 16GB RAM with 10GB cached and SQL trying to take 12GB equals memory issues.
Rebooting resolved the issue, but the cached is starting to slowly creep back up. It's a VM issue, not a SQL issue.
...
August 13, 2013 at 3:44 pm
Edward Mlynar (8/12/2013)
Does this still matter in Windows 2008? I thought it was a Windows 2003 thing. It is using 5GB of RAM. I'm a little fuzzy on the whole AWE thing in Windows 2008 because I don't set the /PAE on any of my servers (and some of them are large transactional systems with more than 64GB of RAM) and haven't had any problems.
I read the version number wrong and thought you were using Win2008. So /PAE may not be needed.
But you definitely need to enable AWE in SQL Server, and the output from DBCC MEMORYSTATUS makes quite clear that you had not.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply