Conhecendo o “Buffer Cache Database” no Microsoft SQL Server 2012 e 2014.

Boa tarde, Comunidade e Amantes do Microsoft SQL Server.

Meu Deus, como o tempo esta passando de forma avassaladora, já estamos no mês de Setembro e daqui a alguns dias estaremos falando sobre o que aconteceu neste ano de 2014, nossa isso acaba sendo assustador. Mesmo assim, vamos em frente ainda temos muito para conhecer e produzir até o final de 2014.

Falando em conhecer e produzir, estou novamente aqui no meu blog, compartilhando com vocês mais um dos milhares de segredos, recursos, funcionalidades e praticidades que o Fantásico Microsoft SQL Server nos apresenta e permita utilizar.

Dentre estes segredos e recursos, quero mostrar para vocês algo muito simples de ser utilizado e entendido, mas que pode ajudar em muito a vida de Administrador de Banco de Dados em suas atividades diárias, estou me referindo ao “Buffer Cache Database”.

É isso mesmo “Buffer Cache Database”!!! Você conhece? Já teve a necessidade de utilizar ou descobrir para que ele serve? Ele existe? Ou somente é uma expressão ou termo técnico?

Eu mesmo no primeiro momento que ouvi este termo e me apresentaram fiquei um pouco perdido, sem saber o que este elemento pode me apresentar de informações úteis, mas com o passar do tempo e necessidade foi possível analisar e entender o quanto este pequeno recurso pode nos ajudar, principalmente em situações de alocação de uso de memória por parte do SQL Server.

Pensando justamente neste tipo de necessidade, decidi escrever este pequeno artigo, para tentar mostrar a vocês uma possível como podemos obter informações sobre o “Buffer Cache Database” e o quanto este Buffer faz parte do nosso ambiente.

Então mãos a massa, vamos descobrir um pouquinho sobre o “Buffer Cache Database”, saber do que ele composto e como podemos trabalhar com ele.

Introdução

O nome “Buffer Cache Database” a princípio pode parecer um bicho de sete cabeças, mas na verdade esta muito longe disso, basicamente pode ser entendido ou tratado como um Cache: “Uma área de armazenamento onde dados ou processos freqüentemente utilizados são guardados para um acesso futuro mais rápido, poupando tempo e uso desnecessário do seu hardware.”

O SQL Server é composto por um grande conjunto de Caches, um dos mais conhecidos é o Bufer Pool: Buffer reponsável em definir a quantidade possível de memória que poderá ser utilizada pelo SQL Server. Através deste recuros, temos a capacidade de utilizar “endereçar” ou “instanciar” o máximo de memória possível disponível em nosso Sistema Operacional sem afetar sua performance. De uma forma bem simples, o Buffer Pool tem como função realizar um balanceamento “equilíbrio” e “compartilhamento” de memória entre uma instância ou Servidor SQL Server através do seu Sistema Operacional do SQL Server chamado SQLOS em conjunto com os Memory Brokers e o próprio Sistema Operacional.

Mas, então, o que seria o “Buffer Cache Database”? Ele Existe? Ou somente é uma expressão ou termo técnico? Como ele é formado?

A resposta é muito simples e direta. O “Buffer Cache Database” não existe, pode ser entendido ou tratado como uma termo ou expressão técnica se comparado com Database Cache existente no Oracle. Na verdade é a forma natural que o SQL Server trabalha para organizar, estruturar e armazenar em páginas de dados que compõem os bancos de dados. Algo que pode ocorrer em diversos momentos, tanto na inicialização de uma instância ou servidor SQL Server, como também, quando um banco de dados realiza o alocação de suas páginas de dados em memória.

Isso mesmo páginas de dados, pequenas estrutura lógicas que armazenam os dados de forma física e temporária. As páginas de dados são interpretados pelo SQL Server da seguinte forma:

  • As páginas de dados são numeradas em sequência, iniciando com zero (0) para a primeira página do arquivo.
  • O tamanho padrão de uma página é 8 KB. Isso significa que qualquer banco de dados criado no SQL Server terá 128 páginas por megabyte.
  • Cada página começa com um cabeçalho de 96 bytes usado para armazenar informações de sistema sobre a página. Essas informações incluem o número de página, o tipo de página, a quantidade de espaço livre na página e a ID de unidade de alocação do objeto que possui a página.
  • Para identificar de forma exclusiva uma página em um banco de dados, são necessários ID do arquivo e número de página. Cada arquivo em um banco de dados tem um número de ID de arquivo exclusivo. Para tentar ilustrar, o exemplo a Figura 1 seguir mostra os números de página em um banco de dados que tem um arquivo de dados primário de 1 MB:
  • Com isso, podemos dizer que o “Buffer Cache Database” é criado e mantido, decorrente da existência das páginas de dados que forma nosso banco, isso nos permite dizer que a partir do momento que realizamos a criação de um banco de dados com tamanho de 1 MB, já estaremos fazendo uso desta estrutura de alocação e nosso novo  banco já terá um “Buffer Cache Database” inicial de 128 páginas.

Buffer01

Figura 1 – Estrutura de Páginas de Dados que compõem a estrutura de Banco de Dados no Microsoft SQL Server.

Caso você queira saber mais sobre páginas de dados, acesse: http://technet.microsoft.com/pt-br/library/ms190969(v=SQL.105).aspx

 

Obtendo o “Buffer Cache Database”

Para obtermos o “Buffer Cache Database” que atualmente esta sendo gerado e consumido pelo nosso SQL Server, vamos fazer uso do Código denominado Parte 1, apresentando abaixo:

— Parte 1 – Obtendo Buffer Cache por Banco de Dados –

Select DB_NAME(database_id) As ‘Database’,

COUNT(*) * 8 / 1024.0 As ‘Cached Size (MB)’

From sys.dm_os_buffer_descriptors

Where database_id <> 32767

Group By DB_NAME(database_id)

Order By ‘Cached Size (MB)’ Desc

 

A Figura 2 apresentada abaixo ilustra o retorno dos “Buffer Cache Database” obtidos no meu SQL Server, observe que o Banco de Dados que neste momento esta gerando o maior Buffer Cache é o Banco Cars com 1.078 MBs.

Buffer02

Figura 2 – Relação de “Buffer Cache Database”.

Nosso próximo passo, será forçar o SQL Server a gerar um novo “Buffer Cache Database”, neste caso, criando o nosso próprio ambiente, para isso será necessário criar um novo banco de dados, denominado: BufferCacheDatabase e uma Tabela denominada BufferCacheDatabaseTable.

Gerando o “Buffer Cache Database”

Para criarmos nosso ambiente, vamos utilizar o Código denominado Parte 2, conforme apresenta abaixo:

— Parte 2 – Criando Ambiente para Gerar Buffer Cache Databae –

Create Database BufferCacheDatabase

Go

 

Use BufferCacheDatabase

Go

 

Create Table BufferCacheDatabaseTable

(BufferID Int Identity Primary Key Clustered,

BufferDescription Varchar(50) Default ‘Buffer Table’,

BufferDateTime DateTime Default GetDate())

Go

 

Observação: Vale ressaltar que este banco de dados esta criado com o tamanho de 3 Megabyte, pois o banco de dados de sistema Model existente no meu Servidor SQL Server, esta configurado neste momento justamente com este tamanho de arquivo, que representa o tamanho mínimo, sendo assim, os novos bancos de dados criados de forma rápida vão herdar esta configuração.

Legal, legal, nosso ambiente já esta criado e pronto para começarmos a brincar!!!! O próximo passo será obter o quando de Buffer Cache foi gerado após a criação deste novo banco de dados, onde vamos somente consultar o quantidade de cache deste Banco, para isso, iremos utilizar o Código denominado Parte 3.

— Parte 3 – Consultando o “Buffer Cache Database” gerado para este novo Banco –

Select DB_NAME(database_id) As ‘Database’,

COUNT(*) * 8 / 1024.0 As ‘Cached Size (MB)’

From sys.dm_os_buffer_descriptors

Where database_id = DB_ID()

Group By DB_NAME(database_id)

 

Ao executar este código, o SQL Server deverá retornar a quantidade de Buffer Cache, criado e alocado para o Banco de Dados: BufferCacheDatabase, conforme apresenta a Figura 3 a seguir:

Buffer03

Figura 3 – Total de “Buffer Cache Database” alocado para o Banco de Dados – BufferCacheDatabase.

Muito bem, como eu destaquei anteriormente, uma simples transação de criação de um novo banco de dados gera Buffer, neste caso nosso banco de dados esta apresentando o Buffer Cache de 1.304 MBs, isso nos faz pensar o quanto o SQL Server tem que trabalhar para controlar e manter em memória este recurso.

Nossa caminhada ainda não acabou mas esta quase lá, na sequência vamos obter as informações sobre “Buffer Cache Database” no nível de Tabela, onde nossa tabela: BufferCacheDatabaseTabela estará vazia e posteriormente após algumas transações de Inserção de dados.

Comecaremos pela Inserção de 2000 linhas de registro na tabela BufferCacheDatabaseTable, utilizando o Código denominado Parte 4, na sequência vamos consultar total estrutura de páginas de dados que é será alocado para esta tabela, o que nos dará mais comprovações do quanto o “Buffer Cache Database” é mantido pelo SQL Server.

— Parte 4 – Inserindo 2000 linhas na tabela BufferCacheDatabaseTable —

Insert Into BufferCacheDatabaseTable Default Values

Go 2000

 

Como você percebeu acabamos de inserir 2000 linhas de registro em nossa tabela, de forma simples e rápida, agora vamos consultar as informações sobre as páginas de dados que forma esta tabela, começando Código denominado Parte 5, onde teremos alguns detalhes, como quantidade de páginas de dados utilizadas e total de página de dados. A Figura 4, ilustra o resultado do processamento deste código.

— Parte 5 – Consultando informações sobre as páginas de dados –

Select ST.Name As ‘Tabela’,

SP.rows As ‘Linhas’,

SA.data_pages As ‘Páginas de Dados’,

SA.used_pages As ‘Páginas de Dados Utilizadas’,

SA.total_pages As ‘Total de Páginas de Dados’

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Go

Note que estamos utilizando as Visões de Sistema:

  • Sys.tables;
  • Sys.partitions; e
  • Sys. Allocations_Units.

Caso você queira saber mais sobre estas Views de Sistema, consulte as Referências no final deste artigo.

Buffer04

Figura 4 – Informações sobre as páginas de dados que forma a tabela BufferCacheDatabaseTable.

Perfeito, neste momento temos boa parte das informações do nosso ambiente, inclusive alguns detalhes sobre as páginas de dados. Mas se observarmos com cuidado a Figura 4, como podemos ter o Total de Páginas de Dados com o valor 17 se a coluna Páginas de Dados esta com o valor de 10?

Será que existe alguma coisa de errado? De forma alguma, este valor esta correto, esta diferença existe porque a coluna data_pages existente na System View: sys.allocation_units, apresenta como valor para quantidade de páginas de dados, somente as páginas que forma a tabela, excluíndo deste valor páginas de índice internas e páginas do gerenciamento de alocações. Por isso este valor, então podemos dizer que nossa tabela possui:

  • O total de 17 páginas de dados considerando páginas para índices e alocações internas; e
  • O Total de 10 páginas somente para armazenamento de dados que forma a tabela.

Para deixar mas claro esta constatação, vamos utilizar o Código denominado Parte 6, onde será possível ver todo detalhamento e distribuição das alocações de páginas de dados, registros por páginas e espaço livre em bytes para armazenamento de dados em cada página, conforme ilustra a Figura 5:

— Parte 6 – Detalhamento da Alocação de Dados por Página de Dados –

Select ST.Name As ‘Tabela’,

SB.page_id As ‘Id Página’,

SB.page_type As ‘Tipo da Página’,

SB.Row_Count As ‘Linhas por Página’,

SB.free_space_in_bytes As ‘Espaço Livre em Bytes’

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Inner Join sys.dm_os_buffer_descriptors SB

On SB.allocation_unit_id = SA.allocation_unit_id

Order By SB.page_type Asc

Go

 Buffer05

 

 

 

 

 

Figura 5 – Distribuição de dados alocados por página de dados, quantidade de linhas e espaço livre disponível.

Prontinho, sensacional, aqui esta a toda estrutura de alocação e distribuição de dados em suas respectivas páginas de dados que forma a nossa tabela BufferCacheDatabaseTable.

 

Ufa, é isso, conseguimos chegar vivos e salvos ao final de mais esta caminhada. Vou encerrar por aqui mais este artigo, as demais análises e observações vou deixar para você. Um detalhe importante, repita novamente a execução do Código Parte 3 e verifique que o valor calculado do “Buffer Cache Database” provavelmente foi alterado.

Conclusão

Falar deste tipo recurso que na verdade não existe no Microsoft SQL Server, mas esta presente devido a maneira que as páginas de dados são alocadas em memória pode parecer ser fácil, o duro o conseguir encontrar uma forma de demonstrar como ele acontece, de que forma o “Buffer Cache Database” é criado, gerado, alocado e mantido.

Como destacado o grande segredo de qualquer tipo de Cache é saber a forma correta de armazenar o dado ou conjunto de dados para que tanto o Software como também o Hardware não sejam impactos. O “Buffer Cache Database” faz justamente isso de uma forma muito simples, prática e elegante, além disso, ele nos permite saber e entender como o mesmo esta ocorrendo. Em conjunto com o grande conjunto de Visões de Sistemas e Visões de Gerenciamento Dinâmico existentes no SQL Sever desde a versão 2005, este caminho muito ainda mais tranquilo.

Trabalhando de forma invisível como os demais recursos de Buffer, o “Buffer Cache Database”, oferece para o Database Engine e também para toda estrutura de armazenamento de dados do SQL Server, a flexibilidade para pode ser trabalhar com objetos recêm criados, como também, com aqueles que estão em uso ou foram utilizados em um determinado período de tempo, com isso, podemos ter alguns ganhos de performance na busca por dados e funcionalidades do nosso dia-á-dia.

Entender o Buffer Pool e também o Buffer Cache Database é de grande importância para qualquer Administrador de Banco de Dados, pois ambos estão diretamente trabalhando com a memória existente tanto do SQL Server e também o Windows.

É neste cenário que o “Buffer Cache Database” faz o seu papel, instânciado porções de memória para alocação de seus objetos, neste caso, páginas de dados e índices para o melhor funcionamento do SQL Server.

Referências

– sys.tables: http://msdn.microsoft.com/pt-br/library/ms187406.aspx

– sys.allocation_units: http://msdn.microsoft.com/en-us/library/ms189792.aspx

– sys.dm_os_buffer_pool_extension_configuration: http://msdn.microsoft.com/en-us/library/dn133204.aspx

– sys.partitions: http://msdn.microsoft.com/en-us/library/ms175012.aspx

– sys.dm_os_buffer_descriptors:  http://msdn.microsoft.com/en-us/library/ms173442.aspx

——————————————————————————————————————————-

Agradeço a sua visita, espero que você tenha gostado e que este conteúdo possa te ajudar.

Um grande abraço.

Nos encontramos em breve.

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

7 comentários em “Conhecendo o “Buffer Cache Database” no Microsoft SQL Server 2012 e 2014.”

    1. Rodrigo, boa noite.

      Obrigado por sua visita ao meu blog.

      A diferença é simples, o Buffer Pool é a área de contingência de memória utilizada pelo SQL Server para fazer alocação em relação a memória RAM disponível no Sistema Operacional, em outras palavras o Buffer Pool é o responsável em conversar com o Windows e se entender no uso e alocação de memória.

      Já o Buffer Cache Database é uma área de memória interna utilizada pelo SQL Server dentro da faixa de memória RAM disponível para ele, para manter as páginas de dados de tabelas e índices em memória, ajudando no processo de consulta dos dados. O Buffer Cache Database só existe devido ao fato do SQL Server trabalhar com o conceito de Cache de Plano de Dados e Cache de Plano de Execução. Quando o Serviço do SQL Server é inicializado todos os bancos de dados recebem uma solicitação interna e começam a gerar e oferecer alocação de Buffer Cache Database.

      Bom é isso, espero ter ajudado.

      Até mais.

      Curtir

  1. Galvão,

    Continuando a conversa estavamos tendo e respondendo aos pontos que vc colocou.

    1 – O Buffer Pool como você sabe é utilizado pelo SQL Server como um recurso responsável em alocação e gerenciar memória entre o SQL Server é o Windows.

    R: Na verdade o Buffer Pool não faz o gerenciamento da memoria como alocação, liberação de memoria. Na verdade quem faz isso é o SQLOS através de Memory Brokers.

    2 – Eu não destaquei Database Buffer Cache, mas sim Buffer Cache Database que pode ser a mesma coisa, mas é uma área criada internamente pelo SQL Server para alocar dentro de um período de tempo as páginas de dados de tabelas e índices que os bancos de dados estão trabalhando naquele momento. Inclusive se você fizer um pequeno teste, reinicializar uma instância SQL Server e observar, alguns bancos de dados de forma automática são alocado novamente.

    R: Como sabemos que Buffer Cache Database ou Database Buffer Cache não existe, é muito simples paginas de dados serem alocadas em memoria no momento da inicialização de uma instancia, pois nesse momento ocorrem as fases de recovery(undo e redo) no SQL Server. Algo muito trivial de acontecer são paginas de dados da master estarem em memoria logo após a inicialização, pois ai temos paginas de PFS, IAM entre outras.

    3 – Eu sei que isso tudo é aplicável ao SQL Server 2005, 2008 eu somente montei o artigo fazendo uma chamada para versões mais novas do SQL Server, talvez eu deva acrescentar esta observação no artigo.

    R: Concordo que deveria ser ajustado tanto essa parte como ressaltar a todos que não existe essa funcionalidade digamos “Database Buffer Cache” ou “Buffer Cache Database”

    Curtir

  2. Pedro , estou tentando importar dados de uma tabela para outra. É uns tabela que contém 21 milhões de registros. Mas no meio da importação ele gera a seguinte mensagem . Copying to [banco de dados].[dbo].[taba] (Error)
    Messages
    Information 0x4004800c Data Flow Task The buffer manager detected that the system was low on virtual memory,
    but was unable to swap out any buffers. 3 buffers were considered and 3 were locked. Either not enough memory
    is available to the pipeline because not enough is installed, other processes are using it, or too many buffers
    are locked.
    (SQL Server Import and Export Wizard) erro.

    Eu nunca me deparei com esse tipo de erro. Será que tem a ver com o que você postou ? Você sabe como eu faço para contornar essa situação? Existe algum ajuste de memória que eu preciso fazer. ?

    mas uns vez muito obrigado.

    Curtir

Deixe uma resposta

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