To do this, the database engine fetches as much free memory as possible, while keeping enough free memory to prevent the operating system from swapping memory.SQL Server instances typically fetch 8 to 12 MB of memory at startup to complete the initialization process. Once the instance completes initialization, no more memory is fetched until a user connects to the instance and starts generating a workload. At that point, the instance keeps fetching memory as needed to support the workload. As more users connect and run queries, SQL Server will fetch the additional memory needed to support the demand. The instance will continue to fetch memory until it reaches its own memory allocation target and will not release any memory until it reaches the lower limit of that target. In order to fetch as much memory as possible without generating excess page-flip I/O, each instance of SQL Server is configured with a memory fetch target in the range of 4MB to 10MB of physical memory available to the computer. This range is chosen because tests have shown that both Windows NT and Windows 2000 have a minimum memory swap until the memory allocation equals the available physical memory minus 4MB. SQL Server instances with heavy workloads reserve the lower end of the range (4MB) of available physical memory; instances with light workloads reserve the higher end of the range (10MB) of available physical memory. The target for SQL Server instances changes as the workload changes. When more users connect and generate more work, the instance tends to acquire more memory to keep available memory below the 4MB limit. When the workload decreases, the instance adjusts its target to 10MB of free space and frees memory to the operating system. Keeping the amount of free space between 10MB and 4MB prevents Windows NT or Windows 2000 from performing too many page-flips, and allows SQL Server to get the maximum amount of cache memory possible without causing additional swapping. The target memory configuration for an instance is related to the need for database buffer pool pages relative to the available pool size. At any real-time point, the total buffer page requirement depends on the number of data pages needed to satisfy any currently executing query. If the demand for data pages is high relative to the number of pages in cache memory, each page currently in the buffer is likely to be replaced by a new page in a relatively short period of time. This can be measured by the page lifetime performance counter of the buffer manager object. Higher demand for relatively small buffers will generate short lifetimes, and the sheer effect is to increase I/O, as pages are often rewritten before they can be read and referenced by more than one piece of logic. To mitigate this problem, the database engine is able to fetch more memory to increase the size of the cache memory. When pages have long lifetimes, the database engine targets the high end of the available memory range (10 MB); when pages have short lifetimes, the database engine targets the low end of the target range (4 MB). As other applications are started on the computer running the SQL Server instance, they consume memory, causing the amount of physical memory available to fall below SQL Server's target, and the SQL Server instance frees up enough memory from its address space to bring the amount of available memory back to SQL Server's target. If another application stops running and makes more memory available, the SQL Server instance increases the size of its memory allocation.SQL Server can free and fetch several MB of memory per second, which allows it to quickly adjust to changes in memory allocation. You can limit this by configuring the maximum amount of memory allowed to be used by sql server: Impact of Minimum and Maximum Server Memory The min server memory and max server memory configuration options establish upper and lower limits on the amount of memory used by the SQL Server database engine. The database engine does not immediately acquire the amount of memory specified in min server memory. The database engine starts with only the memory it needs for initialization. As the database engine's workload increases, it continues to acquire the memory it needs to support the workload. The database engine does not release any needed memory until it reaches the amount of memory specified in min server memory. Once it reaches min server memory, the database engine uses a standard algorithm (which keeps the operating system's available memory between 4MB and 10MB) to fetch and free the required memory. The only difference is that the database engine never drops the memory allocation below the level specified by min server memory, and never fetches more than the level specified by max server memory. The amount of memory fetched by the database engine depends entirely on the workload placed on the instance. A SQL Server instance that does not handle many requests may never reach min server memory. if you specify the same value for min server memory and max server memory, the database engine stops dynamically freeing and fetching memory as soon as the memory allocated to it reaches that value. If other applications are frequently started or stopped on the computer running the SQL Server instance, the time it takes to start these applications may be lengthened as the SQL Server instance allocates and frees memory. Also, if SQL Server is one of several server applications running on a single computer, the system administrator may need to control the amount of memory allocated to SQL Server.