database-internalssql server
When we execute SQL query, then Relational Engine optimize it and pass query plan to Storage Engine which then ask for a page with actual data.
If data page isn't in memory, then SQL pull those pages from disk and place it in RAM.
How, in future, SQL determine if page, which Storage Engine asks, is already in memory? And which algorithm it uses to keep some free space for new pages?
Depends on the cursor type, as documented in Concepts: Cursors:
Forward-only
A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched ... Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, SQL Server does not. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors.
Static
The complete result set of a static cursor is built in tempdb when the cursor is opened. ...
Keyset
The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers, keys, known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.
Dynamic
Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. ...
As for memory use, all data access goes through the buffer pool, see Memory Manager Architecture. There is no 'close' nor 'deallocation' occurring. Data is brought in memory as needed, it may be referenced by multiple queries, and evicted only when more free memory is required.
In SQL Server 2008 R2 and earlier, the following allocations come from the MemToLeave (MTL) area:
MAXTRANSFERSIZEInformation extracted from this article by Microsoft support engineer Amit Banerjee.
SQL Server 2012 uses the Any Size Page Allocator.
Further information is included in this Microsoft Knowledge Base article.
Multi-page allocations (MPA) also come from the MTL area, before SQL Server 2012. Whether an allocation made by SQL Server is made via the MPA or single page allocator (SPA) depends purely on the size of the allocation request. If it is 8KB or less, it is allocated via SPA and stolen from the buffer pool. If it is 8KB or more, MPA is used.
I know data page is always 8K so is there a situation where data/index page make use of MPA?
No, data and index pages are always cached in the buffer pool.
Is there a way to confirm that memory allocation for the execution plan is using multi page allocation?
Cached plans have a complex structure. Individual allocations within that structure are generally 8KB allocations, but certain queries (generally those that contain long IN lists or many parameters to a stored procedure call) can require an MPA allocation. These are comparatively rare. It is possible to query SQL Server DMVs to determine the split between SPA and MPA for a cached plan.
Best Answer
To balance CPU and I/O throughput to achieve good performance and maximize hardware utilization, SQL Server includes 2 asynchronous I/O mechanisms - sequential read ahead and random prefetching
When SQL Server has to scan a large table, the storage engine will initiate the read ahead mechanism to ensure that pages are in memory and they are ready to scan before they are needed by the Query Optimizer.
If you are using Enterprise edition, then there is a mechanism called advanced scanning or merry-go-round scanning that will allow multiple tasks to share full table scans. This advance scanning avoids users to compete for buffer space and taxing other resources on the server.
SQL Server (and Sybase as well :-) ) uses LRU (Least Recently Used) algorithm to keep track of pages that are aged in the buffer pool. It increments a counter every time a page is referenced and decrements the counter every time the lazy writer process sweeps the page.
Any worker thread will check the memory status of Buffer Pool to make sure that there are healthy number of Free pages to honor incoming new requests.
Note that : SQL Server will always keep a minimum number of free pages on the free list, so that it can serve incoming requests without any significant delay. The amount of free space is calculated based on Buffer Pool and amount of incoming requests.
IF there are no free buffers or very little left then :
You can use DMV (in 2005 and up) -
sys.dm_os_buffer_descriptorsto find the pages in the buffer pool.Also, there are Memory Manager surface area changes in SQL Server 2012.
KB 2663912 :
Highly recommend to read :