Generally SQL SERVER manages memory in two different ways.
32 BIT OS
In 32 Bit OS, Sql Server divides the memory into two parts, one will have the
data pages and the other one will have the rest like indexes, compiled queries,
results of joins and so on.
32 bit are enough to handle 4 GB at a time. Default configuration will be like,
2GB for operating system and 2 GB for application.
If you have 64 GB of RAM with a 32 bit system you will have only 1 GB for the
shared information. You can modify this in your system, but it may have adverse
effect in the performance. You should do various load tests before doing the
manipulation in production.
64 BIT OS
In 64 Bit OS, division of data pages and the shared information goes away. Here
the SQL SERVER can use the entire memory for any type of objects. One more
advantage here is memory usage limit is increased up to 2 TB. Mostly 64 bit
systems are highly recommended for production database servers for high
performance.
32 BIT OS vs 64 BIT OS
- 64 BITS are not bound by the memory limit
as in 32 BIT OS.
- More memory is available in 64 Bit OS for
performing complex queries and supporting essential database operations.
- 64 Bit provides enhanced parallelism
whereas in 32 BIT doesn't provides that.
- 64 Bit enhances performance by moving more
data between cache and processors in shorter periods.
- Index creation operations benefits from
the existence of larger addressable memory in 64 Bit systems.
- The 64-bit architecture can substantially
reduce overall CPU utilization and latency by eliminating the need to evict
procedures from cache and compile frequently.
- Server-side cursors, which are often heavy
consumers of memory, can more readily be kept in memory, thereby resulting
in better performance.
- Operations such as aggregation and sorting
need to work with the entire datasets. These operations can benefit from the
increased memory support provided by the 64-bit platform.