Error: 17803, Severity: 20, State: 14

Had a SQL Server go buck wild this morning. Here are some entries from the log:

2010-02-23 06:29:27.08 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:27.08 server Insufficient memory available..
2010-02-23 06:29:29.37 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:29.37 server Insufficient memory available..
2010-02-23 06:29:31.63 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:31.63 server Insufficient memory available..
2010-02-23 06:29:33.90 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:33.90 server Insufficient memory available..
2010-02-23 06:29:36.16 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:36.16 server Insufficient memory available..
2010-02-23 06:29:36.38 spid1 LazyWriter: warning, no free buffers found.
2010-02-23 06:29:36.38 spid1 Buffer Distribution: Stolen=1722 Free=0 Procedures=24
2010-02-23 06:29:36.38 spid1 Buffer Counts: Commited=2245 Target=2245 Hashed=499
2010-02-23 06:29:36.38 spid1 Procedure Cache: TotalProcs=6 TotalPages=24 InUsePages=24
2010-02-23 06:29:36.38 spid1 Dynamic Memory Manager: Stolen=1746 OS Reserved=504
2010-02-23 06:29:36.38 spid1 Global Memory Objects: Resource=692 Locks=41
2010-02-23 06:29:36.38 spid1 Query Memory Manager: Grants=0 Waiting=0 Maximum=333 Available=333
2010-02-23 06:29:38.43 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:38.43 server Insufficient memory available..
2010-02-23 06:29:40.71 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:40.71 server Insufficient memory available..
2010-02-23 06:29:42.98 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:42.98 server Insufficient memory available..
2010-02-23 06:29:45.26 server Error: 17803, Severity: 20, State: 14

I simply gave this Server a quick reboot. Apparently he server was starved for memory. Fortunately it was a DEV server so no production down time. I am working on getting our monitoring software updated. Once that is done I will be able to fire off alerts based on memory conditions. Should be good times.

AWE Shucks

AWE – Address Windowing Extensions. Honestly I have heard the term before, and I knew that the person/article stating the term was talking memory. So I set out to get a clearer understanding of what AWE meant and how it relates to SQL Server.

From M$ web site: Address Windowing Extensions (AWE) allow 32-bit operating systems to access large amounts of memory.

My first question from that is how? How does that work? Some more searching led me to this from Microsoft’s web site:

Microsoft SQL Server uses the Microsoft Windows Address Windowing Extensions (AWE) API to support very large amounts of physical memory. SQL Server can access up to 64 gigabytes (GB) of memory on Microsoft Windows 2000 Server and Microsoft Windows Server 2003.

So from that we know the following:
* AWE is a set of memory management extensions (API)
* Only applicable to 32bit Systems
* Will increase the amount of memory SQL Server can support

Standard 32-bit addresses can map a maximum of 4GB of memory. You can find several articles on the Boot.ini hack to override the windows default of 2GB to SQL, 2GB to OS. The override will get you another 1GB so that 3GB is dedicated to SQL Server while 1GB is reserved for the OS.

So yes you can use AWE to allow SQL server to allocate more memory, however the memory can only be used by SQL Server data buffers and cannot be used for things like caching query plans, sorting, indexing, user connections, etc. And best guess is that enabling AWE adds some overhead that you would not see in a pure 64bit installation. Before you utilize AWE, if the budget is there, and you need to address more than 3GB of RAM you may want to go the 64bit route.