Buffer Pool

Buffer Pool
SQL Server zapisuje dane w pliku na tzw. stronach danych (8KB) (data page), pogrupowanych w Extent (po 8 stron, czyli 64 KB). Gdy informacje zapisane w pliku są potrzebne, cała strona danych je zawierająca jest odczytywana i zapisywana w buforach pamięci operacyjnej, które łacznie składają się na Buffer Pool. Pojedynczy bufor także ma wielkość 8KB.

Strona odczytana z dysku to strona "czysta" (clean page). Wszystkie kolejne żądania danych z tej strony danych są obsługiwane przez bezpośredni odczyt z pamięci operacyjnej, z pominięciem kosztownych odczytań z dysku. Gdy strona danych zostanie zmodyfikowana (Insert/update/delete), staje się stroną "brudną" (dirty page). Procesem odczyty czystych stron dancych lub stron indeksu z dysku i zapisaniem do pliku stron czystych zarządza specjalny proces "Buffer Manager"

Jak obejrzeć
Od wersji 2005 SQL Server możemy łatwo odczytać zawartość buforów danych - każda strona w Buffer Pool ma Buffer Descriptor. Widok DMV sys.dm_os_buffer_descriptors zwraca informacje o zawartości bufora, wraz z informacją o tym, z jakiej bazy danych, pliku i strony w pliku te dane pochodzą.

 SELECT * FROM sys.dm_os_buffer_descriptors;  
 GO  

Widok zwraca zarówno informacje o stronach pochodzących z baz systemowych, jak i baz użytkownika. Dzięki informacjom o "pochodzeniu" strony danych wewnętrzny proces checkpoint wie, które gdzie zapisać "brudne" strony w pliku. Informacja  w kolumnie "free_space_in_bytes" informuje, jak dużo miejsca jest "zmarnowane".

Możemy oczywiście pogrupować te wartości

 SELECT (CASE  
       WHEN ( [database_id] = 32767 ) THEN 'Resource Database'  
       ELSE Db_name (database_id)  
      END ) AS 'Database Name',  
     Sum(CASE  
        WHEN ( [is_modified] = 1 ) THEN 0  
        ELSE 1  
       END) AS 'Clean Page Count',  
           Sum(CASE  
        WHEN ( [is_modified] = 1 ) THEN 1  
        ELSE 0  
       END) AS 'Dirty Page Count'  
 FROM  sys.dm_os_buffer_descriptors  
 GROUP BY database_id  
 ORDER BY DB_NAME(database_id);  

Można śledzić wielkość Buffer Pool poprzez PerfMon - liczniki "Buffer Pool"

Czyszczenie 
Polecenie DBCC DROPCLEANBUFFERS powoduje opróżnienie Buffer Pool z czystych stron. Kolejne żądanie danych z tych stron spowoduje ponowne ich odczytanie z dysku i ponowne umieszczenie w Buffer Pool.

Wielkość 
Wielkość Buffer Pool może być kontrolowana pośrednio poprzez ustawienie Max Server Memory. Na wyliczenie wielkości oprócz dostępnej pamięci składa się jednak więcej składników, np. ilość wątków.

Dodatkowe informacje
1. https://technet.microsoft.com/en-us/library/aa337525(v=sql.105).aspx
2. http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-whats-in-the-buffer-pool/