SQL Server Architextura (explicado)

MS SQL Server é uma arquitetura cliente-servidor. O processo do MS SQL Server começa com o aplicativo cliente enviando uma solicitação. O SQL Server aceita, processa e responde à solicitação com dados processados. Vamos discutir em detalhes toda a arquitetura mostrada abaixo:

Como mostra o diagrama abaixo, existem três componentes principais no SQL Server Architextura:

1. Camada de protocolo
2. Motor Relacional
3. Motor de Armazenamento

Camada de protocolo – SNI

MS SQL SERVER PROTOCOL LAYER suporta 3 tipos de servidor cliente Archiarquitetura. Começaremos com “Três tipos de cliente-servidor Archiarquitetura” que o MS SQL Server suporta.

Memoria compartilhada

Vamos reconsiderar um cenário de conversa matinal.

MÃE e TOM – Aqui Tom e sua mãe estavam no mesmo lugar lógico, ou seja, na casa deles. Tom conseguiu pedir café e mamãe serviu quente.

SERVIDOR MS SQL – Aqui você encontra MS SQL servidor fornece PROTOCOLO DE MEMÓRIA COMPARTILHADA. Aqui CLIENTE e MS SQL servidor executado na mesma máquina. Ambos podem se comunicar via protocolo de memória compartilhada.

Analogia: Vamos mapear entidades nos dois cenários acima. Podemos mapear facilmente Tom para cliente, mãe para servidor SQL, casa para máquina e comunicação verbal para protocolo de memória compartilhada.

Da mesa de configuração e instalação:

Para conexão com banco de dados local – In SQL Management Studio, a opção “Nome do servidor” pode ser

"."

“host local”

"127.0.0.1"

“Máquina\Instância”

TCP / IP

Agora considere que à noite, Tom está em clima de festa. Ele quer um café encomendado em uma cafeteria conhecida. A cafeteria fica a 10 km de sua casa.

Aqui Tom e Starbuck estão em locais físicos diferentes. Tom em casa e Starbucks no movimentado mercado. Eles estão se comunicando via rede celular. Da mesma forma, o MS SQL SERVER oferece a capacidade de interagir via Protocolo TCP / IP, onde CLIENT e MS SQL Server são remotos entre si e instalados em uma máquina separada.

Analogia: Vamos mapear entidades nos dois cenários acima. Podemos facilmente mapear Tom para Cliente, Starbuck para servidor SQL, Home/Market Place para local remoto e, finalmente, rede celular para protocolo TCP/IP.

Notas da mesa de Configuração/instalação:

• No SQL Management Studio – Para conexão via TCP\IP, a opção “Nome do Servidor” deve ser “Máquina\Instância do servidor”.
• O servidor SQL usa a porta 1433 em TCP/IP.

Tubulações nomeadas

Agora finalmente à noite, Tom quis tomar um chá verde light que sua vizinha Sierra prepara muito bem.

Aqui você encontra Tom e sua Vizinho, Sierra, estão na mesma físico localização, sendo vizinhos um do outro. Eles estão se comunicando através Intra-rede. Do mesmo modo, SERVIDOR MSSQL fornece a capacidade de interagir através do Tubo nomeado protocolo. Aqui o CLIENTE e SERVIDOR MSSQL estão em conexão via LAN.

Analogia: Vamos mapear entidades nos dois cenários acima. Podemos mapear facilmente Tom para cliente, Sierra para servidor SQL, vizinho para LAN e, finalmente, rede intra para protocolo de pipe nomeado.

Notas da mesa de Configuração/instalação:

• Para conexão via tubo nomeado. Esta opção está desabilitada por padrão e precisa ser habilitada pelo SQL Configuration Manager.

O que é TDS?

Agora que sabemos que existem três tipos de cliente-servidor Archiarquitetura, vamos dar uma olhada no TDS:

• TDS significa Fluxo de Dados Tabulares.
• Todos os 3 protocolos usam pacotes TDS. O TDS é encapsulado em pacotes de rede. Isso permite a transferência de dados da máquina cliente para a máquina servidora.
• O TDS foi desenvolvido inicialmente pela Sybase e agora é propriedade da Microsoft

Motor Relacional

O Mecanismo Relacional também é conhecido como Processador de Consultas. Tem o SQL Server componentes que determinam exatamente o que uma consulta precisa fazer e como ela pode ser feita melhor. É responsável pela execução das consultas do usuário, solicitando dados do mecanismo de armazenamento e processando os resultados retornados.

Como retratado no Archidiagrama estrutural existem 3 componentes principais do Motor Relacional. Vamos estudar os componentes em detalhes:

Analisador CMD

Os dados, uma vez recebidos da camada de protocolo, são então passados ​​para o Relational Engine. “Analisador CMD” é o primeiro componente do Relational Engine a receber os dados da Consulta. A principal tarefa do CMD Parser é verificar a consulta em busca de Erro sintático e semântico. Finalmente, gera uma árvore de consulta. Vamos discutir em detalhes.

Verificação sintática:

• Como qualquer outra linguagem de programação, o MS SQL também possui um conjunto predefinido de palavras-chave. Além disso, o SQL Server possui sua própria gramática que o SQL Server entende.
• SELECT, INSERT, UPDATE e muitos outros pertencem às listas de palavras-chave predefinidas do MS SQL.
• O CMD Parser faz verificação sintática. Se a entrada dos usuários não seguir estas regras de sintaxe ou gramática da linguagem, retorna um erro.

Exemplo: Digamos que um russo foi a um restaurante japonês. Ele pede fast food em russo. Infelizmente, o garçom só entende japonês. Qual seria o resultado mais óbvio?

A resposta é – o garçom não consegue processar o pedido posteriormente.

Não deve haver nenhum desvio na gramática ou no idioma aceito pelo SQL Server. Se houver, o SQL Server não poderá processá-lo e, portanto, retornará uma mensagem de erro.

Aprenderemos mais sobre a consulta MS SQL nos próximos tutoriais. No entanto, considere abaixo a sintaxe de consulta mais básica como

SELECT * from <TABLE_NAME>;

Agora, para ter uma ideia do que a sintaxe faz, diga se o usuário executa a consulta básica conforme abaixo:

SELECR * from <TABLE_NAME>

Observe que em vez de 'SELECT' o usuário digitou “SELECR”.

Resultado: O analisador CMD analisará esta instrução e lançará a mensagem de erro. Como “SELECR” não segue o nome e a gramática da palavra-chave predefinida. Aqui o CMD Parser esperava “SELECT”.

Verificação semântica:

• Isto é realizado por Normalizador.
• Em sua forma mais simples, ele verifica se o nome da coluna e o nome da tabela consultada existem no esquema. E se existir, vincule-o à Consulta. Isso também é conhecido como Confeção.
• A complexidade aumenta quando as consultas do usuário contêm VIEW. O Normalizer realiza a substituição pela definição de visualização armazenada internamente e muito mais.

Vamos entender isso com a ajuda do exemplo abaixo –

SELECT * from USER_ID

Resultado: O analisador CMD analisará esta instrução para verificação semântica. O analisador lançará uma mensagem de erro porque o Normalizer não encontrará a tabela solicitada (USER_ID) porque ela não existe.

Criar árvore de consulta:

• Esta etapa gera diferentes árvores de execução nas quais a consulta pode ser executada.
• Observe que todas as árvores diferentes têm a mesma saída desejada.

Optimizer

O trabalho do otimizador é criar um plano de execução para a consulta do usuário. Este é o plano que determinará como a consulta do usuário será executada.

Observe que nem todas as consultas são otimizadas. A otimização é feita para comandos DML (Data Modification Language) como SELECT, INSERT, DELETE e UPDATE. Essas consultas são primeiro marcadas e depois enviadas ao otimizador. Comandos DDL como CREATE e ALTER não são otimizados, mas são compilados em um formato interno. O custo da consulta é calculado com base em fatores como uso de CPU, uso de memória e necessidades de entrada/saída.

O papel do otimizador é encontrar o plano de execução mais barato, não o melhor e com boa relação custo-benefício.

Antes de entrarmos em mais detalhes técnicos do Optimizer, considere o exemplo da vida real abaixo:

Exemplo:

Digamos que você queira abrir uma conta bancária online. Você já conhece um banco que leva no máximo 2 dias para abrir uma conta. Mas você também tem uma lista de outros 20 bancos, que pode ou não levar menos de 2 dias. Você pode começar a interagir com esses bancos para determinar quais bancos levam menos de 2 dias. Agora, você pode não encontrar um banco que demore menos de 2 dias e há tempo adicional perdido devido à própria atividade de pesquisa. Teria sido melhor abrir uma conta no próprio primeiro banco.

Conclusão: É mais importante selecionar com sabedoria. Para ser mais preciso, escolha qual a opção é a melhor, não a mais barata.

Da mesma forma, MS SQL Optimizer funciona em algoritmos exaustivos/heurísticos integrados. O objetivo é minimizar o tempo de execução da consulta. Todos os algoritmos do Optimizer são propriedade de Microsoft e um segredo. Apesar, abaixo estão as etapas de alto nível executadas pelo MS SQL Optimizer. As pesquisas de otimização seguem três fases conforme mostrado no diagrama abaixo:

Fase 0: Busca pelo Plano Trivial:

• Isso também é conhecido como Estágio de pré-otimização.
• Para alguns casos, poderia haver apenas um plano prático e viável, conhecido como plano trivial. Não há necessidade de criar um plano otimizado. A razão é que pesquisar mais resultaria na localização do mesmo plano de execução em tempo de execução. Isso também com o custo extra de busca por um plano otimizado que não era necessário.
• Se nenhum plano trivial for encontrado, então 1^st A fase começa.

Fase 1: Pesquisa de planos de processamento de transações

• Isto inclui a busca por Plano Simples e Complexo.
• Pesquisa Simple Plan: Os dados anteriores da coluna e do índice envolvidos na consulta serão utilizados para análise estatística. Isso geralmente consiste, mas não está restrito a um índice por tabela.
• Ainda assim, se o plano simples não for encontrado, procura-se um plano mais complexo. Envolve vários índices por tabela.

Fase 2: Processamento Paralelo e Otimização.

• Se nenhuma das estratégias acima funcionar, o Optimizer procura possibilidades de processamento paralelo. Isto depende das capacidades de processamento e da configuração da Máquina.
• Se isso ainda não for possível, inicia-se a fase final de otimização. Agora, o objetivo final da otimização é encontrar todas as outras opções possíveis para executar a consulta da melhor maneira. Fase final de otimização Algorithms e guarante que os mesmos estão Microsoft Propriedade.

Executor de Consultas

Chamadas do executor de consulta Método de acesso. Ele fornece um plano de execução para a lógica de busca de dados necessária para a execução. Depois que os dados são recebidos do Storage Engine, o resultado é publicado na camada de protocolo. Finalmente, os dados são enviados ao usuário final.

Motor de Armazenamento

O trabalho do mecanismo de armazenamento é armazenar dados em um sistema de armazenamento como disco ou SAN e recuperar os dados quando necessário. Antes de nos aprofundarmos no mecanismo de armazenamento, vamos dar uma olhada em como os dados são armazenados em banco de dados e tipo de arquivos disponíveis.

Arquivo de dados e extensão:

Arquivo de Dados, armazena fisicamente dados na forma de páginas de dados, com cada página de dados tendo um tamanho de 8 KB, formando a menor unidade de armazenamento no SQL Server. Essas páginas de dados são agrupadas logicamente para formar extensões. Nenhum objeto recebe uma página no SQL Server.

A manutenção do objeto é feita via extensões. A página possui uma seção chamada Cabeçalho da Página com tamanho de 96 bytes, contendo as informações de metadados sobre a página, como Tipo de Página, Número da Página, Tamanho do Espaço Utilizado, Tamanho do Espaço Livre e Ponteiro para a próxima página e a página anterior , etc.

Os tipos de arquivo

1. Arquivo primário

• Cada banco de dados contém um arquivo primário.
• Isso armazena todos os dados importantes relacionados a tabelas, visualizações, gatilhos, etc.
• Extensão é .mdf geralmente, mas pode ter qualquer extensão.

2. Arquivo secundário

• O banco de dados pode ou não conter vários arquivos secundários.
• Isso é opcional e contém dados específicos do usuário.
• Extensão é .naf geralmente, mas pode ter qualquer extensão.

3. Arquivo de log

• Também conhecido como logs Write ahead.
• Extensão é .ldf
• Usado para gerenciamento de transações.
• Isso é usado para recuperar quaisquer instâncias indesejadas. Execute uma tarefa importante de reversão para transações não confirmadas.

O Storage Engine possui 3 componentes; vamos examiná-los em detalhes.

Método de Acesso

Ele atua como uma interface entre o executor da consulta e Buffer Logs de gerente/transação.

O método de acesso em si não faz nenhuma execução.

A primeira ação é determinar se a consulta é:

1. Selecione a instrução (DDL)
2. Instrução Não Selecionada (DDL e DML)

Dependendo do resultado, o Método de Acesso segue os seguintes passos:

1. Se a consulta for DDL, instrução SELECT, a consulta é passada para o Buffer Manager para processamento adicional.
2. E se perguntar se DDL, instrução NON-SELECT, a consulta será passada para o Transaction Manager. Isso inclui principalmente a instrução UPDATE.

Buffer Manager

Buffer manager gerencia as funções principais dos módulos abaixo:

• Cache do plano
• Análise de dados: Buffer cache e armazenamento de dados
• Página suja

Vamos aprender Plano, Buffer e Cache de dados nesta seção. Abordaremos as páginas sujas na seção Transação.

Cache do plano

• Plano de consulta existente: O gerenciador de buffer verifica se o plano de execução está no Cache de Plano armazenado. Se sim, então o cache do plano de consulta e seu cache de dados associado serão usados.
• Plano de cache pela primeira vez: De onde vem o cache do Plano existente? Se o plano de execução da consulta inicial estiver sendo executado e for complexo, faz sentido armazená-lo no cache do Plano. Isso garantirá uma disponibilidade mais rápida na próxima vez que o SQL Server receber a mesma consulta. Portanto, nada mais é do que a própria consulta cuja execução do Plano está sendo armazenada caso esteja sendo executada pela primeira vez.

Análise de dados: Buffer cache e armazenamento de dados

Buffer gerente fornece acesso aos dados necessários. Abaixo, duas abordagens são possíveis, dependendo da existência ou não de dados no cache de dados:

Buffer Cache – análise suave:

Buffer Gerente procura dados em Buffer em Cache de dados. Se presentes, esses dados serão usados ​​pelo Query Executor. Isso melhora o desempenho, pois o número de operações de E/S é reduzido ao buscar dados do cache em comparação com a busca de dados do armazenamento de dados.

Armazenamento de dados – análise difícil:

Se os dados não estiverem presentes em Buffer Gerente do que o necessário Os dados são pesquisados ​​no armazenamento de dados. Também armazena dados no cache de dados para uso futuro.

Página suja

Ele é armazenado como uma lógica de processamento do Transaction Manager. Aprenderemos em detalhes na seção Transaction Manager.

Gerente de Transação

O Transaction Manager é invocado quando o método de acesso determina que Query é uma instrução Non-Select.

Gerenciador de registros

• O Log Manager mantém um controle de todas as atualizações feitas no sistema por meio de logs em logs de transações.
• Os registros têm Registra o número de sequência com o ID da transação e o registro de modificação de dados.
• Isso é usado para acompanhar Transação confirmada e reversão de transação.

Gerenciador de bloqueio

• Durante a transação, os dados associados no armazenamento de dados estão no estado bloqueado. Este processo é tratado pelo Lock Manager.
• Este processo garante consistência e isolamento de dados. Também conhecidas como propriedades ACID.

Processo de Execução

• O Log Manager inicia o registro e o Lock Manager bloqueia os dados associados.
• A cópia dos dados é mantida no Buffer cache.
• A cópia dos dados que deveriam ser atualizados é mantida no buffer de log e todos os eventos atualizam os dados no buffer de dados.
• As páginas que armazenam os dados também são conhecidas como Páginas sujas.
• Registro de ponto de verificação e write-ahead: Este processo é executado e marca todas as páginas de Dirty Pages para Disk, mas a página permanece no cache. A frequência é de aproximadamente 1 corrida por minuto. Mas a página é primeiro enviada para a página Dados do arquivo de log de Buffer registro. Isso é conhecido como Registro de gravação antecipada.
• Escritor preguiçoso: A página suja pode permanecer na memória. Quando o SQL Server observa uma carga enorme e Buffer memória é necessária para uma nova transação, ela libera Dirty Pages do cache. Ele opera em LRU – Algoritmo usado menos recentemente para limpar a página do buffer pool para o disco.

Resumo

• Três tipos de servidor cliente Archiexiste uma arquitetura: 1) Memória Compartilhada 2) TCP/IP 3) Pipes Nomeados
• TDS, desenvolvido pela Sybase e agora propriedade da Microsoft, é um pacote encapsulado em pacotes de rede para transferência de dados da máquina cliente para a máquina servidor.
• O Mecanismo Relacional contém três componentes principais:Analisador CMD: Isso é responsável pelo erro sintático e semântico e, finalmente, gera uma árvore de consulta.Otimizador: A função do otimizador é encontrar o plano de execução mais barato, e não o melhor, com boa relação custo-benefício.

  Executor de consulta: O executor de consulta chama o método de acesso e fornece um plano de execução para a lógica de busca de dados necessária para a execução.

• Existem três tipos de arquivos: arquivo primário, arquivo secundário e arquivos de log.
• Mecanismo de armazenamento: possui os seguintes componentes importantesMétodo de acesso: Este componente determina se a consulta é uma instrução selecionada ou não selecionada. Invoca Buffer e Gerenciador de Transferências em conformidade.Buffer Manager: Buffer O gerenciador gerencia funções básicas para Plan Cache, Data Parsing e Dirty Page.

  Gerenciador de transações: Gerencie transações não selecionadas com a ajuda dos gerenciadores de log e bloqueio. Além disso, facilita a implementação importante do registro Write Ahead e dos gravadores preguiçosos.