March 15, 2010 at 10:13 pm
Hi All,
I'm fairly new to DBA'ing and I've come accross an issue on a clients system where they are having to reboot the database server every few weeks as they are getting numerous 701 errors (There is insufficient memory to run this query) followed by an unresponsive system.
From what I have read it seems related to MemToLeave but there are various other methods suggested to improve memory related issues (AWE, /3GB, etc.) but non were for the combination of versions involved in this problem: SQL Server Standard (32-bit) and Windows Server 2008 Standard (32-bit).
I'm hoping my gratitude will be enough to encourage someone to shed some light on this HF!
The system details are as follows...
Operating System:
Windows 2008 Server Standard 6.0.6002 Service Pack 2 Build 6002
Intel Xeon CPU E5345 @ 2.33GHz, 2336 MHz, 1 Core, 1 Logical Processor
Hardware Abstraction Layer Version 6.0.6002.18005
Installed Physical Memory 4.00Gb
Total Physical Memory 3.75 Gb
Available Physical Memory 1.45 Gb
Total Virtual Memory 7.67 Gb
Available Virtual Memory 5.48 Gb
Page File Space 4.04 Gb (C:\pagefile.sys)
SQL Server 2005:
SQL Server Standard Edition 9.00.4053.00 (I believe this corresponds to Service pack 3 GDR release)
March 29, 2010 at 7:13 pm
wow, this must be even more complicated than I thought as there have been 0 replies in the two weeks since I posted!!
March 30, 2010 at 1:32 am
Before upgrading the server for our most productive client and put it on 64 and Windows 2008 R2, I have had the same problem, but, unfortunately, my OS was Microsoft Windows Server 2003 SP2 32-bit. In that case, the 3GB/AWE configuration worked just fine 🙁
I can't believe you haven't found anything regarding your configuration.
In case I'll find out something I'll let you know.
Wish you good ideas! 🙂
Andreea
March 30, 2010 at 10:58 pm
I had a client who was having the exact same issue. I spent weeks troubleshooting and changing settings to no avail. I recently upgraded them to SQL 64-bit and all issues are gone. Probably not the answer you are looking for but at least your not alone.
March 31, 2010 at 1:06 am
Thanks for both replies and yes those are exactly the answers I'm looking for because I think they should have gone with 64-bit in the first place. Luckily they're not my direct clients, I've just been dragged in as the company "guru" so I've pointed them off down the AWE route for now.
March 31, 2010 at 1:20 am
If the server only has 4Gb of RAM then 64-bit won't provide much of an advantage...it'll be able to utilise a little bit more of the available RAM, but that's about it.
March 31, 2010 at 1:35 pm
True, but 64-bit can take advantage of as much RAM as we can throw at the problem and as far as I'm aware 32-bit cannot.
64-bit would either
a) solve the issue
b) take advantage of lots more RAM to buy us time to investigate without the regular system crashes
April 1, 2010 at 7:09 am
Are there any CLR procedures on the server? If so it's possible that one of those has a memory leak. Have you tried monitoring the available RAM over the few weeks it's up? When the amount of available RAM is decreasing may give some insight into what's going on.
April 1, 2010 at 11:47 am
have you looked at the query to see if it can be optimized, indexes created, changed, etc? or how much data it returns?
with 32 bit you are limited to 4GB for the process and anything above 4GB is just for caching execution plans. in reality with OS overhead you will get less than 4GB of RAM to run the query.
we went to 64 bit a few years ago and queries that ran for hours started to run in less than a minute. now every new or replacement SQL server we set up is 64 bit.
April 2, 2010 at 3:37 pm
Hi,
AWE and /3GB may bring you some extra memory for the cache but this won't solve the problem if the server is very busy. It will be a matter of time till the problem comes back.
Just analyse the database and it's use to find out which server you really need for that database.
I'm sure you'll end up with a 64 bit system and more than 4 GB RAM. Sizing the Database server is where you have to start because the system is already up and running. You can later on try to optimize the queries and all the other stuff.
Regards
Richellère
April 3, 2010 at 3:31 am
paul.knibbs (3/31/2010)
If the server only has 4Gb of RAM then 64-bit won't provide much of an advantage...it'll be able to utilise a little bit more of the available RAM, but that's about it.
On 32-bit SQL Server, virtual address space (VAS) is frequently more of a problem than physical memory. By default, each process running on 32-bit Windows has a maximum user address space of 2GB (the top 2GB of virtual addresses is reserved for Windows, and shared across all processes).
64-bit SQL Servers have access to 8TB of virtual addresses (7TB on Itanium systems). This gives SQL Server much more room to work, so there are significant advantages in moving to 64-bit even for servers with less than 4GB physical memory.
If that seems strange, consider that SQL Server must squish everything into 2GB of address space on 32-bit. That includes addresses for the SQL Server executables themselves, thread stacks, multi-page allocator ('memtoleave'), buffer pool (data and procedure cache), memory-mapped files...absolutely everything must fit in 2GB of VAS.
Enabling AWE is pretty much essential on 32-bit Windows, even for servers with less than 4GB RAM. See http://www2.sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx - the results and graph there show that AWE can be useful for servers with only 1.5GB of memory!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 3:39 am
alen teplitsky (4/1/2010)
with 32 bit you are limited to 4GB for the process and anything above 4GB is just for caching execution plans. in reality with OS overhead you will get less than 4GB of RAM to run the query.
32 bit is not limited to 4GB. By default, a 32-bit process has a maximum address space of 2GB for user code. This can be increased by using the AWE mechanism, to a maximum of 64GB (depending on SKU).
AWE-mapped memory is only available for use by the data cache part of the buffer pool, but this is usually by far the largest memory consumer in SQL Server anyway.
Cached plans reside in the procedure cache, which cannot use AWE, and is therefore always directly mapped into the virtual address space of SQL Server - the 2GB mentioned above. Moving the data page cache into AWE-mapped memory frees this VAS up a lot.
The /3GB switch can be employed to give SQL Server 3GB of VAS when it starts but this is not recommended, and should only be done if directed to do so by a Microsoft CSS engineer.
The /3GB switch restricts Windows to 1GB of process space (shared across all processes). This is extremely tight and can result in unexpected behaviours. For example, in this configuration, Windows supports an absolute maximum of 16GB of memory - it does not have the necessary address space to support more than that when forced to run in 1GB of VAS.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply