MS SQL Server - Arquitetura

Classificamos a arquitetura do SQL Server nas seguintes partes para fácil compreensão -

  • Arquitetura geral
  • Arquitetura de memória
  • Arquitetura de arquivo de dados
  • Arquitetura do arquivo de log

Arquitetura Geral

Client - Onde a solicitação foi iniciada.

Query - Consulta SQL que é uma linguagem de alto nível.

Logical Units - Palavras-chave, expressões e operadores, etc.

N/W Packets - Código relacionado à rede.

Protocols - No SQL Server temos 4 protocolos.

  • Memória compartilhada (para conexões locais e solução de problemas).

  • Pipes nomeados (para conexões que estão em conectividade LAN).

  • TCP / IP (para conexões em conectividade WAN).

  • VIA-Virtual Interface Adapter (requer hardware especial para configuração pelo fornecedor e também obsoleto da versão SQL 2012).

Server - Onde o SQL Services foi instalado e os bancos de dados residem.

Relational Engine- Aqui é onde a execução real será feita. Ele contém analisador de consulta, otimizador de consulta e executor de consulta.

Query Parser (Command Parser) and Compiler (Translator) - Isso verificará a sintaxe da consulta e converterá a consulta em linguagem de máquina.

Query Optimizer - Ele irá preparar o plano de execução como saída, tendo como entrada a consulta, estatísticas e árvore Algebrizer.

Execution Plan - É como um roteiro, que contém a ordem de todas as etapas a serem realizadas como parte da execução da consulta.

Query Executor - É aqui que a consulta será executada passo a passo com o auxílio do plano de execução e também o mecanismo de armazenamento será contatado.

Storage Engine - É responsável pelo armazenamento e recuperação de dados no sistema de armazenamento (disco, SAN, etc.), manipulação de dados, bloqueio e gerenciamento de transações.

SQL OS- Isso fica entre a máquina host (sistema operacional Windows) e o SQL Server. Todas as atividades realizadas no mecanismo de banco de dados são atendidas pelo sistema operacional SQL. O SQL OS fornece vários serviços de sistema operacional, como gerenciamento de memória, lida com buffer pool, buffer de log e detecção de deadlock usando a estrutura de bloqueio e bloqueio.

Checkpoint Process- Checkpoint é um processo interno que grava todas as páginas sujas (páginas modificadas) do cache de buffer para o disco físico. Além disso, ele também grava os registros de log do buffer de log para o arquivo físico. A gravação de páginas sujas do cache de buffer em um arquivo de dados também é conhecida como Hardening of dirty pages.

É um processo dedicado e executado automaticamente pelo SQL Server em intervalos específicos. O SQL Server executa o processo de checkpoint para cada banco de dados individualmente. O Checkpoint ajuda a reduzir o tempo de recuperação do SQL Server no caso de desligamento inesperado ou falha / falha do sistema.

Pontos de verificação no SQL Server

No SQL Server 2012, existem quatro tipos de checkpoints -

  • Automatic - Este é o ponto de verificação mais comum executado como um processo em segundo plano para garantir que o banco de dados SQL Server possa ser recuperado no limite de tempo definido pelo Intervalo de recuperação - Opção de configuração do servidor.

  • Indirect- Isso é novo no SQL Server 2012. Ele também é executado em segundo plano, mas para atender a um tempo de recuperação de destino especificado pelo usuário para o banco de dados específico onde a opção foi configurada. Depois que o Target_Recovery_Time para um determinado banco de dados for selecionado, isso substituirá o intervalo de recuperação especificado para o servidor e evitará o ponto de verificação automático em tal banco de dados.

  • Manual- Este é executado como qualquer outra instrução T-SQL, assim que você emitir o comando de ponto de verificação, ele será executado até sua conclusão. O ponto de verificação manual é executado apenas para seu banco de dados atual. Você também pode especificar o Checkpoint_Duration que é opcional - esta duração especifica o tempo em que você deseja que o seu checkpoint seja concluído.

  • Internal- Como usuário, você não pode controlar o ponto de verificação interno. Emitido em operações específicas, como

    • O desligamento inicia uma operação de ponto de verificação em todos os bancos de dados, exceto quando o desligamento não é limpo (desligamento com nowait).

    • Se o modelo de recuperação for alterado de Full \ Bulk-logging para Simple.

    • Ao fazer backup do banco de dados.

    • Se o seu banco de dados estiver em um modelo de recuperação simples, o processo de checkpoint é executado automaticamente quando o log fica 70% cheio ou com base na opção do servidor - intervalo de recuperação.

    • Alterar o comando do banco de dados para adicionar ou remover um arquivo de dados \ log também inicia um ponto de verificação.

    • O ponto de verificação também ocorre quando o modelo de recuperação do banco de dados é registrado em massa e uma operação com registro mínimo é executada.

    • Criação de instantâneos de banco de dados.

  • Lazy Writer Process- O gravador lento enviará páginas sujas para o disco por um motivo totalmente diferente, porque ele precisa liberar memória no buffer pool. Isso acontece quando o servidor SQL fica sob pressão de memória. Pelo que eu sei, isso é controlado por um processo interno e não há configuração para isso.

O servidor SQL monitora constantemente o uso de memória para avaliar a contenção de recursos (ou disponibilidade); sua função é garantir que haja uma certa quantidade de espaço livre disponível o tempo todo. Como parte desse processo, quando ele percebe qualquer contenção de recurso, ele aciona o Lazy Writer para liberar algumas páginas na memória gravando páginas sujas no disco. Ele emprega o algoritmo Least Recent Used (LRU) para decidir quais páginas devem ser descarregadas no disco.

Se Lazy Writer estiver sempre ativo, isso pode indicar gargalo de memória.

Arquitetura de Memória

A seguir estão alguns dos recursos mais importantes da arquitetura de memória.

  • Um dos principais objetivos de design de todo software de banco de dados é minimizar a E / S de disco, porque as leituras e gravações de disco estão entre as operações que consomem mais recursos.

  • A memória no Windows pode ser chamada com Espaço de Endereço Virtual, compartilhado pelo modo Kernel (modo OS) e modo Usuário (Aplicativo como SQL Server).

  • O "espaço de endereço do usuário" do SQL Server é dividido em duas regiões: MemToLeave e Buffer Pool.

  • O tamanho de MemToLeave (MTL) e Buffer Pool (BPool) é determinado pelo SQL Server durante a inicialização.

  • Buffer managementé um componente chave para alcançar alta eficiência de E / S. O componente de gerenciamento de buffer consiste em dois mecanismos: o gerenciador de buffer para acessar e atualizar as páginas do banco de dados e o buffer pool para reduzir a E / S do arquivo de banco de dados.

  • O buffer pool é dividido em várias seções. Os mais importantes são o cache de buffer (também conhecido como cache de dados) e o cache de procedimento.Buffer cachemantém as páginas de dados na memória para que os dados acessados ​​com frequência possam ser recuperados do cache. A alternativa seria ler as páginas de dados do disco. A leitura das páginas de dados do cache otimiza o desempenho, minimizando o número de operações de E / S necessárias que são inerentemente mais lentas do que recuperar dados da memória.

  • Procedure cachemantém o procedimento armazenado e os planos de execução de consulta para minimizar o número de vezes que os planos de consulta devem ser gerados. Você pode descobrir informações sobre o tamanho e a atividade no cache de procedimento usando a instrução DBCC PROCCACHE.

Outras partes do pool de buffer incluem -

  • System level data structures - Contém dados de nível de instância do SQL Server sobre bancos de dados e bloqueios.

  • Log cache - Reservado para ler e gravar páginas de log de transações.

  • Connection context- Cada conexão com a instância possui uma pequena área de memória para registrar o estado atual da conexão. Essas informações incluem procedimento armazenado e parâmetros de função definidos pelo usuário, posições do cursor e muito mais.

  • Stack space - O Windows aloca espaço de pilha para cada thread iniciado pelo SQL Server.

Arquitetura de arquivo de dados

A arquitetura do arquivo de dados tem os seguintes componentes -

Grupos de arquivos

Os arquivos de banco de dados podem ser agrupados em grupos de arquivos para fins de alocação e administração. Nenhum arquivo pode ser membro de mais de um grupo de arquivos. Os arquivos de log nunca fazem parte de um grupo de arquivos. O espaço de log é gerenciado separadamente do espaço de dados.

Existem dois tipos de grupos de arquivos no SQL Server, Primário e Definido pelo usuário. O grupo de arquivos primário contém o arquivo de dados primário e quaisquer outros arquivos não atribuídos especificamente a outro grupo de arquivos. Todas as páginas das tabelas do sistema são alocadas no grupo de arquivos principal. Grupos de arquivos definidos pelo usuário são quaisquer grupos de arquivos especificados usando a palavra-chave do grupo de arquivos em criar banco de dados ou alterar a instrução do banco de dados.

Um grupo de arquivos em cada banco de dados opera como o grupo de arquivos padrão. Quando o SQL Server aloca uma página para uma tabela ou índice para o qual nenhum grupo de arquivos foi especificado quando foram criados, as páginas são alocadas do grupo de arquivos padrão. Para mudar o grupo de arquivos padrão de um grupo para outro, ele deve ter a função db fixa db_owner.

Por padrão, o grupo de arquivos principal é o grupo de arquivos padrão. O usuário deve ter a função de banco de dados fixa db_owner para fazer backup de arquivos e grupos de arquivos individualmente.

arquivos

Os bancos de dados têm três tipos de arquivos - arquivo de dados primário, arquivo de dados secundário e arquivo de log. O arquivo de dados primário é o ponto de partida do banco de dados e aponta para os outros arquivos no banco de dados.

Cada banco de dados possui um arquivo de dados primário. Podemos dar qualquer extensão para o arquivo de dados principal, mas a extensão recomendada é.mdf. O arquivo de dados secundário é um arquivo diferente do arquivo de dados primário nesse banco de dados. Alguns bancos de dados podem ter vários arquivos de dados secundários. Alguns bancos de dados podem não ter um único arquivo de dados secundário. A extensão recomendada para o arquivo de dados secundários é.ndf.

Os arquivos de log contêm todas as informações de log usadas para recuperar o banco de dados. O banco de dados deve ter pelo menos um arquivo de log. Podemos ter vários arquivos de log para um banco de dados. A extensão recomendada para o arquivo de log é.ldf.

A localização de todos os arquivos em um banco de dados é registrada no banco de dados mestre e no arquivo principal do banco de dados. Na maioria das vezes, o mecanismo de banco de dados usa o local do arquivo do banco de dados mestre.

Os arquivos têm dois nomes - lógico e físico. O nome lógico é usado para se referir ao arquivo em todas as instruções T-SQL. O nome físico é OS_file_name, ele deve seguir as regras do SO. Os arquivos de dados e log podem ser colocados em sistemas de arquivos FAT ou NTFS, mas não podem ser colocados em sistemas de arquivos compactados. Pode haver até 32.767 arquivos em um banco de dados.

Extensões

As extensões são unidades básicas nas quais o espaço é alocado para tabelas e índices. Uma extensão é de 8 páginas contíguas ou 64 KB. O SQL Server tem dois tipos de extensão - Uniforme e Mista. As extensões uniformes são compostas por um único objeto. Extensões mistas são compartilhadas por até oito objetos.

Páginas

É a unidade fundamental de armazenamento de dados no MS SQL Server. O tamanho da página é de 8 KB. O início de cada página é um cabeçalho de 96 bytes usado para armazenar informações do sistema, como tipo de página, quantidade de espaço livre na página e id do objeto proprietário da página. Existem 9 tipos de páginas de dados no SQL Server.

  • Data - Linhas de dados com todos os dados, exceto texto, ntext e dados de imagem.

  • Index - Entradas de índice.

  • Tex\Image - Dados de texto, imagem e ntext.

  • GAM - Informações sobre extensões alocadas.

  • SGAM - Informações sobre extensões alocadas no nível do sistema.

  • Page Free Space (PFS) - Informações sobre o espaço livre disponível nas páginas.

  • Index Allocation Map (IAM) - Informações sobre extensões usadas por uma tabela ou índice.

  • Bulk Changed Map (BCM) - Informações sobre extensões modificadas por operações em massa desde a última instrução de log de backup.

  • Differential Changed Map (DCM) - Informações sobre extensões que foram alteradas desde a última instrução de backup do banco de dados.

Arquitetura do arquivo de log

O log de transações do SQL Server opera logicamente como se o log de transações fosse uma string de registros de log. Cada registro de log é identificado por Log Sequence Number (LSN). Cada registro de log contém o ID da transação à qual pertence.

Os registros de log para modificações de dados registram a operação lógica realizada ou gravam as imagens anteriores e posteriores dos dados modificados. A imagem anterior é uma cópia dos dados antes da operação ser executada; a pós-imagem é uma cópia dos dados após a realização da operação.

As etapas para recuperar uma operação dependem do tipo de registro de log -

  • Operação lógica registrada.
    • Para avançar a operação lógica, a operação é executada novamente.
    • Para reverter a operação lógica, a operação lógica reversa é executada.
  • Antes e depois da imagem registrada.
    • Para avançar a operação, a pós-imagem é aplicada.
    • Para reverter a operação, a imagem anterior é aplicada.

Diferentes tipos de operações são registrados no log de transações. Essas operações incluem -

  • O início e o final de cada transação.

  • Cada modificação de dados (inserir, atualizar ou excluir). Isso inclui alterações por procedimentos armazenados do sistema ou instruções de linguagem de definição de dados (DDL) em qualquer tabela, incluindo tabelas do sistema.

  • Cada extensão e alocação ou desalocação de página.

  • Criação ou eliminação de uma tabela ou índice.

As operações de reversão também são registradas. Cada transação reserva espaço no log de transações para garantir que exista espaço de log suficiente para suportar uma reversão causada por uma instrução de reversão explícita ou se um erro for encontrado. Este espaço reservado é liberado quando a transação é concluída.

A seção do arquivo de log do primeiro registro de log que deve estar presente para uma reversão bem-sucedida de todo o banco de dados para o último registro de log é chamada de parte ativa do log ou log ativo. Esta é a seção do log necessária para uma recuperação completa do banco de dados. Nenhuma parte do log ativo pode ser truncada. O LSN desse primeiro registro de log é conhecido como o LSN de recuperação mínima (LSN mínimo).

O SQL Server Database Engine divide cada arquivo de log físico internamente em vários arquivos de log virtuais. Os arquivos de log virtuais não têm tamanho fixo e não há um número fixo de arquivos de log virtuais para um arquivo de log físico.

O Mecanismo de Banco de Dados escolhe o tamanho dos arquivos de log virtuais dinamicamente enquanto cria ou estende os arquivos de log. O Mecanismo de Banco de Dados tenta manter um pequeno número de arquivos virtuais. O tamanho ou o número de arquivos de log virtuais não podem ser configurados ou definidos pelos administradores. A única vez que os arquivos de log virtuais afetam o desempenho do sistema é se os arquivos de log físicos são definidos por valores de tamanho pequeno e growth_increment.

O valor de tamanho é o tamanho inicial para o arquivo de log e o valor de growth_increment é a quantidade de espaço adicionado ao arquivo sempre que um novo espaço é necessário. Se os arquivos de log crescerem para um tamanho grande devido a muitos pequenos incrementos, eles terão muitos arquivos de log virtuais. Isso pode retardar a inicialização do banco de dados e também registrar operações de backup e restauração.

Recomendamos que você atribua aos arquivos de log um valor de tamanho próximo ao tamanho final necessário e também tenha um valor growth_increment relativamente grande. O SQL Server usa um log write-ahead (WAL), que garante que nenhuma modificação de dados seja gravada no disco antes que o registro de log associado seja gravado no disco. Isso mantém as propriedades ACID para uma transação.