Esta não é uma nova feature do SQL Server, ela foi introduzida no SQL Server 2014, porém eu tive algumas discussões sobre o assunto na semana passada e resolvi falar um pouco desta feature.
O que é?
Quem já teve a oportunidade de ver uma palestra minha sobre Query Internals já sabe que os dados são lidos da memória e não do disco.
Uma vez que os dados são alterados, ele estará marcado como sujo (as famosas dirty pages). Eventualmente as páginas sujas são gravadas em disco e marcadas como limpa, páginas limpas podem ser liberadas da memória quando o cache de dados (o buffer pool) sofrer pressão. Neste caso, quando os dados forem consultados novamente eles serão lidos do disco e alocados na memória e se a pressão sobre o buffer pool continuar algumas outras páginas serão expurgadas da memória novamente, causando um ciclo prejudicial a performance.
Rodrigo: Eu possuo um servidor de SQL Server com mais de 128GB de memória RAM onde isso me ajuda? Se você tiver uma licença de SQL Server Enterprise este recurso poderá não ser útil para você.
Na minha opinião este recurso, na maioria dos casos, poderá ser útil para quem possui um servidor com licenciamento Standard, cujo o limite de memória é de 128GB de RAM e não pode subir para uma edição Enterprise.
Além disso só será útil se houver um SSD disponível para alocar o BPE. Imagine cenários de diversas máquinas virtualizadas onde há SSD sobrando e a memória está limitada.
Outro cenário do qual eu já utilizei foi um cliente que havia um SSD disponível, mas, para adquirir memória RAM para o servidor ele precisava de um processo demorado entre aprovação, compra e instalação; neste caso em particular utilizamos o BPE até a chegada das memórias.
Conceito
O conceito por trás do BPE é muito similar a área de swap ou também conhecida como memória virtual.
No caso do BPE o SQL Server utilizará um espaço no disco para armazenar as páginas limpas, mais precisamente o BPE irá transacionar páginas de dados não modificadas que poderia ter sido expurgada para fora do buffer pool.
Novamente quero deixar claro que a ideia não é substituir a memória RAM.
Como configurar
Pessoal, para ser mais didático fiz um vídeo que explica somente como ativar e alterar. Veja aqui e lembrem-se de assinar o meu canal no youtube. ; )
Os scripts seguem abaixo:
/*
Vídeo sobre BPE
Ativando
*/
USE master
GO
sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON
(FILENAME = 'c:\cache\sqlserver.bpe', SIZE= 4GB);
GO
/*
Verificando
*/
SELECT path ,
state_description ,
current_size_in_kb / 1024 AS tamanhoMB
FROM sys.dm_os_buffer_pool_extension_configuration;
/*
Modificando
*/
--para modificar primeiro para o BPE
--Neste ponto ele deleta o arquivo
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO
--Em seguida faz a alteração
alter server configuration
set buffer pool extension on
(filename = 'c:\cache\sqlpool.bpe, size = 5Gb');
/*
TESTE
*/
sys.sp_configure 'max server memory (MB)', '2000';
GO
RECONFIGURE WITH OVERRIDE;
GO
--Limpa os buffers
DBCC DROPCLEANBUFFERS;
GO
--
SELECT *
FROM sys.dm_os_buffer_descriptors
WHERE is_in_bpool_extension = 1;
--verifica páginas no BPE do adventurworks2
SELECT CASE is_in_bpool_extension
WHEN 1 THEN 'BPE'
ELSE 'RAM'
END AS location ,
COUNT(*) AS NrPaginas ,
COUNT(*) * 8 / 1024 AS TamanhoMB ,
COUNT(*) * 100. / ( SELECT COUNT(*)
FROM sys.dm_os_buffer_descriptors
) AS Percentual
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID('AdventureWorks2')
GROUP BY is_in_bpool_extension;
--
--em outra guia
USE adventureworks2
GO
SELECT *
FROM [Person].[Address]
GO
SELECT *
FROM Production.product
GO
SELECT o.*
FROM [Sales].[SalesOrderHeader] o
INNER JOIN [Sales].[SalesOrderDetail] d ON o.salesorderid = d.salesorderid
SELECT *
FROM person.person
GO
SELECT *
FROM person.personphone
GO
SELECT *
FROM Production.culture
Conclusão
Esta é uma feature que poderá ser boa ou não, isso irá depender da estrutura do ambiente.
Meu conselho, façam os testes analisem se as páginas de dados estão com maior percentual alocados no BPE. Pode ser que está solução não esteja produzindo melhoras no ambiente.
Façam os testes e comentem aqui.
Fonte:
https://msdn.microsoft.com/en-us/library/dn133176(v=sql.130).aspx
https://msdn.microsoft.com/en-us/library/dn133176.aspx
Abraço, Rodrigo