O 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 os dados processados. Vamos discutir em detalhes toda a arquitetura mostrada abaixo:
Como mostra o diagrama a seguir, existem três componentes principais na arquitetura do SQL Server:
- Camada de protocolo
- Relational Engine
- Motor de Armazenamento
Vamos discutir em detalhes sobre todos os três módulos principais acima. Neste tutorial, você aprenderá.
- Camada de protocolo - SNI
- Memoria compartilhada
- TCP / IP
- Pipes nomeados
- O que é TDS?
- Relational Engine
- Analisador CMD
- Otimizador
- Executor de consulta
- Motor de Armazenamento
- Tipos de arquivo
- Método de Acesso
- Gerenciador de Buffer
- Cache de plano
- Análise de dados: cache de buffer e armazenamento de dados
- Gerente de Transação
Camada de protocolo - SNI
MS SQL SERVER PROTOCOL LAYER oferece suporte a 3 tipos de arquitetura de servidor cliente. Começaremos com " Três tipos de arquitetura de servidor cliente" 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, em sua casa. Tom pôde pedir café e mamãe pôde servi-lo quente.
MS SQL SERVER - Aqui o MS SQL Server fornece PROTOCOLO DE MEMÓRIA COMPARTILHADA . Aqui CLIENT e MS SQL server rodam na mesma máquina. Ambos podem se comunicar por meio do protocolo de memória compartilhada.
Analogia: permite 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 ao banco de dados local - no SQL Management Studio, a opção "Nome do servidor" pode ser
"."
"localhost"
"127.0.0.1"
"Máquina \ Instância"
TCP / IP
Agora, considere que à noite, Tom está no clima de festa. Ele quer um café encomendado em uma cafeteria conhecida. O Café fica a 10 km de sua casa.
Aqui, Tom e Starbuck estão em localizações físicas diferentes. Tom em casa e Starbucks no movimentado mercado. Eles estão se comunicando via rede celular. Da mesma forma, o MS SQL SERVER fornece a capacidade de interagir por meio do protocolo TCP / IP, onde CLIENT e MS SQL Server são remotos entre si e instalados em uma máquina separada.
Analogia: permite mapear entidades nos dois cenários acima. Podemos mapear facilmente 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.
Pipes nomeados
Agora, finalmente, à noite, Tom queria um chá verde leve que sua vizinha, Sierra, preparava muito bem.
Aqui Tom e seu vizinho , Sierra, estão no mesmo local físico, sendo vizinhos um do outro. Eles estão se comunicando através da rede Intra. Da mesma forma, o MS SQL SERVER fornece a capacidade de interagir por meio do protocolo Named Pipe . Aqui, o CLIENTE e o MS SQL SERVER estão em conexão via LAN .
Analogia: permite 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 pipe 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 arquitetura cliente-servidor, vamos dar uma olhada no TDS:
- TDS significa Tabular Data Stream.
- 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 servidor.
- TDS foi desenvolvido pela primeira vez pela Sybase e agora é propriedade da Microsoft
Relational Engine
O Relational Engine também é conhecido como Query Processor. Ele tem os componentes do SQL Server que determinam exatamente o que uma consulta precisa fazer e como isso pode ser feito da melhor maneira. Ele é responsável pela execução de consultas do usuário, solicitando dados do mecanismo de armazenamento e processando os resultados que são retornados.
Conforme representado no Diagrama de Arquitetura, existem 3 componentes principais do Mecanismo 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 mecanismo relacional. "CMD Parser" é o primeiro componente do Relational Engine a receber os dados da Consulta. A principal tarefa do CMD Parser é verificar se há erros sintáticos e semânticos na consulta . Finalmente, ele gera uma Árvore de Consulta . Vamos discutir em detalhes.
Verificação sintática:
- Como todas as outras linguagens de programação, o MS SQL também possui o conjunto predefinido de palavras-chave. Além disso, o SQL Server tem 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 a verificação sintática. Se a entrada dos usuários não seguir a sintaxe do idioma ou as regras gramaticais, ele retornará 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 de gramática ou idioma aceito pelo servidor SQL. Se houver, o servidor SQL não pode processá-lo e, portanto, retornará uma mensagem de erro.
Aprenderemos mais sobre a consulta do MS SQL nos próximos tutoriais. No entanto, considere a seguir a sintaxe de consulta mais básica como
SELECT * from;
Agora, para obter a percepção do que a sintaxe faz, diga se o usuário executa a consulta básica conforme abaixo:
SELECR * from
Observe que, em vez de 'SELECIONAR', o usuário digitou "SELECR".
Resultado: o analisador CMD analisará essa instrução e emitirá a mensagem de erro. Como "SELECR" não segue o nome e a gramática predefinidos da palavra-chave. Aqui, o CMD Parser esperava "SELECT".
Verificação semântica:
- Isso é executado pelo Normalizer .
- Em sua forma mais simples, ele verifica se o nome da coluna e o nome da tabela que está sendo consultada existem no esquema. E se existir, vincule-o a Query. Isso também é conhecido como Ligação .
- A complexidade aumenta quando as consultas do usuário contêm VIEW. Normalizer realiza a substituição com a definição de visão armazenada internamente e muito mais.
Vamos entender isso com a ajuda do exemplo abaixo -
SELECT * from USER_ID
Resultado: o analisador CMD analisará essa instrução para verificação semântica. O analisador lançará uma mensagem de erro, pois o Normalizador não encontrará a tabela solicitada (USER_ID) porque ela não existe.
Criar árvore de consulta:
- Esta etapa gera uma árvore de execução diferente na qual a consulta pode ser executada.
- Observe que todas as árvores diferentes têm a mesma saída desejada.
Otimizador
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 marcadas primeiro e depois enviadas ao otimizador. Comandos DDL como CREATE e ALTER não são otimizados, mas sim 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 Optimizer é encontrar o plano de execução mais barato, não o melhor e com melhor custo-benefício.
Antes de entrarmos em mais detalhes técnicos do Otimizador, considere o exemplo da vida real abaixo:
Exemplo:
Digamos que você deseja 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 20 outros bancos, o que pode ou não levar menos de 2 dias. Você pode começar a se envolver com esses bancos para determinar quais bancos levam menos de 2 dias. Agora, você não pode encontrar um banco que leva menos de 2 dias, e há mais tempo 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 opção é melhor, não a mais barata.
Da mesma forma, o 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 da Microsoft e um segredo. Embora , 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: Pesquisa por Plano Trivial:
- Isso também é conhecido como estágio de pré-otimização .
- Para alguns casos, pode haver apenas um plano prático e viável, conhecido como plano trivial. Não há necessidade de criar um plano otimizado. O motivo é que pesquisar mais resultaria em encontrar o mesmo plano de execução de tempo de execução. Isso também com o custo extra de Busca por um plano otimizado, que não era necessário de forma alguma.
- Se nenhum plano Trivial encontrado, em seguida, 1 st fase começa.
Fase 1: Pesquisa de planos de processamento de transações
- Isso inclui a busca por planos simples e complexos .
- Busca de Plano Simples: Dados passados de coluna e índice envolvidos na Consulta, serão usados para Análise Estatística. Isso geralmente consiste, mas não se restringe a um índice por tabela.
- Ainda assim, se o plano simples não for encontrado, então um plano mais complexo é pesquisado. Envolve índice múltiplo por tabela.
Fase 2: Processamento paralelo e otimização.
- Se nenhuma das estratégias acima funcionar, o Optimizer procura possibilidades de processamento paralelo. Isso depende dos recursos de processamento e da configuração da Máquina.
- Se isso ainda não for possível, a fase final de otimização começa. Agora, o objetivo final da otimização é encontrar todas as outras opções possíveis para executar a consulta da melhor maneira. Os algoritmos da fase final de otimização são propriedade da Microsoft.
Executor de consulta
O executor da consulta chama o 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 mecanismo de armazenamento, 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 no banco de dados e os tipos de arquivos disponíveis.
Arquivo de dados e extensão:
Arquivo de Dados, armazena fisicamente os 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 do SQL Server. Essas páginas de dados são agrupadas logicamente para formar extensões. Nenhum objeto é atribuído a uma página no SQL Server.
A manutenção do objeto é feita por meio de extensões. A página tem uma seção chamada Cabeçalho da Página com um tamanho de 96 bytes, carregando as informações de metadados sobre a página, como Tipo de Página, Número da Página, Tamanho do Espaço Usado, Tamanho do Espaço Livre e Ponteiro para a próxima página e página anterior etc.
Tipos de arquivo
- 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.
- A extensão é. mdf normalmente, mas pode ter qualquer extensão.
- 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.
- A extensão é. ndf normalmente, mas pode ser de qualquer extensão.
- Arquivo de log
- Também conhecido como registros de gravação antecipada.
- A extensão é. ldf
- Usado para gerenciamento de transações.
- Isso é usado para se recuperar de quaisquer instâncias indesejadas. Execute a tarefa importante de Rollback para transações não confirmadas.
O mecanismo de armazenamento tem 3 componentes; vamos examiná-los em detalhes.
Método de Acesso
Ele atua como uma interface entre o executor de consulta e o Gerenciador de Buffer / Logs de Transação.
O Método de Acesso em si não faz nenhuma execução.
A primeira ação é determinar se a consulta é:
- Selecionar declaração (DDL)
- Declaração de não seleção (DDL e DML)
Dependendo do resultado, o Método de Acesso executa as seguintes etapas:
- Se a consulta for DDL , instrução SELECT, a consulta será passada ao Gerenciador de Buffer para processamento posterior.
- E se a consulta for DDL, instrução NON-SELECT , a consulta é passada para o Gerenciador de Transações. Isso inclui principalmente a instrução UPDATE.
Gerenciador de Buffer
O gerenciador de buffer gerencia as funções principais para os módulos abaixo:
- Cache de plano
- Análise de dados: cache de buffer e armazenamento de dados
- Página Suja
Aprenderemos Plano, Buffer e Cache de dados nesta seção. Cobriremos páginas sujas na seção Transação.
Cache de plano
- Plano de consulta existente: O gerenciador de buffer verifica se o plano de execução está no cache de plano armazenado. Se Sim, o cache do plano de consulta e seu cache de dados associado são usados.
- Plano de cache pela primeira vez: de onde vem o cache de planos existente?
Se o plano de execução da consulta inicial estiver sendo executado e for complexo, faz sentido armazená-lo no cache do Plane. Isso garantirá uma disponibilidade mais rápida na próxima vez que o servidor SQL obtiver a mesma consulta. Portanto, nada mais é que a própria consulta que a execução do plano está sendo armazenada se for executada pela primeira vez.
Análise de dados: cache de buffer e armazenamento de dados
O gerenciador de buffer fornece acesso aos dados necessários. Abaixo, duas abordagens são possíveis, dependendo da existência de dados no cache de dados ou não:
Cache de buffer - análise de software:
O Gerenciador de buffer procura dados no buffer no cache de dados. Se estiverem 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 pesada:
Se os dados não estiverem presentes no Buffer Manager, os dados necessários serã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 Gerenciador de transações.
Gerente de Transação
O Transaction Manager é chamado quando o método de acesso determina que Query é uma instrução Non-Select.
Log Manager
- O Log Manager mantém um controle de todas as atualizações feitas no sistema por meio de registros em Registros de transações.
- Os logs têm número de sequência de logs com o ID da transação e o registro de modificação de dados .
- Isso é usado para controlar a transação confirmada e a reversão da transação .
Gerente de Bloqueio
- Durante a transação, os dados associados no armazenamento de dados estão no estado de bloqueio. Este processo é gerenciado pelo Lock Manager.
- Esse processo garante a consistência e o isolamento dos dados . Também conhecido 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 cache do Buffer.
- A cópia dos dados supostamente 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 gravação antecipada: este processo é executado e marca todas as páginas de páginas sujas para o disco, mas a página permanece no cache. A frequência é de aproximadamente 1 execução por minuto. Mas a página é primeiro enviada para a página de dados do arquivo de log do log do buffer. Isso é conhecido como registro de gravação antecipada.
- Gravador preguiçoso: a página Suja pode permanecer na memória. Quando o servidor SQL observa uma carga enorme e a memória Buffer é necessária para uma nova transação, ele libera páginas sujas do cache. Ele opera em LRU - Algoritmo menos usado recentemente para limpar a página do buffer pool para o disco.
Resumo:
- Existem três tipos de arquitetura cliente-servidor: 1) Memória compartilhada 2) TCP / IP 3) Pipes nomeados
- TDS, desenvolvido pela Sybase e agora propriedade da Microsoft, é um pacote que é 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: Este é 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, não o melhor e econômico.
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: tem os seguintes componentes importantes
Método de acesso: este componente Determine se a consulta é uma instrução selecionada ou não selecionada. Invoca o Buffer e o Transfer Manager de acordo.
Gerenciador de buffer: o gerenciador de buffer gerencia as funções principais do cache de plano, análise de dados e página suja.
Gerenciador de transações: gerenciador de transações não selecionadas com a ajuda de gerenciadores de log e bloqueio. Além disso, facilita a implementação importante de logs Write Ahead e gravadores Lazy.