Urgent -- Production (IOCP Listener )

HI all,
This is 32-bit SQL Server Entp on 64-bit Windows 2003 Entp.on X64 box.
Before we were getting some warnings : ,spid1s,Unknown,A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 461120<c/> committed (KB): 3631376<c/> memory utilization: %.
So, we made some changes to the system:
(1) Enable Lock pages in memory group policy for the sql server startup account.
(2) Next day, we enabled the AWE from SQL Server side & set max memory to 14 GB ( Total = 16 GB).
Everything went fine for a week, then our windows admin made change in page file size ( virtual memory) on c,d,f drives.
Then suddenly after 2 hours, sql server became unresponsive, & following is the dump file which says the error.
=====================================================================
BugCheck Dump
=====================================================================

This file is generated by Microsoft SQL Server
version 9.00.3186.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.


Computer type is AT/AT COMPATIBLE.
Current time is 06:50:49 01/03/08.
8 Intel x86 level 15, 3392 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 2.

Memory
MemoryLoad = 90%
Total Physical = 16382 MB
Available Physical = 1560 MB
Total Page File = 21917 MB
Available Page File = 6867 MB
Total Virtual = 4095 MB
Available Virtual = 928 MB
**Dump thread - spid = 0, PSS = 0x00000000, EC = 0x00000000
***Stack Dump being sent to Crogram Files (x86)Microsoft SQL ServerMSSQL.1MSSQLLOGSQLDump0003.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 01/03/08 06:52:07 spid 0
*
* Non-yielding IOCP Listener
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
******************************************* Then , we rebooted the box, & system came up, then we rollback the changes made to page file size ( virtual memory). Then we opened the ticket with microsoft, they also suggested that
(1) Enable Lock pages in memory group policy for the sql server startup account.
(2) Enable the AWE from SQL Server side & they said to set max memory to 10 Gb.( We havent done that yet, we were supposed to do that tonight, {Now max memory is set to Defaults( not 14 Gb).},<<<<<< Suddenly this afternoon, system went down again , with the same error:
Non-yielding IOCP Listener --- Microsoft didnt have much info on this.
Based on your experinece with sql server ( 32-bit sql on 64-bit OS), can you suggest what can causing this issue & how we can avoid this happeinig again.
For now: we are going to disable AWE, set memory to defaults, & keep the lock pages memory group policy ON with sql server start up account.
Please reply as early as possible.
Thanks.

Hi the proplem you are experiencing is described in the following article
http://support.microsoft.com/kb/918483
also you may want to look at the following article http://support.microsoft.com/kb/941689
which claims that the Non-yielding IOCP Listener was fixed in the cumlative service pack 4 (you need to contact ms for that)
Kevin Brennan M.Sc MCDBA MCSE MCP+I

Both times got the same sql dump info/error or different...
As per the MS article (http://support.microsoft.com/kb/941689) mentioned by Kevin, you have to apply Cumulative Update 4 for SP2 which is Build 09.00.3200 (cumulative update)
where as you are on Cumulative Update 3 for SP2 Build 09.00.3186 (cumulative update)
Try applying latest or CU OR CU-4...

Yes, we got the same dump both the times.
Actually, we are little bit reluctant to with Cumulative update 4 (or) 5, because there is known issure of some Security( on Clustering) for X64 SQL Server & Windows OS ( Actually, We are planning to on Clustering in the near future, so thats stopping us to apply this Cumualtive update)
If we turned AWE off, & lock pages memroy is enabled for SQL Service startup account.... What should be the max server memory in this case.
( Becasue , i think when we set lock page memory for SQL , its tries to catch around 14.5 GB of RAM ( as seen in Task Manger) & remaining is there for other like OS). .............. Does this starving the OS. & hanging the Sql Server ...
Thanks .

Leave 2 GB for OS and rest can be configured for SQL server but if I am not mistaken you have to enable AWE to take the advantage for 32 bit sql.
Note: Use perfmon counters for TOTAL SERVER MEMORY and TARGET SERVER MEMORY in SQL SERVER: Memory Manager to check how much SQL is using the memory...
Read Slavo's blog for memory related questions...
http://blogs.msdn.com/slavao/archiv...server-always-respond-to-memory-pressure.aspx

Leave the lock pages in memory setting and probably AWE as well all you have to do is set the max and min sql server memory settings to the same value this value should be as suggested 2GB below your installed RAM value for example is you have 16GB of RAM installed set MIN and MAX to 14GB. This will cap sql server at a fixed value in this case 14GB if you find that you server
By monitoring the total server memory in perf mon you will get an indication of how much of both ram and page memory sql is using if this figure turns out to be more than the installed value for example say we found that total server memory was 30GB and we only had 16GB installed we would be sure that we had a large shortfall and would indicate that you need to install more ram.
If you find that you working set size is below the now capped allocaation for example say you find that the working set size is
10 GB the and you have allocated 14GB then you have over allocated memory to sql server and can reduce the fixed cap to 10 or 11 GB