October 7, 2009 at 8:57 am
Been struggling with the error below for some time now and was hoping somebody could provide some other ideas on cause/resolution:
Any suggestions would be appreciated.
The error below appears when trying to create an index on a clustered table with 266 million rows.
Msg 701, Level 17, State 123, Server WINPRISMNJDEV03, Line 1 There is insufficient system memory in resource pool 'default' to run this query
The machine has 16gb of memory and 16 cpus. Windows Enterprise 32 bit, SQL Server Enterprise 32 bit.
Max server memory is set to 11 gb.
AWE is enabled
PAE is enabled
Below is the CREATE INDEX statement:
CREATE NONCLUSTERED INDEX [IX_tbl_field1_field2_field3_field4]
ON [dbo].[tbl1] ([field1],[field2],[field3],[field4])
INCLUDE ([field5],[field6],[field7]) WITH (ONLINE=ON)
Below is a dump of DBCC MEMORYSTATUS that appears in the error log:
2009-10-06 16:11:42.37 spid51 Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2009-10-06 16:11:42.39 spid51
Memory Manager KB
---------------------------------------- ----------
VM Reserved 1712224
VM Committed 252000
AWE Allocated 11264000
Reserved Memory 1024
Reserved Memory In Use 0
2009-10-06 16:11:42.39 spid51
Memory node Id = 0 KB
---------------------------------------- ----------
VM Reserved 126720
VM Committed 111856
AWE Allocated 0
MultiPage Allocator 110040
SinglePage Allocator 347752
2009-10-06 16:11:42.39 spid51
Memory node Id = 1 KB
---------------------------------------- ----------
VM Reserved 1582688
VM Committed 137436
AWE Allocated 11264000
MultiPage Allocator 12336
SinglePage Allocator 347752
2009-10-06 16:11:42.39 spid51
Memory node Id = 32 KB
---------------------------------------- ----------
VM Reserved 1728
VM Committed 1672
AWE Allocated 0
MultiPage Allocator 1600
SinglePage Allocator 347752
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1744
MultiPage Allocator 2360
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLGENERAL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1752
MultiPage Allocator 2360
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 1558584
VM Committed 114332
AWE Allocated 11264000
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 3728
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 168
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLQUERYEXEC (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 302992
MultiPage Allocator 103584
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLQUERYEXEC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 303160
MultiPage Allocator 103584
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 120
MultiPage Allocator 88
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- ----------
VM Reserved 360
VM Committed 360
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 128
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- ----------
VM Reserved 2880
VM Committed 2880
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2776
MultiPage Allocator 176
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLSTORENG (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 40
MultiPage Allocator 2816
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------- ----------
VM Reserved 2880
VM Committed 2880
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2816
MultiPage Allocator 2992
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 264
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 192
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 456
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 120
MultiPage Allocator 344
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLSERVICEBROKER (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 136
MultiPage Allocator 344
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 16
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SNI (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 16
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
MEMORYCLERK_SNI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 48
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1014600
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 2816
VM Committed 2816
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_XE_BUFFER (node 1) KB
---------------------------------------- ----------
VM Reserved 1408
VM Committed 1408
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_XE_BUFFER (Total) KB
---------------------------------------- ----------
VM Reserved 4224
VM Committed 4224
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_HOST (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2472
MultiPage Allocator 5264
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_SOSNODE (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 13176
MultiPage Allocator 2792
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 15664
MultiPage Allocator 9576
2009-10-06 16:11:42.39 spid51
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 384
SM Committed 384
SinglePage Allocator 0
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 48
MultiPage Allocator 96
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 336
MultiPage Allocator 200
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
CACHESTORE_TEMPTABLES (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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
CACHESTORE_STACKFRAMES (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 24
2009-10-06 16:11:42.39 spid51
CACHESTORE_BROKERTBLACS (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-10-06 16:11:42.39 spid51
CACHESTORE_BROKERTBLACS (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 56
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 144
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
CACHESTORE_CONVPRI (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-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 15752
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 304
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 208
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
USERSTORE_TOKENPERM (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
USERSTORE_TOKENPERM (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 232
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1768
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
USERSTORE_SXC (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-10-06 16:11:42.39 spid51
USERSTORE_SXC (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 40
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 96
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 80
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
OBJECTSTORE_LBSS (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 96
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
OBJECTSTORE_LBSS (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 176
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 184
MultiPage Allocator 56
2009-10-06 16:11:42.39 spid51
OBJECTSTORE_SNI_PACKET (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 208
MultiPage Allocator 56
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 392
MultiPage Allocator 168
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1320
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
OBJECTSTORE_LOCK_MANAGER (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2016
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
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-10-06 16:11:42.39 spid51
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3360
MultiPage Allocator 0
2009-10-06 16:11:42.39 spid51
OBJECTSTORE_SECAUDIT_EVENT_BUFFER (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-10-06 16:11:42.48 spid51
Buffer Pool Value
---------------------------------------- ----------
Committed 1408000
Target 1408000
Database 1364063
Dirty 1008320
In IO 0
Latched 1
Free 435
Stolen 43502
Reserved 126792
Visible 177664
Stolen Potential -1513
Limiting Factor 10
Last OOM Factor 0
Page Life Expectancy 1244
2009-10-06 16:11:42.48 spid51
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 4743221248
Available Virtual Memory 240627712
Available Paging File 17547272192
Working Set 187084800
Percent of Committed Memory in WS 100
Page Faults 53435
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2009-10-06 16:11:42.48 spid51
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 1
TotalPages 71
InUsePages 3
2009-10-06 16:11:42.48 spid51
Global Memory Objects Pages
---------------------------------------- ----------
Resource 198
Locks 420
XDES 69
SETLS 16
SE Dataset Allocators 32
SubpDesc Allocators 16
SE SchemaManager 313
SE Column Metadata Cache 1655
SQLCache 39
Replication 2
ServerGlobal 26
XP Global 2
SortTables 2
2009-10-06 16:11:42.48 spid51
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 3530
Current Max 3530
Future Max 3530
Physical Max 132860
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2009-10-06 16:11:42.48 spid51
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 640
Current Max 640
Future Max 640
2009-10-06 16:11:42.48 spid51
Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 1
Waiting 0
Available 0
Current Max 126825
Future Max 86811
Physical Max 128931
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2009-10-06 16:11:42.48 spid51
Small Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 4569
Current Max 4569
Future Max 4569
2009-10-06 16:11:42.48 spid51
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 1166606336
Target Memory 36454400
Last Notification 0
Timeout 6
Early Termination Factor 5
2009-10-06 16:11:42.48 spid51
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
2009-10-06 16:11:42.48 spid51
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
2009-10-06 16:11:42.48 spid51
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2009-10-06 16:11:42.48 spid51
Optimization Queue (default) Value
---------------------------------------- ----------
Overall Memory 1166606336
Target Memory 324386816
Last Notification 0
Timeout 6
Early Termination Factor 5
2009-10-06 16:11:42.48 spid51
Small Gateway (default) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
2009-10-06 16:11:42.48 spid51
Medium Gateway (default) Value
---------------------------------------- ----------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
2009-10-06 16:11:42.48 spid51
Big Gateway (default) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2009-10-06 16:11:42.48 spid51
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 126825
Reserved Limit 126825
2009-10-06 16:11:42.48 spid51
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 1514
Predicted 1534
Private Target 0
Private Limit 0
Total Target 168780
Total Limit 168780
OOM Count 0
2009-10-06 16:11:42.48 spid51
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 557
Rate 20
Target Allocations 4450
Future Allocations 0
Overall 142408
Last Notification 0
2009-10-06 16:11:42.48 spid51
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 957
Rate 0
Target Allocations 4450
Future Allocations 0
Overall 142408
Last Notification 0
2009-10-06 16:11:42.48 spid51
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 4450
Future Allocations 0
Overall 142408
Last Notification 0
2009-10-06 16:11:42.48 spid51
Memory Pool (default) Pages
---------------------------------------- ----------
Allocations 168780
Predicted 183719
Private Target 0
Private Limit 0
Total Target 168780
Total Limit 168780
OOM Count 1
2009-10-06 16:11:42.48 spid51
MEMORYBROKER_FOR_CACHE (default) Pages
---------------------------------------- ----------
Allocations 2357
Rate 0
Target Allocations 4450
Future Allocations 0
Overall 142408
Last Notification 0
2009-10-06 16:11:42.48 spid51
MEMORYBROKER_FOR_STEAL (default) Pages
---------------------------------------- ----------
Allocations 39598
Rate 14939
Target Allocations 39598
Future Allocations 0
Overall 142408
Last Notification 0
2009-10-06 16:11:42.48 spid51
MEMORYBROKER_FOR_RESERVE (default) Pages
---------------------------------------- ----------
Allocations 126825
Rate 0
Target Allocations 97486
Future Allocations 0
Overall 142408
Last Notification 2
October 7, 2009 at 9:13 am
I would take a stab in the dark and say change the -g startup switch to 384, i.e -g384
i think that in sql 2008 it is defaulted to 256MB, this is to increase the amount of unreserved virtual memory
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 9:18 am
Yep. Was hoping you guys weren't going to say that.
Thanks
Any other ideas?
October 7, 2009 at 9:48 am
gravelm (10/7/2009)
Yep. Was hoping you guys weren't going to say that.Thanks
Any other ideas?
roflmao, So i take it you already know that option, the only other option I can think of is freeing up all the cache, but that is as drastic solution as restarting with the -g switch.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 9:55 am
Yep. Tried the cache too.
The amount of time I've spent on this I could have upgraded to 64bit.
Any other ideas?
Please....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply