In a very real sense, SQL Server automatically attempts to keep as much of the database in memory as it can.
By default, when SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory available. If there is more memory free, SQL Server recommits memory to the buffer cache, which SQL Server uses to store data for ready access. SQL Server adds memory to the buffer cache only when its workload requires more memory; a server at rest does not grow its buffer cache.
SQL Server allocates much of its virtual memoryto a buffer cache and uses the cache to reduce physical I/O. Each instance of SQL Server automatically caches execution plans in memory based upon available memory. Data is read from the database disk files into the buffer cache. Multiple logical reads of the data can be satisfied without requiring that the data be physically read again.
By maintaining a relatively large buffer cache in memory, an instance of SQL Server can significantly reduce the number of physical disk reads it requires.
Another method of providing performance improvement is some cases it to use DBCC PINTABLE, which is used to store specific tables in memory on a more or less permanent basis. It works best for small tables that are frequently accessed. The pages for the small table are read into memory one time, and then all future references to their data do not require a disk read. SQL Server keeps a copy of the page available in the buffer cache until the table is unpinned using the DBCC UNPINTABLE statement. This option should be used sparingly as it can reduce the amount of overall buffer cache available for SQL Server to use dynamically.
Other than what has been described above, there is no command to tell SQL Server to store an entire database in memory all the time. The reason for this is because for most databases, there are portions of it that are rarely used, and there is no point in storing data in memory if it is rarely or never accessed. It is more efficient to only store in memory that data that is accessed frequently, and get rarely used data only upon specific request.
]]>