SQL Server Performance Forum – Threads Archive
Problem with Memory?
For 2 weeks we started to have problem with one of our productions servers. We have med to changes during that time, I have started to use <br />Log Shipping and we upgraded the calling web server to asp.net 2.0 version.<br /><br />The server is a SQL Server 9.0.1399 X64 bit running on a <br /><br />1st Itanium 2 1.6 GHz (Cache 6 MB)<br />4 GB DDR memory quad (1 x 1GB)<br />HP Smart Array 6402/128MB Slot 1, HP Smart Array 6402/128MB Slot 8<br /><br />+ Smart Array 6400 Controller in Slot 1<br />-Parallel SCSI Array A<br />oLogical Drive 1 ( 34727 MB, RAID 1+0 ) – Windows<br />+ Smart Array 6400 Controller in Slot 8<br />-Parallel SCSI Array A<br />oLogical Drive 1 (104183 MB, RAID 1+0 ) – Database<br />-Parallel SCSI Array B – Logs<br />oLogical Drive 1 (104183 MB, RAID 1+0 )<br />-Parallel SCSI Array C <br />oLogical Drive 3 (104183 MB, RAID 5 ) – Bakups<br /><br />This full time SQL Server no other programs is using the server. <br /><br />I found these line “LazyWriter: warning, no free buffers found.†In the errorlog file. This has happened to use 4 times. And I takes 2 days <br />before the computer hung and the mssqlserver processor is tanke 100% cpu time. <br /><br />I have really now ide was is happening when we are not able in any way to connect to the server. Not even with this new admin<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />erver line. <br /><br />DBCC MEMORYSTATUS<br /><br />Memory Manager KB <br />—————————— ——————–<br />VM Reserved 4258616<br />VM Committed 1153904<br />AWE Allocated 0<br />Reserved Memory 1024<br />Reserved Memory In Use 0<br /><br />(5 row(s) affected)<br /><br />Memory node Id = 0 KB <br />—————————— ——————–<br />VM Reserved 4252984<br />VM Committed 1148352<br />AWE Allocated 0<br />MultiPage Allocator 19024<br />SinglePage Allocator 390176<br /><br />(5 row(s) affected)<br /><br />MEMORYCLERK_SQLGENERAL (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 6336<br /> MultiPage Allocator 4040<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLBUFFERPOOL (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 4214784<br /> VM Committed 1110784<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 0<br /> MultiPage Allocator 8<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLOPTIMIZER (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 488<br /> MultiPage Allocator 96<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLUTILITIES (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 120<br /> VM Committed 120<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 144<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLSTORENG (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 1728<br /> VM Committed 1728<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 1160<br /> MultiPage Allocator 720<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 872<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLCLR (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLSERVICEBROKER (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 80<br /> MultiPage Allocator 304<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLHTTP (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SNI (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 72<br /> MultiPage Allocator 16<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_FULLTEXT (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 16<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLXP (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 16<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_HOST (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 16<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SOSNODE (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 4856<br /> MultiPage Allocator 11632<br /><br />(7 row(s) affected)<br /><br />MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 48<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_OBJCP (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 209408<br /> MultiPage Allocator 1088<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_SQLCP (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 135312<br /> MultiPage Allocator 784<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_PHDR (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 2224<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_XPROC (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 88<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_TEMPTABLES (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 136<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_NOTIF (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 16<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_VIEWDEFINITIONS (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 16<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_XMLDBTYPE (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_XMLDBELEMENT (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_XMLDBATTRIBUTE (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_STACKFRAMES (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 0<br /> MultiPage Allocator 8<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_BROKERTBLACS (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 192<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_BROKERKEK (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_BROKERDSH (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_BROKERRSB (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_BROKERREADONLY (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 72<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_BROKERTO (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 8<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_EVENTS (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 16<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />CACHESTORE_SYSTEMROWSET (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 2264<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />USERSTORE_SCHEMAMGR (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 2048<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />USERSTORE_DBMETADATA (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 3400<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />USERSTORE_TOKENPERM (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 17696<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />USERSTORE_OBJPERM (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 440<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />USERSTORE_SXC (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 336<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />OBJECTSTORE_LBSS (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 16<br /> MultiPage Allocator 96<br /><br />(7 row(s) affected)<br /><br />OBJECTSTORE_SNI_PACKET (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 1344<br /> MultiPage Allocator 48<br /><br />(7 row(s) affected)<br /><br />OBJECTSTORE_SERVICE_BROKER (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 0<br /> VM Committed 0<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 272<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />OBJECTSTORE_LOCK_MANAGER (Total) KB <br />—————————————————————- ——————–<br /> VM Reserved 16384<br /> VM Committed 16384<br /> AWE Allocated 0<br /> SM Reserved 0<br /> SM Commited 0<br /> SinglePage Allocator 648<br /> MultiPage Allocator 0<br /><br />(7 row(s) affected)<br /><br />Buffer Distribution Buffers<br />—————————— ———–<br />Stolen 1772<br />Free 12105<br />Cached 47000<br />Database (clean) 73159<br />Database (dirty) 2124<br />I/O 0<br />Latched 0<br /><br />(7 row(s) affected)<br /><br />Buffer Counts Buffers<br />—————————— ——————–<br />Committed 136160<br />Target 377256<br />Hashed 75283<br />Stolen Potential 447733<br />External Reservation 0<br />Min Free 32<br />Visible 377256<br />Available Paging File 1564802<br /><br />(8 row(s) affected)<br /><br />Procedure Cache Value<br />—————————— ———–<br />TotalProcs 1261<br />TotalPages 43613<br />InUsePages 136<br /><br />(3 row(s) affected)<br /><br /> <br />Global Memory Objects Buffers<br />—————————— ——————–<br />Resource 293<br />Locks 84<br />XDES 28<br />SETLS 1<br />SE Dataset Allocators 2<br />SubpDesc Allocators 1<br />SE SchemaManager 255<br />SQLCache 790<br />Replication 2<br />ServerGlobal 48<br />XP Global 2<br />SortTables 35<br /><br />(12 row(s) affected)<br /><br /> <br />Query Memory Objects Value<br />—————————— ———–<br />Grants 0<br />Waiting 0<br />Available (Buffers) 263500<br />Maximum (Buffers) 263500<br />Limit 263500<br />Next Request 0<br />Waiting For 0<br />Cost 0<br />Timeout 0<br />Wait Time 0<br />Last Target 276300<br /><br />(11 row(s) affected)<br /><br />Small Query Memory Objects Value<br />—————————— ———–<br />Grants 0<br />Waiting 0<br />Available (Buffers) 12800<br />Maximum (Buffers) 12800<br />Limit 12800<br /><br />(5 row(s) affected)<br /><br />Optimization Queue Value<br />—————————— ——————–<br />Overall Memory 2414387200<br />Target Memory 2165514240<br />Last Notification 1<br />Timeout 6<br />Early Termination Factor 5<br /><br />(5 row(s) affected)<br /><br />Small Gateway Value<br />—————————— ——————–<br />Configured Units 4<br />Available Units 4<br />Acquires 0<br />Waiters 0<br />Threshold Factor 500000<br />Threshold 500000<br /><br />(6 row(s) affected)<br /><br />Medium Gateway Value<br />—————————— ——————–<br />Configured Units 1<br />Avail able Units 1<br />Acquires 0<br />Waiters 0<br />Threshold Factor 12<br /><br />(5 row(s) affected)<br /><br />Big Gateway Value<br />—————————— ——————–<br />Configured Units 1<br />Available Units 1<br />Acquires 0<br />Waiters 0<br />Threshold Factor 8<br /><br />(5 row(s) affected)<br /><br />MEMORYBROKER_FOR_CACHE Value<br />——————————– ——————–<br />Allocations 47002<br />Rate 34<br />Target Allocations 294725<br />Future Allocations 0<br />Last Notification 1<br /><br />(5 row(s) affected)<br /><br />MEMORYBROKER_FOR_STEAL Value<br />——————————– ——————–<br />Allocations 1766<br />Rate 0<br />Target Allocations 264345<br />Future Allocations 0<br />Last Notification 1<br /><br />(5 row(s) affected)<br /><br />MEMORYBROKER_FOR_RESERVE Value<br />——————————– ——————–<br />Allocations 0<br />Rate 0<br />Target Allocations 294725<br />Future Allocations 65875<br />Last Notification 1<br /><br />Sp_configure<br />name minimum maximum config_value run_value<br />———————————– ———– ———– ———— ———–<br />allow updates 0 1 0 0<br />clr enabled 0 1 1 1<br />cross db ownership chaining 0 1 0 0<br />default language 0 9999 0 0<br />max text repl size (B) 0 2147483647 65536 65536<br />nested triggers 0 1 1 1<br />remote access 0 1 1 1<br />remote admin connections 0 1 1 1<br />remote login timeout (s) 0 2147483647 20 20<br />remote proc trans 0 1 0 0<br />remote query timeout (s) 0 2147483647 600 600<br />server trigger recursion 0 1 1 1<br />show advanced options 0 1 0 0<br />user options 0 32767 0 0<br /><br />Error Log Last Lines<br />2006-03-24 09:30:06.87 Backup Log was backed up. Database: BPROJCT, creation date(time): 2006/02/12(11:35:37), first LSN: 17471:20266:1, last LSN: 17472:772:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘F:ackupLog ShippingBPROJCTBPROJCT_20060324083005.trn’}). This is an informational message only. No user action is required.<br />2006-03-24 09:36:39.73 spid2s LazyWriter: warning, no free buffers found.<br />2006-03-24 09:36:40.28 spid2s <br />Memory Manager <br />VM Reserved = 4295800 KB<br />VM Committed = 4276184 KB <br />AWE Allocated = 0 KB <br />Reserved Memory = 1024 KB <br />Reserved Memory In Use = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />Memory node Id = 0 <br />VM Reserved = 4290104 KB <br />VM Committed = 4270568 KB <br />AWE Allocated = 0 KB <br />SinglePage Allocator = 1821352 KB<br />MultiPage Allocator = 35968 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLGENERAL (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 49312 KB<br />MultiPage Allocator = 4424 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLBUFFERPOOL (Total) <br />VM Reserved = 4214784 KB <br />VM Committed = 4198400 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 0 KB<br />MultiPage Allocator = 8 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLOPTIMIZER (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 720 KB<br />MultiPage Allocator = 96 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLUTILITIES (Total) <br />VM Reserved = 120 KB <br />VM Committed = 120 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 128 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLSTORENG (Total) <br />VM Reserved = 19328 KB <br />VM Committed = 19328 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 19056 KB<br />MultiPage Allocator = 12672 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLCONNECTIONPOOL (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 536 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLCLR (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLSERVICEBROKER (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 80 KB<br />MultiPage Allocator = 304 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLHTTP (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SNI (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 48 KB<br />MultiPage Allocator = 16 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_FULLTEXT (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 16 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLXP (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 16 KB<br />Mul tiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLQERESERVATIONS (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 1024 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_HOST (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 96 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SOSNODE (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 19864 KB<br />MultiPage Allocator = 11648 KB <br />2006-03-24 09:36:40.28 spid2s <br />MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 48 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_OBJCP (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 474816 KB<br />MultiPage Allocator = 3712 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_SQLCP (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 1165400 KB<br />MultiPage Allocator = 2736 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_PHDR (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 25656 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_XPROC (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 112 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_TEMPTABLES (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 176 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_NOTIF (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 16 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_VIEWDEFINITIONS (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 16 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_XMLDBTYPE (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_XMLDBELEMENT (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_XMLDBATTRIBUTE (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_STACKFRAMES (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 0 KB<br />MultiPage Allocator = 8 KB <br />2006-03-24 09:36:40.28 spid2s <br />CACHESTORE_BROKERTBLACS (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 96 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />CACHESTORE_BROKERKEK (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />CACHESTORE_BROKERDSH (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />CACHESTORE_BROKERUSERCERTLOOKUP (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />CACHESTORE_BROKERRSB (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />CACHESTORE_BROKERREADONLY (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 32 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />CACHESTORE_BROKERTO (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 8 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />CACHESTORE_EVENTS (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 16 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />CACHESTORE_SYSTEMROWSET (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 1944 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />USERSTORE_SCHEMAMGR (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 4032 KB<br />MultiPage A llocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />USERSTORE_DBMETADATA (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 6392 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />USERSTORE_TOKENPERM (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 47480 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />USERSTORE_OBJPERM (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 488 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />USERSTORE_SXC (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 176 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />OBJECTSTORE_LBSS (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 16 KB<br />MultiPage Allocator = 112 KB <br />2006-03-24 09:36:40.29 spid2s <br />OBJECTSTORE_SNI_PACKET (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 1488 KB<br />MultiPage Allocator = 48 KB <br />2006-03-24 09:36:40.29 spid2s <br />OBJECTSTORE_SERVICE_BROKER (Total) <br />VM Reserved = 0 KB <br />VM Committed = 0 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 272 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s <br />OBJECTSTORE_LOCK_MANAGER (Total) <br />VM Reserved = 16384 KB <br />VM Committed = 16384 KB <br />AWE Allocated = 0 KB <br />SM Reserved = 0 KB <br />SM Committed = 0 KB<br />SinglePage Allocator = 2592 KB<br />MultiPage Allocator = 0 KB <br />2006-03-24 09:36:40.29 spid2s Buffer Distribution: Stolen=12469 Free=0 Cached=215200<br />Database (clean)=285449 Database (dirty)=2977<br />I/O=0, Latched=1 <br />2006-03-24 09:36:40.29 spid2s Buffer Counts: Committed=516096 Target=522637 Hashed=288427<br />Internal Reservation=104527 External Reservation=128<br />Stolen Potential=268708<br /> Min Free=32 Visible=522637<br /> Available Paging File=9603375104<br />2006-03-24 09:36:40.29 spid2s Procedure Cache: TotalProcs=6148 TotalPages=209054 InUsePages=521 <br />2006-03-24 09:36:40.29 spid2s Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0 <br />2006-03-24 09:36:40.29 spid2s Global Memory Objects: <br />Resource=369<br />Locks=327<br />SE Schema Mgr=503<br />SQLCache=6125<br />Replication=2<br />ServerGlobal=48<br />XPGlobal=2<br />Xact=24<br />SETLS=1<br />DatasetMemObjs=2<br />SubpDescPmos=1<br />SortTables=1524 <br />2006-03-24 09:36:40.29 spid2s Query Memory Objects: Grants=1 Waiting=0 Maximum=262210 Available=262082 Limit=263725 <br />2006-03-24 09:36:40.29 spid2s Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=276525 <br />2006-03-24 09:36:40.29 spid2s Small Query Memory Objects: Grants=0 Waiting=0 Maximum=12800 Available=12800 Limit=12800 <br />2006-03-24 09:36:40.29 spid2s <br />Optimization Queue <br />Overall Memory = 3344793600 Target Memory = 1760739328 Last Notification = GROW Timeout = 6 Early Termination Factor = 5 <br />2006-03-24 09:36:40.30 spid2s <br />Small Gateway <br />Configured Units = 4 Available Units = 3 Acquires = 1 <br />Waiters = 0 Threshold Factor = 500000 Threshold = 500000 <br />2006-03-24 09:36:40.30 spid2s <br />Medium Gateway <br />Configured Units = 1 Available Units = 1 Acquires = 0 <br />Waiters = 0 Threshold Factor = 12 Threshold = 146728277 <br />2006-03-24 09:36:40.30 spid2s <br />Big Gateway <br />Configured Units = 1 Available Units = 1 Acquires = 0 <br />Waiters = 0 Threshold Factor = 8 Threshold = -1 <br />2006-03-24 09:36:40.30 spid2s <br />MEMORYBROKER_FOR_CACHE <br />Allocations = 215211 <br />Rate = 0 <br />Target Allocations = 408300 <br />Future Allocations = 0 <br />Last Notification = GROW <br />2006-03-24 09:36:40.30 spid2s <br />MEMORYBROKER_FOR_STEAL <br />Allocations = 12458 <br />Rate = 0 <br />Target Allocations = 214934 <br />Future Allocations = 0 <br />Last Notification = GROW <br />2006-03-24 09:36:40.30 spid2s <br />MEMORYBROKER_FOR_RESERVE <br />Allocations = 128 <br />Rate = 0 <br />Target Allocations = 294968 <br />Future Allocations = 92492 <br />Last Notification = GROW <br />2006-03-24 09:36:53.79 Server Using ‘dbghelp.dll’ version ‘4.0.5’<br />2006-03-24 09:36:55.18 Server **Dump thread – spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000<br />2006-03-24 09:36:59.13 Server ***Stack Dump being sent to C
rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump0005.txt<br />2006-03-24 09:37:03.13 Server * *******************************************************************************<br />2006-03-24 09:37:03.13 Server *<br />2006-03-24 09:37:03.13 Server * BEGIN STACK DUMP:<br />2006-03-24 09:37:03.13 Server * 03/24/06 09:36:59 spid 0<br />2006-03-24 09:37:03.13 Server *<br />2006-03-24 09:37:03.13 Server * Non-yielding IOCP Listener<br />2006-03-24 09:37:03.13 Server *<br />2006-03-24 09:37:03.13 Server * *******************************************************************************<br />2006-03-24 09:37:03.13 Server * ——————————————————————————-<br />2006-03-24 09:37:03.13 Server * Short Stack Dump<br />2006-03-24 09:37:12.25 Server Stack Signature for the dump is 0x000000000000019E<br />2006-03-24 09:37:46.97 Server External dump process return code 0x20000001.<br />External dump process returned no errors. <br /><br /><br />
Probably a windows based hotfix needed or check latest service pack has been applied. As usual the SQL hotfixes would take care of those lazy writer issues but SQL 2005 there is no hotfix or service pack until of April 2006. If the problems persist then MS product support would help to check and suggest a work around. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I realize this isn’t your version of sql but the message was a match so I thought I’d post it: http://support.microsoft.com/kb/891719 Good luck, memory issues are a bear to troubleshoot. David
]]>