Sql-server – 32-bit DLL on SQL Server 2008 x64

x86 processes with the LargeAddressAware bit set (like sqlservr.exe) get a full 4 GB VAS for themselves. Enabling AWE (on OS and SQL editions that support it) allows the SQL server instance to map extra pages (up to 64GB) in and out of its VAS and use them for the buffer pool.

That being said, x86 is a dead end and you should switch to an x64 instance ASAP.

• Best Practices for WOW64
• Memory Architecture
• Q and A: x64 & AWE mechanism

I had a similar problem. I needed to delete a couple million old records. The delete failed after about 30 minutes because the available space for the logfile was too small. We solved it by modifying the delete statement to delete only 500,000 records at a time. This solved the problem.

To apply that knowledge to your case. Can you run a few smaller updates instead of one big one? This can be done by explicitly adding 'begin transaction' and 'commit' statements in your stored procedure. In addition to statements at beginning and end of the stored procedure, you can issue a commit after a million lines and start a new transaction than. I wouldn't do a commit after every update since this will slow down performance greatly. Another option would be to limit the stored procedure to a certain amount and call it several times or create similar stored procedures that work on different parts of the data.

Since you are not using explicitly the transaction boundaries, you might have some luck setting the transaction isolation level to uncommitted reads. See here. I think it might speed up the processing, but I don't expect it to have a major impact on the transaction log. Be aware that you enable dirty reads by setting the isolation level to uncommitted reads.