Buffer Pool Extensions a.k.a. BPE

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

Autor: SQLCrespi

Rodrigo Crespi é MVP Data Platform, MCT, MCSE, MCSA, MCIT, MCDBA e MCP em SQL Server, possuí outras certificações em .NET Framework, Dynamics CRM e etc. Iniciou trabalhando com SQL Server na versão 6.5 na época como desenvolvedor, logo direcionou a sua carreira para a área de administração de bancos de dados. Atualmente é Sócio da CrespiDB – Soluções em Plataformas de Dados, além de professor universitário na Uniftec – Caxias do Sul Autor de diversos artigos publicados em sites e revistas sobre SQL Server e engenharia de software, mantem o canal SQLCrespi no Youtube, o blog SQLCrespi, além de ser dedicado ao SQL Server RS PASS Chapter Leader do grupo de profissionais em SQL Server do Rio Grande do Sul.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s