Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Can you please tell whether there are advantages in the transition from 32-bit to 64-bit architecture for the SQL Anywhere 12 server on a 64-bit Windows operating system?
How much can increase system performance under the condition that all the characteristics of the hardware (since the only difference for the cache: with AWE on 32-bit and without AWE on 64-bit) and all the characteristics of the database (DDL, stored procedures, ...) will remain unchanged? P.S. Note that the user's PowerBuilder12-application will remain 32-bit.

asked 06 Feb '13, 01:11

Ilia63's gravatar image

accept rate: 44%

edited 08 Feb '13, 04:34

Volker%20Barth's gravatar image

Volker Barth

If you are using AWE currently, you may consider John's answer in this FAQ.

(06 Feb '13, 03:37) Volker Barth

The case of the small amount of RAM can not be considered. Assume we have an lot of RAM (but, <= 64 GB for Windows Server 2008 Enterprise).

(06 Feb '13, 03:49) Ilia63
Replies hidden

My hint was not focussed on that particular user's problem but on the general explanation John has given to the limitations of AWE (e.g. no dynamic cache sizing, not swappable, particular (and more expensive) addressing mode and the like).

As I understand his profound answer, he makes a general recommendation for "bigger" databases on 64-bit Windows to use a 64-bit database engine instead of a 32-bit engine with AWE.

(07 Feb '13, 04:27) Volker Barth

From the comments on:

With 32-bit Windows one is limited in address space to 2GB by default (one can set this to 3GB if one uses the /3GB switch: add the /3GB option to the Windows boot line in the “[operating systems]” section of the boot.ini file).

The maximal amount of address space that can be allocated by the SQL Anywhere server process is bounded by this address space setting – it varies by the variant of Windows, and is either approximately 1.8GB or 2.7GB (we document all of the OS variants with this information in our product documentation).

To simplify the following explanation, let’s stick to a 2GB address space bound. With a 2GB address space, a 32-bit server can operate with a maximal cache of 1.8GB. To use more physical memory than that, one can use Address Windowing Extensions (AWE) that bank-switches memory pages to and from the addressable portion of the server’s address space. Hence even with AWE, the server is still unable to directly address more than 1.8GB directly. AWE pages not addressable by the SQL Anywhere server can be images of table or index pages, but are by definition not part of the server’s working set of memory and cannot be addressed by the server process unless they are swapped in (and other memory pages already in addressable memory are swapped out to make room).

To answer your question, then: if my application can benefit from a larger cache size than is directly addressable by the server, then by all means use AWE extensions. However, with 64-bit versions of Windows, and 64-bit versions of SQL Anywhere, the need for AWE is eliminated because the server can directly address all of the available RAM on the processor.

The above description was assuming a 32-bit OS. The only changes from a move to a 64-bit OS and a 32-bit process vs a 64-bit process is that the base amount of memory increases to about ~3.6GB in a 32-bit process, then AWE is used. The same limitations about AWE still apply.

In short, always use the 64-bit database server on a 64-bit operating system where possible if you wish for the database server to directly use all of the memory (rather than have the expense of memory swapping to simulate this with a 32-bit AWE).

Also notably, AWE extensions (dbsrv12 -cw) are deprecated.

permanent link

answered 06 Feb '13, 08:56

Jeff%20Albion's gravatar image

Jeff Albion
accept rate: 25%

Thank you very much for Your answer. However, I would like to clarify some details. So, Glenn Paulley wrote that: "There is little difference in the operation of the cache manager between 64- and 32-bit architectures with (or without) AWE. In all cases the cache manager must keep track of all pages in the buffer pool; the difference is that with AWE a page may not have a page frame in addressable memory. If a page is required by a thread and is not in addressable memory, the cache manager determines a victim and makes the AWE call to swap the victim page to extended storage; and then makes a second call to make the required page addressable. This is done through page table manipulations rather than memcpy, so it’s not that expensive (though nothing is free)." Does it follow that the cost usage of AWE-memory for 32-bit SQL Anywhere is only the support "page table manipulations"-mechanism? In my opinion, this board is still very small. Or the use of AWE-memory requires additional exchange (which is absent for 64-bit SQL Anywhere), for example, between RAM and disk?

(07 Feb '13, 01:39) Ilia63
Replies hidden

Or the use of AWE-memory requires additional exchange (which is absent for 64-bit SQL Anywhere), for example, between RAM and disk?

I'll let Glenn answer this question again:

The issue with AWE vs. 64-bit is the address space. Memory-intensive operators (such as hash join or sort) require contiguous areas of addressable memory.

... yes, with 32-bit AWE, it is possible that there may not be "enough" query memory for memory-intensive operations and the server will have to resort to spooling intermediate results to disk, whereas with 64-bit servers this may be unnecessary.

Again, we highly emphasize to always use the 64-bit database server, wherever possible. The technical details behind this rationale agree with this recommendation.

(07 Feb '13, 12:24) Jeff Albion
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 06 Feb '13, 01:11

question was seen: 17,408 times

last updated: 08 Feb '13, 04:34