SQL SERVER Memory Management

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.