SQL Server 2014 - Buffer Pool Extension - BFE
Introdução
O buffer pool extension é um novo recurso do SQL Server 2014, o objetivo principal é extender o buffer pool alocando memória em disco SSD, como o acesso ao disco SSD é muito efeciente a melhora de throughput é signifacante e além disso você aumenta a memória do seu cache em seu servidor ou seja, acessar o disco não é uma rotina rápida para qualquer banco de dados, porém em diversos cenários sofremos com a quantidade de dados dentro do nosso buffer pool, as vezes temos uma entrada e saída tão grandes das páginas que foram carregadas em cache que a expectativa da página em memória se torna muito baixa e assim a eficiência de suas consultas são totalmente afetadas. Os benefícios que temos em extender nosso cache utilizando SSD são:
Aumento de throughput de IO.
Redução da latência (menos acesso ao disco).
Aumento de throughput transactional, mais transações são realizadas.
Grande aumento de leitura, possibilidade de leitura em dois locais diferentes.
A possibilidade de se lidar agora não somente com DRAM (memória RAM) mas também com NAND-Flash(disco SSD), faz com que você tenha muito mais páginas em cache, com isso a busca de registros em disco acontecem com menor frequência. O que é totalmente interessante de notar é que, esses dois tipos de memória trabalham totalmente integrados e com isso os mesmos possuem níveis hierârquicos, sendo nivel 1 (Cache L1) que é a memória RAM e nível 2 (Cache L2) que é o disco SSD. Com isso, somente as páginas “limpas” ou o que chamamos de Clean Pages são escritas no cache de nível 2, isso garante que os dados no SSD sempre estarão seguros e garantidos. O Buffer Manager se encarrega de mover as páginas entre os dois níveis. Quando habilitado é necessário criar um arquivo com o tamanho que você deseja que seja seu nível 2 com isso se você especificar por exemplo 50GB então em seu HD SSD será alocado 50GB.
http://i.msdn.microsoft.com/dynimg/IC655069.gif
(Figura 1 – http://msdn.microsoft.com/en-us/library/dn133176(v=sql.120).aspx)
Iremos agora realizar a extensão do Buffer Pool, iremos realizar a configuração somente para exemplo do “Max Server Memory” para 512 MB, assim eu acabo forçando mais com que as páginas sejam alocadas no nível 2 (SSD)
Demonstração
EXEC sp_configure ‘advanced options’, 1;
GO
RECONFIGURE
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO
EXEC sp_configure ‘max server memory (MB)’, 512;
GO
RECONFIGURE;
GO
Realizando a configuração da memória, agora iremos alocar a quantidade de 50GB para meu cache em SSD ou seja estamos de fato realizando a configuração do Buffer Pool Extension.
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(
FILENAME = ‘C:\BufferPoolExtension\SQLServerLuanMoreno.BPE’,
SIZE = 50 GB
);
GO
Iremos nesse momento criar um tabela em um banco de dados e inserir registros, após isso, iremos realizar o select dos registros para colocá-los em memória, como temos pouca quantidade de RAM após um determinado momento iremos ver que as páginas não irão mais para a RAM (L1) mais sim para o SSD (L2)…… Porém irei criar 3 databases snapshots nesse banco e realizar a carga em memória dos resultados, com isso eu estarei trazendo mais registros ainda para o cache, para entender melhor o porque do database snapshot (Cache Trashing), veja essa explicação do nosso MVP em SQL Server Luciano Caixeta Moreira –http://luticm.blogspot.com.br/2011/07/artigo-o-caso-dos-snapshots-e-data.html
USE Demos
GO
CREATE TABLE DadosClientes
(
ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID(),
Nome VARCHAR(100) NOT NULL INDEX idx_nome,
IDGUID UNIQUEIDENTIFIER NOT NULL INDEX idx_guid,
Tipo VARCHAR(50) NOT NULL
)
SET NOCOUNT ON;
INSERT INTO DadosClientes (Nome, IDGUID, Tipo)
SELECT S.name, NEWID(), S.type_desc
FROM master.sys.objects AS S
CROSS APPLY msdb.sys.objects AS O
GO 4
CREATE DATABASE DemosSnapshot1 ON
( NAME = Demos,
FILENAME = 'C:\BufferPoolExtension\DemosSnapshot1.SS'
) AS SNAPSHOT OF Demos
GO
CREATE DATABASE DemosSnapshot2 ON
( NAME = Demos,
FILENAME = 'C:\BufferPoolExtension\DemosSnapshot2.SS'
) AS SNAPSHOT OF Demos
GO
CREATE DATABASE DemosSnapshot3 ON
( NAME = Demos,
FILENAME = 'C:\BufferPoolExtension\DemosSnapshot3.SS'
) AS SNAPSHOT OF Demos
GO
DBCC DROPCLEANBUFFERS
SELECT * FROM Demos.dbo.DadosClientes
SELECT * FROM DemosSnapshot1.dbo.DadosClientes
SELECT * FROM DemosSnapshot2.dbo.DadosClientes
SELECT * FROM DemosSnapshot3.dbo.DadosClientes
Se verificarmos na DMV – sys.dm_os_buffer_descriptors no novo campois_in_bpool_extension iremos ver que…..
SELECT DB_NAME(database_id),*
FROM sys.dm_os_buffer_descriptors
WHERE is_in_bpool_extension = 1
http://luanmorenodba.files.wordpress.com/2013/07/capture_thumb1.png?w=904&h=177
(Figura 2 – Buffer Pool Extension – DMV – sys.dm_os_buffer_descriptors – Registros no SSD)
http://luanmorenodba.files.wordpress.com/2013/07/capture1_thumb.png?w=529&h=44
(Figura 3 – Quantidade de registros no SSD (L2) – 26.741 páginas.)
Assim conseguimos de fato comprovar que temos essa grande quantidade de registros no Buffer Pool Extension. Não somente assim, temos novos Extended Events para a captura da infornação e para esses recurso assim como outros mais Deep Dives recomendo leitura obrigatória o Blog do meu amigo –http://ivanglima.com/, iremos aqui ver a captura dos eventos referentes a alocação no SSD..
O evento sqlserver.buffer_pool_extension_pages_written captura quando páginas são colocadas no buffer extension file
CREATE EVENT SESSION [buffer_pool_extension_pages_written] ON SERVER
ADD EVENT sqlserver.buffer_pool_extension_pages_written
(
ACTION
(
package0.callstack,
package0.collect_cpu_cycle_time,
package0.process_id,
sqlos.scheduler_address,
sqlos.scheduler_id,
sqlos.system_thread_id,
sqlos.task_address,
sqlos.task_time,
sqlos.worker_address,
sqlserver.client_hostname,
sqlserver.context_info,
sqlserver.database_id,
sqlserver.database_name,
sqlserver.is_system,
sqlserver.plan_handle,
sqlserver.query_hash,
sqlserver.query_plan_hash,
sqlserver.server_instance_name,
sqlserver.session_id,
sqlserver.session_server_principal_name,
sqlserver.sql_text,
sqlserver.transaction_id,
sqlserver.tsql_frame,
sqlserver.tsql_stack,
sqlserver.username
)
)
ADD TARGET package0.event_file
(
SET FILENAME= N'C:\BufferPoolExtension\buffer_pool_extension_pages_written.xel',
max_rollover_files=(2)
)
WITH
(
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=OFF)
GO
http://luanmorenodba.files.wordpress.com/2013/07/capture_thumb2.png?w=557&h=155
http://luanmorenodba.files.wordpress.com/2013/07/capture1_thumb1.png?w=615&h=237
Com esse recurso conseguimos aumentar nosso cache fazendo com que a mais páginas sejam alocadas e que a respista seja tão efeciente como o cache L1.