Database Reference
In-Depth Information
Increasing System Memory
The memory requirement of SQL Server depends on the total amount of data processed by SQL activities. It is
not directly correlated to the size of the database or the number of incoming SQL queries. For example, if a
memory-intensive query performs a cross join between two small tables without any filter criteria to narrow down
the result set, it can cause high stress on the system memory.
One of the easiest and quickest resolutions is to simply increase system memory by purchasing and installing
more. However, it is still important to find out what is consuming the physical memory because if the application
workload is extremely memory intensive, you could soon be limited by the maximum amount of memory a system
can access. To identify which queries are using more memory, query the sys.dm_exec_query_memory_grants DMV
and collect metrics on queries and their I/O use. Just be careful when running queries against this DMV using a JOIN
or an ORDER BY statement; if your system is already under memory stress, these actions can lead to your query
needing its own memory grant.
Changing from a 32-Bit to a 64-Bit Processor
Switching the physical server from a 32-bit processor to a 64-bit processor (and the attendant Windows Server software
upgrade) radically changes the memory management capabilities of SQL Server. The limitations on SQL Server for memory
go from 3GB to a limit of up to 8TB depending on the version of the operating system and the specific processor type.
Prior to SQL Server 2012, it was possible to add up to 64GB of data cache to a SQL Server instance through the
use of Address Windowing Extensions. These were removed from SQL Server 2012, so a 32-bit instance of SQL Server
is limited to accessing only 3GB of memory. Only small systems should be running 32-bit versions of SQL Server 2014
because of this limitation.
Compressing Data
Data compression has a number of excellent benefits for storing and retrieving information. It has an added benefit
that many people aren't aware of: While compressed information is stored in memory, it remains compressed. This
means more information can be moved while using less system memory, increasing your overall memory throughput.
All this does come at some cost to the CPU, so you'll need to keep an eye on that to be sure you're not just transferring
stress. Sometimes you may not see much compression because of the nature of your data.
Enabling 3GB of Process Address Space
Standard 32-bit addresses can map a maximum of 4GB of memory. The standard address spaces of 32-bit Windows
operating system processes are therefore limited to 4GB. Out of this 4GB process space, by default the upper 2GB is
reserved for the operating system, and the lower 2GB is made available to the application. If you specify a /3GB switch
in the
boot.ini
file of the 32-bit OS, the operating system reserves only 1GB of the address space, and the application
can access up to 3GB. This is also called
4-gig tuning
(4GT). No new APIs are required for this purpose.
Therefore, on a machine with 4GB of physical memory and the default Windows configuration, you will find
available memory of about 2GB or more. To let SQL Server use up to 3GB of the available memory, you can add the
/3GB
switch in the
boot.ini
file as follows:
[boot loader]
timeout=30
default=multi(o)disk(o)rdisk(o)partition(l)\WINNT
[operating systems]
multi(o)disk(o)rdisk(o)partition(l)\WINNT=
"Microsoft Windows Server 2012 R2 Advanced Server"
/fastdetect /3GB
