0

My environment is SQL 2008 Standard x86 with SP2 running on Server 2008 Standard, with 4Gb ram and AWE enabled. I also have -g1024 set as a startup parameter.

I am getting the following error in the SQL error log: Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 327680

Then later: Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool 'internal' to run this query.

Which are repeated until the server eventually stops processing queries.

I strongly suspect that the issue is to do with linked servers as I am linking to a Sybase ASE 15 server using the "Microsoft OLEDB Provider for ODBC Drivers" provider. How can I prove whether or not this is the case and/or troubleshoot this issue?

Thanks.

Edit: Here is the current output for MEMORYCLERK_SQLGENERAL under error conditions..

VM Reserved 0
VM Committed    0
AWE Allocated   0
SM Reserved 0
SM Committed    0
SinglePage Allocator    2168
MultiPage Allocator 2056
Craig
  • 103
  • 4

1 Answers1

0

First - is your max server memory set for the SQL Server instance as well? If not, given the limited amount of memory you have, your situation probably warrants it.

Regarding your main question, a few things to check:

So I'm suggesting process-of-elimination here. Also - if you can reproduce the OOMs with specific linked server queries, you can run the memory queries to see before/after and then this will be a pretty good indicator. Also check the linked server provider cases to make sure you aren't encountering known issues. [As an aside since this isn't an Oracle provider - there are cases I know of where you get these messages if the Oracle Linked Server is running in process and taking it out of process helped.]

Also - you may have a legit need for the additional memory, so the long term fix might be to scale up to more memory and x64. You may be able to tweak reserved MTL, and max memory, but this might be memory pressure that can only be resolved by a new architecture and increased memory.

Joe Sack
  • 321
  • 1
  • 5
  • Max memory is set to the default of 2147483647. Should I set it to 3x1024? I have had a look at DBCC MEMORYSTATUS but don't know where to look for issues to do with linked servers. We have to use 32bit as 64bit Sybase drivers are not available to us. – Craig Mar 18 '11 at 10:12
  • Save some room for the Operating System too - but yes in this situation you'll want to cap the max memory. The "correct" max memory is really dependent on what else is running, and if you are experience memory pressure, capping may not be a long term solution (reducing buffer pool size means less data in cache), but it is something you should try. Regarding DBCC MEMORYSTATUS - I recommended this so you could rule out other non-buffer pool consumers. More of a process of elimination than a direct way to identify this as a linked server issue. You can check in sys.dm_os_memory_clerks too. – Joe Sack Mar 18 '11 at 11:31
  • And set the max memory with the following in mind: non-buffer pool MTL memory + OS memory + max server buffer pool memory – Joe Sack Mar 18 '11 at 11:35
  • Okay, I am going to go with MTL=1024, Max server memory=3584, leaving 512 for the OS (it does nothing but SQL anyway). Re: DBCC MEMORYSTATUS and other memory status queries, I am unable to use it to rule out anything as I don't understand what it is telling me - I am not a DBA. – Craig Mar 18 '11 at 16:37
  • For an overview of DBCC MEMORYSTATUS, check out: How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;907877 – Joe Sack Mar 18 '11 at 18:28
  • And by the way - you'll see Linked Server query usage lumped in on the MEMORYCLERK_SQLGENERAL category. Unfortunately this is lumped in with other possible areas (like Profiler tracing) - so it isn't an isolated categorization. So the key reason I recommended you check this out was to see if any of the other categories were larger users of the multi-page allocation. – Joe Sack Mar 18 '11 at 18:38
  • I just remembered that the linked server provider has "allow in process" checked, as it doesn't work otherwise. Does this not mean that the SinglePage Allocator is actually the relevant one? It is the one with the most use: 19320 for MultiPage, 235592 for SinglePage right now on Memory node id = 0. – Craig Mar 21 '11 at 10:48
  • My understanding is that it would still be using multi-page allocations. MEMORYCLERK_SQLGENERAL is associated with other allocations beyond linked servers - so if this is the biggest one, then we're not seeing other big consumers (and at least it rules them out). Have you had the out of memory events after capping the max memory? – Joe Sack Mar 21 '11 at 14:16
  • The five highest figures are... MEMORYCLERK_SOSNODE: 7464, MEMORYCLERK_SQLSTORENG: 2984, MEMORYCLERK_SQLGENERAL: 2056, MEMORYCLERK_SQLBUFFERPOOL: 1248, USERSTORE_SCHEMAMGR: 736 There have been no recurrences since capping max memory, but I'll need to give it a couple of days to see for sure. – Craig Mar 21 '11 at 15:23
  • Okay - let me know how it goes. By capping "max server memory" you are reducing the buffer pool, which can translate to more I/O, but frankly you don't have that much memory to begin with. So capping it may reduce the chances of memory pressure in conjunction with your non-buffer pool memory requirements for the Linked Server queries. – Joe Sack Mar 21 '11 at 16:01
  • Okay, I am now getting the memory errors again. The top memory clerks are still the same, with the same values as in my last comment. I will update the question with the dump in the error log. – Craig Mar 22 '11 at 10:26
  • The fact that I am seeing the error but the value of 'Reseve memory in use' is 0 leads me to beleive that the -g startup flag is not needed. – Craig Mar 22 '11 at 10:42
  • Have you eliminated virtual memory fragmentation as an issue (mentioned on my first reply)? (http://troubleshootingsql.com/2010/02/16/how-to-find-who-is-usingeating-up-the-virtual-address-space-on-your-sql-server/) – Joe Sack Mar 22 '11 at 11:19
  • By the way - removing that flag likely won't help either. This is a multipage allocation - so we're talking non-buffer pool memory here. So either your virtual memory is fragmented or there simply isn't enough free memory available. – Joe Sack Mar 22 '11 at 11:36
  • I have had to restart SQL so am back to watching for a builduip again. This time I will monitor the largest contiguous block in VAS as shown in that link. Currently it is at ~50MB. The error seems to come when trying to allocate 320Kb so I'm expecting the max block to come down. Other than that, I don't see any detail on that page of how to actually find what is causing the problem. – Craig Mar 22 '11 at 15:03
  • You're not going to see a bucket that says "linked server memory". If it were something else like CACHESTORE_XPROC (extended stored procedures) or CLR - that is more direct. But with your situation, seems like you need to do process of elimination. So far you've indicated that it doesn't seem to be caused by other non-buffer pool consumers. Even if you find that it is virtual memory fragmentation, then your choice is to add more memory, move the workload to a system that has more memory or explore linked server query alternatives (different drivers/providers, for example). – Joe Sack Mar 22 '11 at 18:03
  • Bearing in mind that this is a 32-bit server, if I add more memory (and enable /PAE as it's already got 4Gb), will it be usable by SQL Server for linked server operations? – Craig Mar 23 '11 at 13:59
  • For 32-bit, additional AWE memory applies to just the buffer pool. This region wouldn't cover linked server providers, extended stored procedures, COM objects. However it could still reduce pressure. Best bet (and yes I realize this isn't ideal) is to move to x64 if you can - but if you're stuck on 32-bit, increasing the memory along with capping the buffer pool size and increasing the MTL, you might be okay. Speaking of which - it would be good to know your values (during failure) of buffer versus non-buffer pool. I blogged on the counters here: http://bit.ly/e8wKvg – Joe Sack Mar 23 '11 at 16:08