Using RAM for SQL Server
Administrators are often surprised how SQL Server occupies RAM. Some seem to worry about the gradual increase and no return of the occupation in RAM of sqlservr.exe process,and fear memory leak and excessive consumption of resources.In this article,we give you a brief explanation of the use of RAM by sqlserver
Administrators are often surprised how SQL Server occupies RAM. Some seem to worry about the gradual increase and no return of the occupation in RAM of sqlservr.exe process,and fear memory leak and excessive consumption of resources. It is instead a desirable behavior. We must understand that SQL Server is designed to be alone, without competition from other applications on a server.Thus,by default ( you can limit the available memory, we shall see later ),it appropriates all the resources reasonably available . If you want to ensure best running SQL Server, you can do that only by dedicating a machine to him, and allowing him to assign the RAM it needs.In a default configuration, a SQL Server instance is not limited in RAM. At startup,it calculates the amount of memory reserved for its different elements, and then assigns physically reserved memory as needed. As the behavior differs depending on the uses of memory, we will detail the behavior of the important elements for performance. SQL Server uses RAM for various caches , the most important of these are data pages , and query execution plan.Buffer
The data cache (called the buffer, or buffer pool) keeps in RAM as much as possible, the data pages and index. Reading from the RAM is about a thousand times faster than on a hard drive, one understands interest easily. This cache is managed by a component of SQLOS named the buffer manager, whose job is reading pages from disk to the buffer and writing modified buffer pages to disk . When starting the instance, SQL Server calculates and reserves the virtual address space of the buffer ( memory target). This is placeholder, but the physical memory will only be taken as needed. Memory actually used is less than the reserved until the data requested by the query will not have it filled (this period before filling is called the ramp-up) . You can see the target memory and memory actually used in bpool_commit_target and bpool_committed columns of the dynamic management view sys.dm_os_sys_info:SELECT bpool_commit_target, bpool_committed FROM sys.dm_os_sys_info;
A DBCC command will allow to empty the buffer pages:DBCC DROPCLEANBUFFERS.As its name suggests, only pages clean (unmodified) are deleted, others expect a checkpoint to be written to disk. So you can be sure to empty the more pages possible by first launching the CHECKPOINT command:CHECKPOINT;
DBCC DROPCLEANBUFFERS;
When SQL Server has "validated" physical memory for the buffer, it does render it to the system. Since the system or task manager (columns Memory Usage and Virtual Memory Size) monitor, you will see the sqlserver.exe process take more virtual memory as to the query execution, and never render it.
One of the items to stay in mind once you area unit watching reducing the RAM allotted to a SQL Server instance is that you just can eventually get to some extent wherever the lower memory gets listed off for higher disk I/O access within the environments.
If you wish to work out the best configuration for SQL Server memory in Associate in Nursing atmosphere that has been over provisioned the most effective thanks to attempt to approach doing this is often begin with a baseline of the atmosphere and therefore the current performance metrics. Counters to start watching would include:
SQL Server:Buffer Manager\Page lifetime
SQL Server:Buffer Manager\Page reads/sec
Physical Disk\Disk Reads/sec
Typically if the atmosphere has excess memory for the buffer pool, the Page lifetime worth can still increase by a worth of 1 each second and it will not generally drop off below the employment as a result of all of the info pages find yourself being cached. At a similar time, the quantity of SQL Server:Buffer Manager\Page reads/sec are low when the cache build up happens which can additionally correspond to an occasional worth for Physical Disk\Disk Reads/sec.