Prévia do material em texto
Tuning de banco de dados no SQL
Server
Você vai compreender o desempenho do sistema de gerenciamento de banco do SQL Server. Aqui
exploraremos estratégias, técnicas e melhores práticas de tuning para otimizar o desempenho do SGBD.
Aprenderemos a identificar gargalos de desempenho, ajustar configurações e consultas, bem como
otimizar a estrutura do banco de dados
Prof. Sidney Nicolau Venturi Filho
1. Itens iniciais
Preparação
Antes de começar seu estudo, tenha o servidor SQL Server instalado em um PC ou máquina virtual e o SQL
Server Management Studio e o Azure Data Studio disponíveis para uso. É necessário o acesso à internet para
evolução da edição do servidor.
Objetivos
Identificar os fundamentos do tuning do SGBD SQL Server.
Reconhecer a otimização do desempenho do SGBD SQL Server.
Identificar os fundamentos do tuning de SQL no SQL Server.
Reconhecer a otimização de consultas SQL no SQL Server.
Introdução
O tuning de um banco de dados SQL Server é o processo de otimização de seu desempenho, envolvendo
análise, identificação e ajuste de áreas problemáticas, visando melhorar a resposta do sistema, reduzir o
tempo de execução das consultas e maximizar a utilização dos recursos do servidor.
Abrangendo áreas como consultas, armazenamento, memória, sistema operacional e configuração, o tuning
requer uma abordagem sistemática, que envolve coleta de informações, análise de desempenho, identificação
de gargalos, implementação de ajustes e monitoramento contínuo.
O tuning de um servidor é um processo contínuo, pois as necessidades e requisitos do sistema podem mudar
ao longo do tempo.
•
•
•
•
1. Fundamentos de tuning do SGBD servidor SQL Server
Visão geral do processo de tuning
Assista ao vídeo e acompanhe um panorama sobre o tuning do SQL Server como processo de otimização de
desempenho de servidores.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Visão geral do tuning do SQL Server
O tuning do SQL Server é um processo de otimização de desempenho de um servidor SQL Server para
garantir que consultas e operações sejam executadas de maneira eficiente e rápida. Isso envolve a
identificação e resolução de gargalos de desempenho, ajuste de consultas, configuração adequada do banco
de dados e do sistema, além de outras otimizações para maximizar a eficiência e a capacidade de resposta.
A otimização de desempenho no SQL Server é uma atividade contínua, pois o desempenho de um banco de
dados pode ser afetado por uma variedade de fatores, incluindo a quantidade de dados armazenados, a
complexidade das consultas, a estrutura do banco de dados, a carga de trabalho do sistema e a configuração
do hardware. Aqui estão alguns aspectos-chave do tuning no SQL Server:
1
Monitoramento e identificação de gargalos
A primeira etapa do tuning é monitorar o desempenho do banco de dados para identificar gargalos e
áreas problemáticas. Isso pode ser feito usando ferramentas de monitoramento, logs de eventos e
análise de consultas.
2
Otimização de consultas
As consultas SQL são frequentemente o principal ponto de foco no tuning. Isso envolve analisar o
plano de execução da consulta, identificar áreas de ineficiência e reescrever a consulta ou adicionar
índices apropriados para melhorar o desempenho.
3
Índices e estruturas de dados
Índices bem projetados podem acelerar a recuperação de dados, mas índices em excesso ou mal
projetados podem diminuir o desempenho. O tuning envolve a seleção cuidadosa de índices a serem
criados, ajustando o design da tabela e reorganizando ou reconstruindo índices, conforme
necessário.
4Configurações do servidor e banco de dados
Ajustar essas configurações de acordo com as necessidades da aplicação e do hardware pode
melhorar significativamente o desempenho. Isso inclui configurações de memória, parâmetros de
inicialização, alocação de recursos e opções de armazenamento.
5
Particionamento de tabelas
Particionar tabelas grandes pode melhorar o desempenho de consultas que acessam apenas um
subconjunto dos dados, permitindo que o SQL Server acesse somente as partições relevantes.
6
Compactação de dados
Pode economizar espaço em disco e melhorar o desempenho de consultas que envolvem tabelas
com muitos dados históricos.
7
Monitoramento contínuo
O desempenho de um banco de dados pode mudar ao longo do tempo, devido a mudanças na carga
de trabalho ou na estrutura do banco de dados. Portanto, o tuning é uma atividade contínua que
requer monitoramento e ajustes regulares.
Ferramentas para tuning do SQL Server
Existem várias ferramentas disponíveis para ajudar no tuning e na otimização de desempenho do SQL Server.
Confira quais destacamos para você.
1
SQL Server Management Studio (SSMS)
É a ferramenta de gerenciamento padrão fornecida pela Microsoft para administrar, desenvolver e
ajustar o SQL Server. O SSMS oferece recursos como análise de consultas, captura de plano de
execução, criação de índices, ajuste de consultas e configuração de servidores.
2
SQL Server Profiler
Permite capturar e analisar eventos em tempo real que ocorrem no SQL Server, o que é
especialmente útil para entender o comportamento das consultas e identificar problemas de
desempenho.
3
SQL Server Query Store
Usada para monitorar e ajustar o desempenho das consultas. Ela permite que você rastreie o
histórico de planos de execução, force planos específicos, reverta para planos anteriores e analise
as estatísticas de consulta.
4 Database Engine Tuning Advisor (DTA)
Ajuda a analisar e otimizar consultas, sugerindo índices, estatísticas e outras recomendações para
melhorar o desempenho.
5
Performance Monitor
Permite monitorar métricas de desempenho do sistema e do SQL Server em tempo real. É do
Windows e é útil para identificar gargalos de desempenho e anomalias no nível do sistema.
6
Extended Events
Estrutura de rastreamento mais avançada e flexível do que o Profiler, permite coletar informações
detalhadas sobre eventos e métricas de desempenho do SQL Server, com menor impacto no
desempenho em comparação ao Profiler.
7
Dynamic Management Views (DMVs) e dm_exec_query_stats
As DMVs são visualizações dinâmicas que fornecem informações detalhadas sobre o estado e o
desempenho do SQL Server. A visão “dm_exec_query_stats” é particularmente útil para analisar o
histórico de consultas e identificar as mais caras.
Atividade 1
O SQL serve pode trazer diversas soluções para sistemas de gerenciamento de banco de dados. Qual é a
finalidade do tuning do SQL Server?
A Desenvolver consultas SQL complexas.
B Garantir a segurança dos dados no servidor.
C Criar índices em excesso para acelerar consultas.
D Otimizar o desempenho do servidor para consultas eficientes e rápidas.
E Realizar compactação de dados em tabelas grandes.
A alternativa C está correta.
O tuning do SQL Server refere-se ao processo de otimização de desempenho de um servidor SQL Server
para garantir que consultas e operações sejam executadas de maneira eficiente e rápida. Isso envolve
identificar e resolver gargalos de desempenho, ajustar consultas, configurar adequadamente o banco de
dados e o sistema, e realizar outras otimizações para maximizar a eficiência e a capacidade de resposta.
Monitorando o servidor com o SSMS
Assista ao vídeo e entenda a importância de monitorar o servidor SQL Server com o SSMS, na garantia de
melhor desempenho e mais segurança em sistemas de banco de dados.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Importância do monitoramento
O monitoramento do servidor SQL Server é fundamental para garantir o desempenho, a disponibilidade e a
confiabilidade de um ambiente de banco de dados. Ele envolve o acompanhamento contínuo de atividades,
recursos e métricas do servidor, permitindo aos administradores identificarem problemas, tomar medidas
preventivas e corretivas, otimizar consultas e garantir que o ambiente funcione de maneira eficiente.
Existem muitas razões para monitorar e você vai entender algumas delas aqui.
Desempenho otimizadoO valor é usado apenas durante a otimização da consulta, e não durante a
execução da consulta.
3OPTIMIZE FOR UNKNOWN
Instrui o otimizador de consulta a usar dados estatísticos em vez dos valores iniciais para todas as
variáveis locais, quando a consulta é compilada e otimizada, incluindo parâmetros criados com
parametrização forçada.
4
USE PLAN
Utiliza um plano armazenado como um documento XML como parâmetro e orienta o mecanismo de
banco de dados a usar o plano de execução especificado para a consulta.
Guias de plano
Às vezes, você não pode ou não deseja alterar diretamente o texto da instrução SELECT. Nesse caso, ainda é
possível influenciar a execução de consultas usando guias de plano. Em outras palavras, guias de plano
permitem que você use uma dica de otimização específica sem alterar a sintaxe da instrução SELECT.
Guias de plano são elaboradas usando o procedimento do sistema sp_create_plan_guide. Esse procedimento
cria uma guia de plano para associar dicas de consulta ou planos de consulta reais a consultas em um banco
de dados. Outro procedimento do sistema, sp_control_plan_guide, habilita, desabilita ou exclui uma guia de
plano existente.
Atividade 4
A hint (dica) é uma diretiva que otimiza a consulta ao rodar uma instrução LMD (Linguagem de Manipulação de
Dados). Qual é o propósito das dicas de otimização (hints) no SQL Server?
A Ajudar o otimizador de consultas a sempre escolher o plano de execução mais rápido.
B Fornecer informações estatísticas detalhadas sobre as tabelas e índices para o otimizador de
consultas.
C Reduzir a complexidade das consultas, tornando-as mais fáceis de entender.
D Forçar o otimizador de consultas a usar planos de execução específicos em situações especiais.
E Garantir que todas as consultas sejam executadas em um único núcleo de CPU para melhor
desempenho.
A alternativa D está correta.
As dicas de otimização (hints) são usadas para forçar o otimizador de consultas a usar planos de execução
específicos em situações especiais em que o otimizador pode não encontrar a solução ideal por conta
própria. Elas podem ser utilizadas para melhorar o desempenho da consulta ao indicar ao otimizador como
ele deve executar a consulta, por exemplo, forçando o uso de um índice específico, definindo a ordem das
operações de junção, otimizando a recuperação rápida das primeiras linhas etc.
Otimização de consultas com índices
Assista ao vídeo e compreenda a importância dos índices utilizados para acelerar a recuperação de dados,
permitindo que o banco de dados localize registros relevantes mais rapidamente no SQL Server.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Uso de índices
Os índices são estruturas de dados que armazenam informações sobre a distribuição dos valores em colunas
de uma tabela. Eles são usados para acelerar a recuperação de dados, permitindo que o banco de dados
localize registros relevantes mais rapidamente. Quando você executa uma consulta, o otimizador de consultas
escolhe o plano de execução mais eficiente, que geralmente envolve o uso de índices.
Veja algumas maneiras pelas quais os índices otimizam consultas.
Acesso rápido aos dados
Os índices permitem que o banco de dados encontre rapidamente os registros que atendem aos
critérios da consulta. Em vez de percorrer toda a tabela, o banco de dados pode usar os índices para
identificar os registros relevantes.
Redução de E/S
Ao usar índices, o banco de dados pode reduzir a quantidade de E/S necessária para recuperar os
dados. Isso ocorre porque o banco de dados pode pular blocos de dados desnecessários, acessando
diretamente os blocos que contêm os registros relevantes.
Filtragem e ordenação
Os índices facilitam a ordenação e a filtragem de dados. Se uma consulta possui uma cláusula ORDER
BY ou WHERE, um índice apropriado pode acelerar essas operações.
Evitar operações caras
Os índices podem evitar operações de junção complexas, especialmente se forem usados índices de
junção.
Evitar leituras completas de tabela
Com índices adequados, o banco de dados pode evitar fazer leituras completas da tabela, o que
economiza tempo e recursos.
Critérios para criação de índices
A seleção adequada de índices é essencial para otimizar consultas. Confira alguns critérios para a criação de
índices.
Colunas usadas em cláusulas WHERE e JOIN
Crie índices em colunas frequentemente usadas em cláusulas WHERE e JOIN, pois essas colunas são
usadas para filtrar e unir dados.
Seletividade
Prefira criar índices em colunas com alta seletividade, ou seja, colunas que têm muitos valores únicos.
Isso reduz o número de registros que precisam ser examinados.
Índices compostos
Considere criar índices compostos, que incluem várias colunas relevantes. Isso pode cobrir múltiplas
colunas usadas em consultas complexas.
Consultas mais frequentes
Analise as consultas mais frequentes em seu aplicativo e crie índices que as atendam. Isso melhora o
desempenho das operações mais comuns.
Key Lookup e Rid Lookup
Quando fazemos uma consulta em uma tabela e existe um índice, temos sempre a possibilidade de ele ser
utilizado para otimizar a execução e diminuir o tempo de respostas.
Quando uma consulta não retorna todas as colunas da tabela – levando-se em conta que o índice clustered é
a própria tabela, o que acarreta o acesso de um grande volume de dados, se ela for usada –, o otimizador
pode preferir utilizar um índice não cluster, se existir.
Como consequência dessa situação, dois fenômenos podem ocorrer:
Para evitar o Key Lookup, basta utilizar a técnica de cobrir o índice (Covering index), que consiste em
adicionar ao índice NonClustered (INCLUDE) as principais colunas, que são utilizadas nas consultas, à tabela.
Isso faz com que o otimizador de consulta consiga obter todas as informações lendo apenas o índice
escolhido, sem precisar ler também o índice clustered.
Entretanto, deve-se ter muita atenção na modelagem dos índices. Não é recomendável adicionar todas as
colunas da tabela no índice não cluster, pois ele ficará tão grande que não será mais efetivo e o otimizador de
consulta poderá até mesmo decidir em não o utilizar e preferir o operador Index Scan, que faz a leitura
sequencial de todo o índice, prejudicando a performance das consultas.
Para evitar o RID Lookup, basta criar o índice clustered na tabela e prestar atenção aos eventos de Key
Lookup que possam vir a surgir.
Atividade 5
O SQL Server dispõe de uma ferramenta chamada Database Engine Tuning Advisor que é muito útil para
analisar os índices existentes em cima de instruções de acesso a uma base de dados para sugerir melhorias
referentes aos mesmos. Qual das seguintes afirmações sobre os índices no SQL Server está correta?
A Os índices são usados apenas para ordenar os dados em uma tabela, não afetando a velocidade de
recuperação de dados.
B Os índices são usados exclusivamente para evitar operações de junção complexas em consultas.
C Os índices permitem que o banco de dados localize registros relevantes mais rapidamente, acelerando
a recuperação de dados.
D Os índices não têm impacto na quantidade de E/S necessária para recuperar os dados de uma tabela.
E Os índices são criados automaticamente pelo otimizador de consultas sempre que uma nova consulta
é executada.
A alternativa C está correta.
Os índices no SQL Server são estruturas de dados que armazenam informações sobre a distribuição dos
valores em colunas de uma tabela. Eles são usados para acelerar a recuperação de dados, permitindo que
Key Lookup (consulta de chave)
Ocorre quando o otimizador de consultas
precisa acessar a tabela base para recuperar
colunas não incluídas no índice usado na
consulta. Isso resulta em operações adicionais
de E/S e pode afetar o desempenho.
RID Lookup (ou Row Identifier Lookup)
Ocorre quando o índice não é
clusterizado e não contém todas as
colunas necessárias. O banco de dados
precisa procurar os identificadores de
linha na tabela base para recuperar os
dados restantes.
o banco dedados localize registros relevantes mais rapidamente. As opções b, d e e estão incorretas. A
opção a também está incorreta, pois além de ordenar os dados, os índices são usados para buscar
registros com base em critérios.
4. Exibição de planos de execução
Gerenciando a autorização
Assista ao vídeo e descubra como gerenciar a autorização para que se exiba um plano de execução.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Roteiro de prática
Vamos ver como exibir os planos de execução das consultas utilizando o Azure Data Studio e, na sequência, o
SSMS:
Faça conexão ao SGBD com um perfil de administrador.
Escreva uma consulta SQL.
Exiba o plano de execução estimado.
Execute o comando incluindo a exibição do plano de execução real e as estatísticas de consultas
dinâmicas.
Atividade prática
Para fixar o entendimento a respeito de exibição de planos de execução, execute as atividades descritas a
seguir:
Acesse o servidor utilizando o SSMS como administrador do servidor.
Conexão ao servidor
Abra uma janela de consulta na SSMS do administrador do sistema e comande:
•
•
•
•
USE BDTUN.
Exibição de plano de execução
Escreva uma consulta SQL que faça a junção da tabela DOCUMENTOS com TIPO_DOCUMENTO.
plain-text
SELECT *
FROM DOCUMENTOS D
INNER JOIN TIPO_DOCUMENTO T ON D.TIPO = T.NR
Para entender melhor, confira a imagem a seguir.
Exibição de plano de execução
Para exibir o plano de execução estimado, clique no botão exibir plano de execução estimado ou tecle Ctrl + L.
Exibição de plano de execução
O plano de execução estimado é exibido em uma janela abaixo da de escrita de comando.
Exibição de plano de execução
O plano é exibido na forma de uma árvore com as operações ocorrendo da direita para a esquerda e de baixo
para cima. No caso, primeiro são realizadas as duas verificações de índice, a seguir o hash join e finalmente o
select.
Passe o mouse sobre o operador de correspondência de hash e serão exibidos todos os detalhes da operação
que será realizada.
Exibição de plano de execução
Clique com o botão direito na mesma operação e selecione propriedades.
Exibição de plano de execução
Será aberta uma janela com todas as propriedades daquela operação.
Exibição de plano de execução
Para exibir o plano de execução real, solicite sua inclusão na execução, clicando no botão correspondente na
barra de menu, e a seguir execute a consulta.
Exibição de plano de execução
Serão exibidas três abas:
1ª. Resultados
Com as linhas retornadas.
Exibição de plano de execução
2ª. Mensagens
Com aquelas retornadas pelo servidor.
Exibição de plano de execução
3ª. Plano de execução
Com os detalhes da execução incluindo número de linhas e tempo de cada etapa.
Exibição de plano de execução
Além do plano real, você pode exibir as estatísticas de consulta dinâmicas. Para isso, selecione essa opção na
barra de menu e clique em executar a consulta.
Exibição de plano de execução
Será exibida uma nova aba na janela de resultados chamada de estatísticas de consulta ao vivo, que mostra
em tempo real as estatísticas da execução.
Exibição de plano de execução
Além disso, na aba resultados são acrescidas as estatísticas de execução.
Exibição de plano de execução
Interpretando planos de execução
Assista ao vídeo e descubra como interpretar os chamados planos de execução.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Roteiro de prática
Vamos ver como interpretar planos de execução e estatísticas dinâmicas utilizando o Azure Data Studio e, na
sequência, o SSMS:
Faça conexão ao SGBD com o perfil de DBA.
Escreva comandos de SQL.
Exiba e interprete o plano de execução estimado.
Execute os comandos exibindo o plano real e as estatística dinâmicas.
Interprete o plano e analise as estatísticas dinâmicas.
Atividade prática
Para fixar o entendimento a respeito de planos de execução, execute as atividades descritas a seguir:
•
•
•
•
•
Acesse o servidor utilizando o SSMS como administrador do servidor.
Conexão ao servidor
Abra uma janela de consulta na SSMS do administrador do sistema e comande:
USE BDTUN.
Exibição de plano de execução
Escreva uma consulta SQL que faça a junção das tabelas TIPO_DOCUMENTO, DOCUMENTOS, DESPACHO e
TIPO DESPACHO que retorne o nome do tipo do documento, o nome e número, o despacho e o nome do tipo
de despacho.
plain-text
SELECT TD.NOME, D.NUMERO, D.NOME, DP.NOME, TDP.NOME
FROM TIPO_DOCUMENTO TD
INNER JOIN DOCUMENTOS D ON D.TIPO = TD.NR
INNER JOIN DESPACHO DP ON DP.DOCUMENTO_ID = D.NUMERO
INNER JOIN TIPO_DESPACHO TDP ON TDP.NR = DP.TIPO
Para entender melhor, confira a imagem a seguir.
Exibição de plano de execução
Exiba o plano de execução estimado, clique no botão exibir plano de execução estimado ou tecle Ctrl + L.
Exibição de plano de execução
Vamos interpretar o plano.
Exibição de plano de execução
Note que as operações ocorrem na seguinte ordem:
1º) Varredura pelo índice cluster nas tabelas tipo_documento
(custo 0%) e documento (custo 1%) e realização de junção,
utilizando hash (custo 1%), sendo o custo total de 2%.
º) Varredura pelo índice cluster nas tabelas despacho (custo
28%) e na tabela resultante da junção anterior (custo 1%) e
realização da junção utilizando hash (custo 43%), sendo o
custo total acumulado até aqui de 73%.
3º) Varredura pelo índice cluster em tipo_despacho (custo 0%),
realização da junção hash com o resultado da junção anterior
(custo 27%) e retorno do conjunto resposta ao usuário (custo
0%), gerando o custo total acumulado de 100%.
Note que a junção de documentos com despacho possui o maior custo individual, 43%. Para entender melhor,
veja os detalhes da operação.
Exibição de plano de execução
Outro detalhe importante: Quando é informado o custo estimado de 0%, não significado que não existe custo,
mas que é muito pequeno se comparado com o total do comando.
Veja o exemplo da verificação de índice em tipo_despacho, que possui um custo absoluto muito pequeno por
isso 0%.
Exibição de plano de execução
Para exibir o plano de execução real, solicite sua inclusão na execução, clicando no botão correspondente na
barra de menu e a seguir execute a consulta.
Exibição de plano de execução
Note que as operações ocorrem na mesma ordem do estimado, mas agora aparece o total de linhas
recuperadas em cada operação e o tempo decorrido:
Exibição de plano de execução
1) Varredura pelo índice cluster nas tabelas tipo_documento (custo 0%) e documento (custo 1%) e realização
da junção utilizando hash (custo 1%), sendo o custo total de 2%.
Exibição de plano de execução
2) Varredura pelo índice cluster nas tabelas despacho (custo 28%) e na tabela resultante da junção anterior
(custo 1%) e realização da junção utilizando hash (custo 43%), sendo o custo total acumulado até aqui de 73%.
Exibição de plano de execução
3) Varredura pelo índice cluster em tipo_despacho (custo 0%), realização da junção hash com o resultado da
junção anterior (custo 27%) e retorno do conjunto resposta ao usuário (custo 0%), gerando o custo total
acumulado de 100%.
Exibição de plano de execução
Perceba um aspecto interessante nesse plano: a operação de maior custo (43%) 0,887s foi de execução mais
rápida que a de 27% de custo 1,215, o que mostra que o custo não é calculado apenas pelo tempo de
execução.
Exibição de plano de execução
Além do plano real, você pode exibir as estatísticas de consulta dinâmicas. Para isso, selecione essa opção na
barra de menu e clique em executar a consulta.
Exibição de plano de execução
Como essa consulta demora um pouco, você poderá ver o andamento das estatísticas.
Exibição de plano de execução
Veja que no fim é exibido como ficou o tempo de execução de cada etapa da consulta.
Exibição de plano de execução
Comparando planos de execução
Assista ao vídeo e entenda as semelhanças e diferenças dos planos de execução.
Conteúdo interativo
Acesse a versão digital para assistirao vídeo.
Roteiro de prática
Vamos agora ver como salvar e comparar planos de execução.
Faça conexão ao SGBD com um perfil de DBA.
Escreva uma consulta e a execute, gerando o plano de execução real e as estatísticas.
Salve o plano de execução.
Escreva uma consulta diferente que produza o mesmo resultado da anterior.
Execute a nova consulta exibindo o plano real e as estatísticas.
Compare os planos de execução.
Atividade prática
Para fixar o entendimento a respeito de comparação de planos de execução, execute as atividades descritas a
seguir:
Acesse o servidor utilizando o SSMS como administrador do servidor.
•
•
•
•
•
•
Conexão ao servidor
Abra uma janela de consulta na SSMS do administrador do sistema e comande:
USE BDTUN.
Comparação de planos de execução
Escreva uma consulta SQL que faça a junção das tabelas TIPO_DOCUMENTO com DOCUMENTOS e que
retorne todos os dados dos documentos do tipo OFICIO ou REQUERIMENTO.
plain-text
SELECT D.*
FROM DOCUMENTOS D
INNER JOIN TIPO_DOCUMENTO TD ON D.TIPO = TD.NR
WHERE TD.NOME IN ('OFICIO', 'REQUERIMENTO')
Para entender melhor, confira a imagem a seguir.
Comparação de planos de execução
Execute a consulta exibindo o plano de execução real.
Comparação de planos de execução
Temos, então, o resultado:
Comparação de planos de execução
E o seguinte plano foi executado.
Comparação de planos de execução
Interpretando o plano, podemos compreender que as duas tabelas foram percorridas utilizando o índice
cluster e a seguir realizada a junção com hash, sendo esta a operação mais custosa (55%) e de maior tempo
de execução 0,010s.
Salve o plano de execução com o nome de PLANJOIN1 para isso:
Clique com o botão direito na janela do plano e selecione salvar plano de execução como.
Comparação de planos de execução
Na janela seguinte, defina nome do plano o local em que deseja gravar o plano e clique em salvar.
Comparação de planos de execução
Vamos mudar a consulta para utilizar uma subconsulta aninhada, produzindo o mesmo resultado.
plain-text
SELECT *
FROM DOCUMENTOS
WHERE TIPO IN (SELECT NR FROM
TIPO_DOCUMENTO
WHERE NOME IN ('OFICIO', 'REQUERIMENTO'))
Para entender melhor, confira a imagem a seguir.
Comparação de planos de execução
Execute a consulta exibindo o plano de execução real.
Comparação de planos de execução
Temos, então, o resultado:
Comparação de planos de execução
E o seguinte plano foi executado.
Comparação de planos de execução
Interpretando o plano, percebemos que as duas tabelas foram percorridas utilizando o índice cluster e a seguir
realizada a junção com hash, sendo esta a operação mais custosa (55%) e de maior tempo de execução
0,010s. Além disso, note que o plano é de junção e totalmente similar ao que foi executado para o primeiro
comando.
Comparação de planos de execução
Isso nos mostra que o otimizador adota estratégias de execução mais eficientes, independentemente de ter
sido ou não comando de junção.
Mas será que a forma mais eficiente de comparar planos é essa? Olhar o primeiro e depois o segundo?
Vamos comparar os planos utilizando os recursos do SSMS para isso:
Clique com o botão direito na área do plano de execução e selecione comparar plano de execução.
Comparação de planos de execução
Na janela abrir, navegue até onde foi salvo o PLANJOIN1, selecione-o e clique em abrir.
Comparação de planos de execução
Será aberta uma janela de comparação dos planos com um abaixo do outro.
Comparação de planos de execução
Há também uma janela de propriedades, na qual são exibidas as diferenças entre as propriedades das
operações dos planos.
Comparação de planos de execução
Observe que as diferenças são, no caso, basicamente o hash e o comando assinalados pelo símbolo de
diferença.
Utilizando índices
Assista ao vídeo e descubra como utilizar índices na otimização de consultas.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Roteiro de prática
Vamos ver como índices são utilizados pelo otimizador de consultas.
Faça conexão ao SGBD com um perfil de DBA.
Escreva uma consulta e execute-a exibindo o plano de execução real e as estatísticas dinâmicas.
Interprete o plano e as estatísticas.
Crie um índice para auxiliar a consulta a partir da sugestão do SSMS.
Reexecute a consulta exibindo o plano de execução real e as estatísticas dinâmicas.
Interprete o plano e as estatística e veja o que mudou com a criação do índice.
Atividade prática
Para fixar o entendimento a respeito da utilização de índices, execute as atividades descritas a seguir:
Acesse o servidor utilizando o SSMS como administrador do servidor.
•
•
•
•
•
•
Conexão ao servidor
Abra uma janela de consulta na SSMS do administrador do sistema e comande:
USE BDTUN.
Utilização de índices
Escreva a seguinte consulta e execute-a:
plain-text
SELECT *
FROM DOCUMENTOS D
INNER JOIN DESPACHO DP ON D.NUMERO = DP.DOCUMENTO_ID
WHERE D.NUMERO = 50000
Para entender melhor, confira a imagem a seguir.
Utilização de índices
Observe que o documento 50000 possui somente um despacho, entre os mais de 1.191.001 da tabela
despacho.
Execute a consulta exibindo o plano de execução real.
Utilização de índices
O plano executado foi:
Utilização de índices
Vamos ver as operações com mais detalhes:
Utilização de índices
O grande custo foi percorrer a tabela despacho toda (94%), pois como não existe índice na coluna
documento_id, é a única forma de encontrar o despacho do documento 50000.
Utilização de índices
Observe que o custo do operador da busca na tabela é de 9,27264 e o de E/S de 7,96238.
Exiba também as estatísticas de consultas dinâmicas.
Utilização de índices
A consulta demorou 0,122s, veja!
Utilização de índices
Será que um índice ajudaria?
Com certeza sim, o próprio SQL SERVER mostra isso. Observe o destaque no cabeçalho do plano de
execução.
Utilização de índices
Será que um índice ajudaria?
Com certeza sim, o próprio SQL SERVER mostra isso. Observe o destaque no cabeçalho do plano de
execução.
Para ver os detalhes dessa observação, clique com o botão direito na área do plano e selecione Detalhes de
índices ausentes, como indicado na imagem.
Utilização de índices
A janela de detalhes mostra o comando a ser utilizado para criar o índice sugerido e a melhoria estimada.
Utilização de índices
Crie o índice sugerido.
CREATE NONCLUSTERED INDEX [docid] ON despacho(documento_id).
Utilização de índices
Confirme a criação do índice.
Utilização de índices
Execute novamente a consulta exibindo o plano de execução real e as estatísticas de consultas dinâmicas.
Utilização de índices
Interprete o plano de execução
Utilização de índices
Observe que agora, durante a execução, ele pesquisa no índice não cluster para obter PK do despacho e no
índice cluster para obter os dados do despacho com cada operação correspondendo a 33% custo. A seguir,
produz a junção das linhas das pesquisas.
Finalmente, faz a junção dessas linhas com as linhas de documentos pesquisando o índice cluster de
documentos com o custo de 33%.
A busca na tabela toda foi, então, substituída pela pesquisa em dois índices com os seguintes custos:
1) Busca no índice não cluster 0,0032831.
Utilização de índices
2) Busca no índice cluster 0,0032831.
Utilização de índices
Cujo total é 0,0065662, bem menor que o custo da busca em toda a tabela, que era de 9,27264.
Analise as estatísticas.
Utilização de índices
Note que o tempo de execução diminuiu para 0,079s.
Índices e hints
Assista ao vídeo e aprenda a utilizar os índices e hints na otimização de consultas.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Roteiro de prática
Vamos ver mais um pouco o uso eficiente de índices:
Faça conexão ao SGBD com um perfil de DBA.
Escreva uma consulta que potencialmente faça uso do índice criado no laboratório anterior.
Execute a consulta e exiba oplano real e as estatísticas.
•
•
•
Interprete o plano e veja se o índice é utilizado.
Escreva e execute consultas diferentes que retornem mais ou menos linhas que a anterior.
Para cada consulta, verifique quando o índice é utilizado.
Escreva e execute uma consulta que obrigue o uso do índice utilizando uma hint.
Interprete o plano e veja se o uso do índice melhora ou piora o custo de execução.
Atividade prática
Para fixar o entendimento a respeito da utilização de hint, execute as atividades descritas a seguir:
Acesse o servidor utilizando o SSMS como administrador do servidor.
Utilização de índices
Abra uma janela de consulta na SSMS do administrador do sistema e comande:
USE BDTUN.
Utilização de índices
Escreva a seguinte consulta e execute-a:
plain-text
SELECT COUNT(*), DOCUMENTO_ID
FROM DESPACHO
GROUP BY DOCUMENTO_ID
ORDER BY 1 DESC
Para entender melhor, confira a imagem a seguir.
•
•
•
•
•
Utilização de índices
Perceba que temos documentos com mais de 1000 despachos, sendo que o 6666 possui 100020.
Escreva a seguinte consulta e execute-a exibindo o plano real e as estatísticas dinâmicas.
plain-text
SELECT *
FROM DOCUMENTOS D
INNER JOIN DESPACHO DP ON D.NUMERO = DP.DOCUMENTO_ID
WHERE D.NUMERO = 6666
Para entender melhor, confira a imagem a seguir.
Utilização de índices
Na aba resultados, temos a exibição das linhas e do resumo da execução.
Utilização de índices
Interprete o plano de execução.
Utilização de índices
Veja que apesar de existir um índice não cluster criado na coluna documento_id, ele não foi utilizado.
O custo do operador de verificação de índice cluster é de 90% do total e seu valor absoluto é 9,27264.
Utilização de índices
Observação importante! O cabeçalho do SSMS avisa da falta do índice, mas ele existe, pois nós o criamos
anteriormente.
Utilização de índices
Veja as estatísticas.
Utilização de índices
Tempo total 0,391s.
Por que o índice não foi utilizado? Por ter baixa seletividade, pois retornam quase 10% das linhas da tabela,
100.020 de 1.191.001.
Utilização de índices
Vamos forçar a utilização do índice com uma hint.
plain-text
SELECT *
FROM DOCUMENTOS D
INNER JOIN DESPACHO DP WITH (INDEX(DOCID))
ON D.NUMERO = DP.DOCUMENTO_ID
WHERE D.NUMERO = 6666
Para entender melhor, confira a imagem a seguir.
Utilização de índices
Interprete o plano de execução.
Note que agora ele faz a varredura no índice cluster.
Utilização de índices
O custo da operação no índice não cluster é 0,240772.
Utilização de índices
O custo da operação no índice cluster é 49,3914, muito alto, pois são realizados 100.020 acessos ao índice,
um para cada linha a ser recuperada.
Utilização de índices
O custo total, então, é: 49,632172; bem maior que os 9,27264 da busca direta no índice cluster.
Veja as estatísticas:
Utilização de índices
5. Conclusão
Considerações finais
O que você aprendeu neste conteúdo?
Visão geral do processo de tuning.
Monitoramento do servidor com o SSMS.
Outras ferramentas de monitoramento.
Índices no SQL Server.
Otimização de armazenamento.
Execução e otimização de consulta.
Planos de execução.
Estatísticas dinâmicas.
Dicas de otimização (hints).
Otimização de consultas com índices.
Explore +
Explore mais os recursos do SQL Server, consultando a documentação disponível no site da Microsoft.
Acesse o site da Microsoft, na área de treinamento e realize cursos sobre SQL Server.
Referências
MICROSOFT IGNITE. Documentação Técnica do SQL Server. Microsoft. Consultado na internet em: 22 mar.
2023.
•
•
•
•
•
•
•
•
•
•
Tuning de banco de dados no SQL Server
1. Itens iniciais
Preparação
Objetivos
Introdução
1. Fundamentos de tuning do SGBD servidor SQL Server
Visão geral do processo de tuning
Conteúdo interativo
Visão geral do tuning do SQL Server
Monitoramento e identificação de gargalos
Otimização de consultas
Índices e estruturas de dados
Configurações do servidor e banco de dados
Particionamento de tabelas
Compactação de dados
Monitoramento contínuo
Ferramentas para tuning do SQL Server
SQL Server Management Studio (SSMS)
SQL Server Profiler
SQL Server Query Store
Database Engine Tuning Advisor (DTA)
Performance Monitor
Extended Events
Dynamic Management Views (DMVs) e dm_exec_query_stats
Atividade 1
Monitorando o servidor com o SSMS
Conteúdo interativo
Importância do monitoramento
Desempenho otimizado
Disponibilidade e confiabilidade
Planejamento de capacidade
Resposta rápida a problemas
Planejamento de manutenção
Monitorando com o monitor de atividade do SSMS
Visão geral
Processos (Processes)
Esperas recentes
E/S do arquivo de dados
Consultas caras recentes
Consultas caras ativas
Monitorando com o painel de desempenho do SSMS
Atividade 2
Outras ferramentas de monitoramento
Conteúdo interativo
SQL Profiler
Captura de eventos
Análise em tempo real
Filtragem
Rastreamento detalhado
Importação e exportação de trace
Eventos Estendidos (Extended Events)
Eventos
Sessões
Filtros
Destinos
Predicados
Histogramas e agregações
Menor sobrecarga no servidor
Maior flexibilidade
Menor impacto no desempenho
Database Tuning Advisor (DTA)
Análise de cargas de trabalho
Sugestões de índices e estatísticas
Relatórios detalhados
Interface gráfica
Opções de personalização
Atividade 3
Índices no SQL Server
Conteúdo interativo
Visão geral dos índices
Índice clusterizado (clustered index)
Índice não clusterizado (nonclustered index)
Criando índices
Atividade 4
Otimização de armazenamento
Conteúdo interativo
Compactação de dados
Page compression (compressão de página)
Row compression (compressão de linha)
Passo 1
Passo 2
Passo 3
Fator de preenchimento (fill factor)
Fragmentação de índice
Atividade 5
2. Tuning de SGBD
Preparando o ambiente
Conteúdo interativo
Roteiro de prática
Atividade prática
Monitorando o servidor com o SSMS
Conteúdo interativo
Roteiro de prática
Atividade prática
Monitorando com Eventos Estendidos
Conteúdo interativo
Roteiro de prática
Atividade prática
Índices clusterizados e não clusterizados
Conteúdo interativo
Roteiro de prática
Atividade prática
Fragmentação e compactação
Conteúdo interativo
Roteiro de prática
Atividade prática
3. Fundamentos de tuning de SQL no SQL Server
xecução e otimização de consulta
Conteúdo interativo
Fases da execução de uma consulta
Análise sintática
Compilação da consulta
Otimização da consulta
Execução da consulta
Como funciona a otimização de consultas
Análise da consulta
Seleção de índices
Seleção da ordem de junção
Definição da técnica de processamento de junção
Nested loop (laço aninhado)
Merge join (junção de merge)
Hash join (junção de hash)
Atividade 1
Planos de execução
Conteúdo interativo
Tipos de plano de execução
Plano de execução estimado
Plano de execução real
Visualizando planos de execução com o SSMS
Atividade 2
Planos de execução e estatísticas dinâmicas
Conteúdo interativo
Estatísticas dinâmicas
Salvando e comparando planos de execução
Salvando um plano de execução
Realize a consulta
Exiba o plano de execução real
Salve o plano de execução
Comparando planos de execução
Realize as consultas
Exiba os planos de execução
Comparação de planos
Atividade 3
Dicas de otimização (hints)
Conteúdo interativo
O que são dicas de otimização (hints)
Dicas de tabela
INDEX
NOEXPAND
FORCESEEK
Dicas de junção
FORCE ORDER
LOOP
HASH
MERGE
Dicas de consulta
FAST n
OPTIMIZE FOR
OPTIMIZE FOR UNKNOWN
USE PLAN
Guias de plano
Atividade 4
Otimização de consultas com índices
Conteúdo interativo
Uso de índices
Acesso rápido aos dados
Redução de E/S
Filtragem e ordenação
Evitar operações caras
Evitar leituras completas de tabela
Critérios para criação de índices
Colunas usadas em cláusulas WHERE e JOIN
Seletividade
Índices compostos
Consultas mais frequentes
Key Lookup e Rid Lookup
Atividade 5
4.Exibição de planos de execução
Gerenciando a autorização
Conteúdo interativo
Roteiro de prática
Atividade prática
Interpretando planos de execução
Conteúdo interativo
Roteiro de prática
Atividade prática
1º) Varredura pelo índice cluster nas tabelas tipo_documento (custo 0%) e documento (custo 1%) e realização de junção, utilizando hash (custo 1%), sendo o custo total de 2%.
º) Varredura pelo índice cluster nas tabelas despacho (custo 28%) e na tabela resultante da junção anterior (custo 1%) e realização da junção utilizando hash (custo 43%), sendo o custo total acumulado até aqui de 73%.
3º) Varredura pelo índice cluster em tipo_despacho (custo 0%), realização da junção hash com o resultado da junção anterior (custo 27%) e retorno do conjunto resposta ao usuário (custo 0%), gerando o custo total acumulado de 100%.
Comparando planos de execução
Conteúdo interativo
Roteiro de prática
Atividade prática
Utilizando índices
Conteúdo interativo
Roteiro de prática
Atividade prática
Índices e hints
Conteúdo interativo
Roteiro de prática
Atividade prática
5. Conclusão
Considerações finais
O que você aprendeu neste conteúdo?
Explore +
ReferênciasO monitoramento constante permite identificar gargalos de desempenho, consultas lentas, bloqueios
e outras situações que podem afetar a velocidade de resposta do banco de dados.
Disponibilidade e confiabilidade
O monitoramento ajuda a garantir que o servidor SQL Server esteja disponível e funcionando
conforme o esperado.
Planejamento de capacidade
Ao acompanhar o uso de recursos como CPU, memória, espaço em disco e largura de banda, os
administradores podem identificar tendências de crescimento e planejar expansões de recursos
quando necessário.
Resposta rápida a problemas
Com o monitoramento em tempo real, os administradores podem identificar problemas assim que eles
ocorrem e tomar medidas imediatas para resolvê-los.
Planejamento de manutenção
O monitoramento auxilia a identificar a necessidade de manutenção regular, como backups,
atualizações de software, reindexações e outras tarefas de administração. Isso ajuda a evitar a
degradação do desempenho e a reduzir o risco de interrupções não planejadas.
Monitorando com o monitor de atividade do SSMS
Monitorar o SQL Server usando o SQL Server Management Studio (SSMS) é uma maneira eficaz de avaliar o
desempenho, identificar problemas e tomar medidas para otimizar o banco de dados.
Após abrir o SSMS, faça a conexão com o servidor utilizando um usuário administrador. No pesquisador de
objetos, localize o nome do servidor que deseja monitorar. Clique com o botão direito no nome do servidor. No
menu de contexto que aparece, selecione a opção monitor de atividade.
Monitor de atividade
No painel do monitor de atividade, você verá várias seções, cada uma fornecendo informações específicas
sobre o desempenho e a atividade do servidor SQL.
1
Visão geral
Fornece informações como a utilização da CPU, memória, E/S de disco e estatísticas de bloqueio.
Você pode rapidamente avaliar o estado geral do servidor e identificar possíveis áreas de
preocupação.
2
Processos (Processes)
Exibe uma lista das sessões ativas no servidor. Você pode ver detalhes sobre cada sessão, como ID
da sessão, status, consulta em execução, tempo decorrido e muito mais. Isso ajuda a identificar as
consultas em execução e verificar se há bloqueios ou sessões problemáticas.
3
Esperas recentes
Permite verificar as estatísticas de espera recentes no servidor. As esperas são eventos que indicam
que uma consulta está aguardando algum recurso ou evento, como bloqueio, I/O de disco, entre
outros. Isso pode ajudar a identificar gargalos e otimizar o desempenho do sistema.
4E/S do arquivo de dados
Monitora as operações de leitura e gravação nos arquivos de dados do SQL Server. Isso inclui
detalhes sobre arquivos de dados, tempos de resposta de E/S e outras métricas relacionadas ao
armazenamento.
5
Consultas caras recentes
Informações sobre as consultas recentes que foram mais caras em termos de recursos, como CPU,
tempo e E/S. Isso ajuda a identificar consultas que impactam negativamente o desempenho do
servidor.
6
Consultas caras ativas
Monitora as consultas ativas que estão consumindo muitos recursos. Isso permite uma visão em
tempo real das consultas que podem estar causando problemas de desempenho.
Monitorando com o painel de desempenho do SSMS
O painel de desempenho do SQL Server Management Studio (SSMS) é uma ferramenta poderosa que permite
aos administradores de banco de dados monitorar, analisar e otimizar o desempenho de instâncias do SQL
Server. Ele oferece uma visão consolidada das métricas e estatísticas essenciais do servidor, permitindo tomar
medidas informadas para melhorar a performance do banco de dados.
Após abrir o SSMS, faça a conexão com o servidor utilizando um usuário administrador para exibir o painel de
desempenho. Clique com o botão direito do mouse no nome da instância SQL Server no Pesquisador de
Objetos, selecione Relatórios e clique em painel de desempenho.
Painel de Desempenho do SSMS
O painel de desempenho é dividido nas seções e sub-relatórios a seguir:
Utilização da CPU do Sistema
Solicitações em Espera Atuais
Atividade Atual
•
•
•
Informação Histórica
Informações Diversas
Atividade 2
O SQL Server Management Studio inclui o painel de desempenho. Esse painel foi projetado para fornecer
visualmente informações rápidas sobre o estado de desempenho do SQL Server. Nesse contexto, analise as
seções listadas a seguir.
I. Utilização da CPU do sistema.
II. Solicitações em espera atuais.
III. Atividade atual.
IV. Monitor de atividade.
Qual opção inclui as seções que fazem parte do painel de desempenho do SQL Server Management Studio
(SSMS)?
A I, II e III.
B I, II e IV.
C I, III e IV.
D II e III.
E I e III.
A alternativa A está correta.
O texto discute as seções presentes no painel de desempenho do SQL Server Management Studio (SSMS),
que incluem utilização da CPU do sistema, solicitações em espera atuais e atividade atual. No entanto, o
monitor de atividade não é mencionado como parte do painel de desempenho.
Outras ferramentas de monitoramento
Assista ao vídeo e conheça outras ferramentas capazes de monitorar o servidor SQL Server, suas vantagens,
seus recursos e suas funcionalidades.
•
•
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
SQL Profiler
Conhecido também simplesmente como Profiler, é uma ferramenta que faz parte do conjunto de ferramentas
de gerenciamento e desenvolvimento do SQL Server. Ele permite que os administradores e desenvolvedores
capturem e analisem eventos e atividades que ocorrem no banco de dados em tempo real.
Alguns dos recursos e benefícios oferecidos pelo SQL Server Profiler incluem:
Captura de eventos
Permite que você selecione e capture eventos específicos que deseja monitorar, o que ajuda a
destacar a atividade relevante do banco de dados.
Análise em tempo real
Exibe os eventos capturados em tempo real, fornecendo uma visão imediata das atividades do banco
de dados.
Filtragem
Aplica filtros para restringir o escopo dos eventos capturados, facilitando a análise do que é mais
importante.
Rastreamento detalhado
Fornece informações detalhadas sobre o tempo de execução, parâmetros, planos de execução e
muito mais, o que é útil para a otimização de consultas e desempenho.
Importação e exportação de trace
Salva perfis capturados para revisão posterior ou importá-los em outros ambientes para análise.
Eventos Estendidos (Extended Events)
São uma tecnologia avançada de rastreamento e monitoramento no Microsoft SQL Server, projetada para
oferecer um mecanismo de rastreamento de eventos mais leve, flexível e escalável em comparação com o
Profiler.
A principal ideia do Extended Events é fornecer uma estrutura extensível e altamente configurável para
capturar, processar e analisar eventos dentro do SQL Server. Ele permite que os administradores e
desenvolvedores criem sessões de eventos personalizadas para rastrear informações específicas que
desejam monitorar no banco de dados.
Confira alguns conceitos-chave relacionados ao Extended Events.
1
Eventos
São os eventos individuais que podem ser monitorados. Eles podem incluir ações como início de
uma consulta, término de uma consulta, erros, avisos etc.
2
Sessões
Uma sessão de eventos é um contêiner que agrupa os eventos que você deseja capturar. Você pode
criar sessões personalizadas com base em suas necessidades de monitoramento.
3
Filtros
É possível aplicar filtros para capturar apenas os eventos relevantes. Isso reduz a sobrecarga no
servidor e ajuda a focar nas informações essenciais.
4
Destinos
Os eventos capturados podem ser enviados para diferentes destinos, como arquivos de log, tabelas
temporárias ou até mesmo para outras sessões.
5
Predicados
Os predicados são usados para definir condições específicas para captura de eventos. Isso permite
capturar apenas eventos que atendam a determinados critérios.
6
Histogramas e agregações
O Extended Events também podem criar histogramas e agregar dados para análise mais
aprofundada.
Comparado ao SQL Server Profiler, o ExtendedEvents oferece várias vantagens:
1
Menor sobrecarga no servidor
É mais eficiente em termos de recursos do servidor, tornando-o adequado para ambientes de
produção com alto volume de transações.
2
Maior flexibilidade
O Extended Events permite uma configuração mais detalhada e customizada, possibilitando uma
captura mais precisa dos eventos necessários.
3 Menor impacto no desempenho
Por ser mais leve, o Extended Events tem menos impacto no desempenho do SQL Server em
comparação com o Profiler.
Database Tuning Advisor (DTA)
Ferramenta da Microsoft que faz parte do conjunto de ferramentas do SQL Server Enterprise projetado para
auxiliar os administradores de banco de dados e desenvolvedores a otimizarem o desempenho de consultas e
cargas de trabalho em bancos de dados SQL Server.
O objetivo principal do Database Tuning Advisor é identificar oportunidades de otimização e propor sugestões
para melhorar o desempenho das consultas e operações no banco de dados. Ele faz isso examinando as
consultas e cargas de trabalho existentes e sugerindo índices, estatísticas e partições que podem melhorar a
eficiência das consultas e reduzir o tempo de resposta.
Vamos ver quais são os principais recursos e funcionalidades do DTA.
Análise de cargas de trabalho
Examina consultas e operações existentes no banco de dados para identificar padrões de uso e
desempenho.
Sugestões de índices e estatísticas
Sugere índices adicionais, estatísticas atualizadas e até mesmo partições para melhorar o
desempenho das consultas, com base na análise.
Relatórios detalhados
Após a análise, gera relatórios detalhados que mostram as sugestões de otimização, o impacto
estimado nas consultas e os benefícios esperados.
Interface gráfica
Possui uma interface gráfica fácil de usar, que permite aos usuários selecionarem as consultas para
análise, ajustar configurações e revisar as sugestões antes de aplicá-las.
Opções de personalização
Os usuários podem personalizar as opções de análise, como definir o tempo de execução da análise e
selecionar as consultas específicas a serem incluídas.
Atividade 3
O SQL Server Extended Events e o SQL Server Profiler são ferramentas para monitoramento. Qual é uma das
principais vantagens do SQL Server Extended Events (Eventos Estendidos) em comparação com o SQL Server
Profiler?
A Oferece uma análise em tempo real dos eventos capturados.
B Permite a criação de sessões de eventos personalizadas.
C Fornece informações detalhadas sobre o tempo de execução das consultas.
D Fornece recursos avançados para análise de cargas de trabalho.
E Captura eventos relacionados apenas ao início e término das consultas.
A alternativa B está correta.
Os Eventos Estendidos são um sistema de monitoramento de desempenho leve que permite que os
usuários coletem os dados necessários para monitorar e solucionar problemas no SQL Server. Isso fornece
uma flexibilidade muito maior em comparação com o SQL Server Profiler, permitindo adaptar o
monitoramento de acordo com as necessidades específicas do ambiente.
Índices no SQL Server
Assista ao vídeo e compreenda a importância e os benefícios dos índices no SQL Server.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Visão geral dos índices
No SQL Server, existem vários tipos de índices cada um com suas características e benefícios específicos.
Quanto à sua estrutura, os índices podem ser de dois tipos:
Índice clusterizado (clustered index)
ada tabela pode ter apenas um índice
clusterizado, que determina a ordem física de
armazenamento das linhas da tabela. Quando
um índice clusterizado é criado, as linhas da
tabela são reorganizadas para seguir a ordem
especificada pelo índice. Isso ajuda a melhorar
a recuperação de intervalos de dados
consecutivos. Como a tabela é reorganizada
fisicamente, a criação ou recriação de um índice
clusterizado pode ser uma operação intensiva.
Devem, de preferência, ser criados em campos
numéricos com dados crescentes, únicos e que
não sofram atualizações constantes.
Exemplo de criação:
CREATE CLUSTERED INDEX CPFEMP01 ON
dbo.EMPREGADO(CPF).
Índice não clusterizado (nonclustered
index)
Pode haver vários índices não clusterizados em
uma tabela. Cada índice não clusterizado possui
uma estrutura separada que armazena as
chaves de índice e uma referência às linhas
correspondentes na tabela. Isso permite uma
busca rápida nas colunas indexadas. Os índices
não clusterizados são especialmente úteis
quando as colunas de índice não seguem a
mesma ordem de armazenamento das linhas da
tabela.
Exemplo de criação:
CREATE NOCLUSTERED INDEX CPFEMP01 ON
dbo.EMPREGADO(CPF).
Quanto ao tratamento das colunas englobadas nos índices, eles podem ser:
Índice composto: É um índice formado por mais de uma coluna. Nesse caso, a estrutura de árvore do
índice terá as informações das colunas que fazem parte do índice em todos os níveis. Buscas utilizando
essas colunas serão filtradas mais rapidamente.
Exemplo de criação
CREATE NONCLUSTERED INDEX COMPIND01 ON dbo.EMPREGADO(CPF, RG).
Índice com coluna incluída: É um índice formado por uma ou mais colunas e que inclui outras colunas.
Nesse caso, a estrutura de árvore do índice é construída apenas com as colunas que fazem parte do
índice. A informação das colunas incluídas está disponível somente no último nível da árvore (folha),
ocupando assim menos espaço em disco em comparação com um índice composto.
Exemplo de criação:
CREATE NONCLUSTERED INDEX COLINCI1 ON dbo.EMPREGADO(CPF) INCLUDE(RG, Nome).
Quanto aos dados dos índices, eles podem ser:
Criando índices
•
•
Únicos
A chave de índice não
contém nenhum valor
duplicado, cada linha na
tabela é exclusiva de algum
modo. Se o índice englobar
várias colunas, ele assegura
que cada combinação de
valores na chave de índice
seja exclusiva.
Exemplo
de
criação:
CREATE UNIQUE
NONCLUSTERED INDEX
CPFUK01 ON
dbo.EMPREGADOS(CPF).
Não únicos
Em um índice não único,
uma chave pode estar
associada a várias linhas
diferentes na tabela de
dados, o que é comum
em situações nas quais
há duplicatas ou valores
semelhantes em uma
coluna indexada.
Exemplo
de
criação:
CREATE NONCLUSTERED
INDEX NOMEMP01 ON
dbo.EMPREGADOS(NOME).
Para criar índices utilizando o SSMS, execute os seguintes passos:
1. Inicie o SQL Server Management Studio em seu computador.
2. Conecte-se ao servidor SQL e ao banco de dados no qual deseja criar os índices.
3. No painel de objetos do SSMS, expanda o banco de dados e a pasta Tabelas. Localize a tabela para a qual
você deseja criar índices.
4. Crie o Índice para tal:
4.1. Clique com o botão direito do mouse na tabela e selecione Índices > Novo Índice.
4.2. Isso abrirá a janela de criação de índices.
5. Configure as propriedades do índice para tal na janela de criação de índices. Você precisa configurar várias
propriedades:
5.1. Nome do índice: Dê um nome significativo para ele.
5.2. Tipo de índice: Escolha o tipo adequado, como clusterizado ou não clusterizado.
5.3. Colunas de índice: Selecione as colunas que serão incluídas. Isso dependerá das consultas que você
executa com mais frequência.
5.4. Opções: Configure opções adicionais, como preenchimento (fill factor), ordenação e filtragem, se
necessário.
6. Após configurar todas as propriedades do índice, clique em OK para criá-lo.
Depois de criar o índice, você pode executar suas consultas para verificar se elas estão aproveitando o novo
índice e obtendo um desempenho melhor.
Atividade 4
Há um tipo de índice que armazena a ordem física de armazenamento das linhas de uma tabela e é mais
apropriado para campos numéricos com dados crescentes e únicos. Que índice é esse?
A Índice clusterizado
B Índice não clusterizado
C Índice composto
D Índice com coluna incluída
E Índice único
A alternativa A está correta.
Um índice clusterizado determina a ordem em que as linhas de uma tabela são armazenadas no disco. Se
uma tabela tem um índice clusterizado, no momento de um insert as linhasdessa tabela serão
armazenadas em disco na ordem exata do mesmo índice. Isso é especialmente útil para recuperação de
intervalos de dados consecutivos. Normalmente, esses índices são criados em campos numéricos com
dados crescentes e únicos para melhorar a eficiência da recuperação de dados.
Otimização de armazenamento
Assista ao vídeo e compreenda a importância da compactação de dados como técnica essencial para reduzir
o espaço ocupado pelos dados no SQL Server.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Compactação de dados
É uma técnica essencial para reduzir o espaço ocupado pelos dados no SQL Server, o que pode levar a
economias significativas de armazenamento e melhorias no desempenho das consultas.
O SQL Server oferece várias opções de compactação de dados para ajudar a reduzir o espaço de
armazenamento necessário e melhorar o desempenho.
Vamos conferir os principais tipos de compactação de dados disponíveis no SQL Server.
1Page compression (compressão de página)
É uma técnica que comprime os dados em nível de página. Ele usa algoritmos de compactação para
reduzir o tamanho das páginas de dados armazenadas no disco. Isso resulta em economia de espaço
de armazenamento, mas também pode aumentar um pouco a sobrecarga de CPU durante a
descompressão dos dados durante a leitura. O Page Compression é adequado para dados históricos
e arquivados.
2
Row compression (compressão de linha)
É uma técnica que comprime cada linha de dados individualmente. Ela usa algoritmos de
compactação para reduzir o tamanho de cada linha armazenada no disco. O Row Compression é
menos intensivo em CPU do que o Page Compression, mas também proporciona economia de
espaço. Geralmente, é adequado para tabelas ativas que precisam de um equilíbrio entre economia
de espaço e desempenho.
Para realizar a compactação, observe os seguintes passos:
Passo 1
Conectando-se ao banco de dados
Abra o SQL Server Management Studio (SSMS).
Conecte-se ao servidor que hospeda o banco de dados que você deseja compactar.
Passo 2
Selecionando a tabela ou índice para compactação
Expanda o nó do banco de dados no explorador de objetos para exibir a lista de tabelas e
índices.
Clique com o botão direito na tabela ou índice que você deseja compactar e selecione
Armazenamento /Gerenciar Compactação.
Passo 3
Configurando e realizando a compactação
Utilizando o assistente, defina o tipo de compactação (ROW, PAGE e NONE). Selecione o tipo
de compactação desejado com base nas suas necessidades.
Defina forma/momento da compactação e clique em concluir.
Fator de preenchimento (fill factor)
1.
2.
1.
2.
1.
2.
É um conceito importante no SQL Server, que afeta a eficiência de armazenamento e o desempenho das
operações de inserção e atualização em tabelas e índices. Ele determina a porcentagem de espaço
preenchido em uma página de índice ou de dados quando uma tabela ou índice é criado ou reconstruído.
O fill factor deve ser um valor inteiro de 1 a 100. Se o fill factor for 100, o Mecanismo de Banco de Dados criará
índices com páginas de folhas preenchidas até a capacidade total. Se nada for especificado na criação/
recriação do índice ele será definido com 0, o que indica a utilização do valor padrão do servidor, normalmente
100.
Exemplo de comando de criação com fill fator:
plain-text
CREATE INDEX NomeDoIndice
ON NomeDaTabela (Coluna1, Coluna2, ...)
WITH (FILLFACTOR = 80); -- Substitua 80 pelo valor desejado
Fragmentação de índice
É um fenômeno que ocorre ao longo do tempo à medida que as páginas de dados em um índice ficam
desorganizadas ou não contíguas. Isso pode acontecer devido à inserção, exclusão ou atualização de dados
nas tabelas subjacentes que o índice está indexando. A fragmentação de índice pode afetar negativamente o
desempenho das consultas e as operações de banco de dados.
Vamos entender os dois métodos existentes para lidar com a fragmentação.
Atividade 5
Há um conceito importante no SQL Server, que determina a porcentagem de espaço preenchido em uma
página de índice ou de dados quando uma tabela ou índice é criado ou reconstruído. Que conceito é esse?
A Espaço reservado (reserved space)
Reorganizar (Reorganize)
Envolve a reorganização das páginas de
índice existentes para reduzir a fragmentação.
Durante esse processo, as páginas são
reorganizadas em uma ordem lógica, mas o
índice permanece on-line e disponível para
consulta. Esse é um processo mais leve em
termos de bloqueio e uso de recursos em
comparação com o rebuild. Ele é
recomendado para índices com níveis
moderados de fragmentação.
Recompilar (Rebuild)
Envolve a recriação completa do índice.
Durante o processo de rebuild, o índice é
excluído e recriado a partir dos dados
originais da tabela. Isso resolve a
fragmentação e pode ajudar a melhorar
as estatísticas do índice. No entanto, o
índice não estará disponível durante o
processo de rebuild, o que pode afetar a
disponibilidade das consultas que usam
esse índice. O rebuild é mais intensivo
em recursos e pode ser mais apropriado
para índices com níveis significativos de
fragmentação ou quando é necessário
atualizar as estatísticas do índice.
B Taxa de ocupação (occupancy rate)
C Fator de preenchimento (fill factor)
D Grau de compactação (compression degree)
E Índice de utilização (utilization index)
A alternativa C está correta.
O fill factor é um conceito importante no SQL Server, que define a porcentagem de espaço preenchido em
uma página de índice ou de dados ao criar ou reconstruir uma tabela ou índice. Ele influencia a eficiência de
armazenamento e o desempenho das operações de inserção e atualização em tabelas e índices. O valor
padrão do fill factor é 100, mas pode ser ajustado para otimizar o espaço de armazenamento e o
desempenho das operações.
2. Tuning de SGBD
Preparando o ambiente
Assista ao vídeo e descubra como instalar e configurar o SGBD SQL Server, assim como preparar o ambiente
para o tuning.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Roteiro de prática
Para preparar o ambiente de estudo, vamos fazer as seguintes atividades:
Baixar o instalador do SGBD.
Realizar a instalação e configuração inicial do servidor.
Baixar e instalar o SQL Server Management Studio.
Acessar o servidor utilizando SSMS.
Criar um banco de dados.
Criar o usuário.
Se o modo não for misto, mudar para esse tipo e reiniciar o serviço do SQL Server.
Atividade prática
Após a instalação do SGBD e do SSMS, realize as atividades descritas a seguir:
Preparando o ambiente
Após baixar e instalar o SQL Server Express e o SQL Server Management Studio, acesse o servidor utilizando
o SSMS.
•
•
•
•
•
•
•
Interface de Input de dados: nome do servidor
Crie o banco de dados de nome BDTUN utilizando a interface gráfica.
Clique com o botão direito em banco de dados e selecione novo banco de dados.
Interface de input de dados: Novo banco de dados
Na janela que abrir, digite o nome do banco de dados, no caso, BDTUN, e depois clique em OK.
Interface de input de dados: BDTUN
Confirme a criação do banco de dados na listagem do pesquisador de objetos.
Listagem pesquisador de objetos
Confirme se o servidor está no modo misto de autenticação.
Clique com o botão direito no nome do servidor e selecione propriedades.
Interface de input de dados: propriedades
Na janela de propriedades:
Interface de input de dados: propriedades
Confirme se está no modo misto.
Interface de Input de dados: Modo de autenticação do SQL server e do Windows
Se estiver em autenticação do Windows, mude para misto.
Interface de input de dados: modo de autenticação do Windows
Siga os seguintes passos para passar para o modo misto:
Selecione modo de autenticação do SQL Server e do Windows e clique em OK.
Interface de input de dados: autenticação do servidor
Agora reinicie o serviço do SQL Server.
Clique com o botão direito no nome do servidor e selecione reiniciar.
Interface de Input de dados:reiniciar
Autorize o reinício.
Autorização de dados
Confirme para o SSMS.
Confirmação de reinício
Após o reinício, o servidor estará no modo de autenticação misto.
Crie o logon USUTUN utilizando a interface gráfica.
Clique com o botão direito em logon e selecione novo logon.
Seleção: novo logon
Na janela que abrir, digite o nome do logon, no caso USUTUN, a senha, e desmarque impor política de senha.
Interface de input de dados: USUTUN
Defina o banco de dados padrão como BDTUN.
Interface de Input de dados: BDTUN
Na aba funções de servidor marque sysadmin e clique em OK.
Seleção: Funções de servidor
Confirme a criação do logon na listagem do pesquisador de objetos.
Listagem do pesquisador de objetos: users
Monitorando o servidor com o SSMS
Assista ao vídeo e entenda a importância de monitorar o servidor SQL Server com o SSMS, na garantia de
melhor desempenho e mais segurança em sistemas de banco de dados.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Roteiro de prática
Vamos ver como monitorar o servidor utilizando o SSMS:
Acessar o servidor utilizando SSMS.
Abrir o monitor de atividade.
Abrir o painel de desempenho.
Fazer login com o usuário no Azure Data Studio.
Gerar tráfego no Azure Data Studio com os comandos do arquivo criar criarbancotuning.txt.
Acompanhar o funcionamento do servidor no SSMS.
Atividade prática
Vamos criar as tabelas e inserir os dados que serão utilizados nos próximos laboratórios, empregando o Azure
Data Studio e o usuário criado, bem como monitorar o funcionamento do servidor.
•
•
•
•
•
•
https://stecine.blob.core.windows.net/repositorio/00212ti/40174/docs/criarbancotuning.txt
https://stecine.blob.core.windows.net/repositorio/00212ti/40174/docs/criarbancotuning.txt
Para isso, realize as atividades práticas descritas a seguir.
Acesse o servidor utilizando o SSMS.
Interface de Input de dados: conectar
Abra o monitor de atividade.
Clique com o botão direito no nome do servidor e selecione monitor de atividade.
Monitor de atividade
A janela do monitor de atividade será aberta.
Visão geral
Faça conexão com USUTUN utilizando o Azure Data Studio.
Abra o Azure Data Studio.
Azure Data Studio
Selecione Create a Connection.
Create a Connection
Preencha os dados de conexão definindo:
O NOME DO SERVIDOR.
Tipo de autenticação: SQL SERVER.
USERNAME: USUTUN.
SENHA:
Mude Encrypt para false
•
•
•
•
•
A seguir clique em connect.
Connection details
Com isso, a conexão ocorrerá.
Conexão
O nome do servidor varia de instalação para instalação. A melhor forma de saber o seu é copiar o nome que
está na janela de login do administrador do SSMS para usar no Azure Data Studio.
Conectar ao servidor
Gerando atividade no servidor.
Após a conexão, note que você está como USUTUN. Clique em new query.
New Query
Confirme o banco de dados em que USUTUN está conectado, escrevendo o comando e clicando em run.
SELECT DB_NAME() AS BancoAtual;
Run
Vamos criar as tabelas para usar durante laboratórios de tuning. Para isso, copie e cole do conteúdo de
criarbancotuning.txt para a janela de consulta e clique em run.
Estes comandos criam quatro tabelas:
Tipo_documento com 15 linhas.
Tipo_despacho com 11 linhas.
Documentos com 50000 linhas.
Despacho com 1191001 linhas.
Como a execução demorará um pouco, vamos aproveitar para monitorar a atividade e o desempenho do
servidor.
•
•
•
•
https://stecine.blob.core.windows.net/repositorio/00212ti/40174/docs/criarbancotuning.txt
https://stecine.blob.core.windows.net/repositorio/00212ti/40174/docs/criarbancotuning.txt
Run
Durante a execução, acesse o monitor de atividade para observar os vários aspectos do funcionamento.
Processos
Consultas caras recentes
Acesse também o painel de desempenho.
Painel de desempenho
Após o término da execução com USUTUN comande:
SELECT COUNT(*) FROM DESPACHO e
SELECT COUNT (*) FROM DOCUMENTOS
Criação de tabelas e inserção de dados
Criação de tabelas e inserção de dados
SELECT COUNT(*) FROM TIPO_DOCUMENTO e
SELECT COUNT (*) FROM TIPO_DESPACHO
Criação de tabelas e inserção de dados
Criação de tabelas e inserção de dados
Esses comandos confirmam que as tabelas foram criadas e as linhas inseridas.
Monitorando com Eventos Estendidos
Assista ao vídeo e descubra como monitorar o servidor SQL Server com Eventos Estendidos.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Roteiro de prática
Vamos ver como configurar Eventos Estendidos:
Faça conexão ao SGBD com um perfil de Administrador com o SSMS.
Crie uma sessão de Eventos Estendidos.
Configure a sessão.
Inicie a sessão.
Pare a sessão.
Atividade prática
•
•
•
•
•
Para praticar a criação e configuração de Eventos Estendidos, execute as atividades descritas a seguir.
Acesse o servidor utilizando o SSMS.
Conexão ao servidor
No pesquisador de objetos, clique em Gerenciamento > Eventos Estendidos > Nova Sessão. Embora as duas
sejam semelhantes, dê preferência à caixa de diálogo Nova Sessão, em vez de Assistente de Nova Sessão.
Criação e configuração de eventos estendidos
No canto superior esquerdo, clique na página geral. Digite minhasessao ou qualquer nome que desejar na
caixa de texto nome da sessão.
Criação e configuração de eventos estendidos
No canto superior esquerdo, clique na página Eventos e no botão selecionar.
Criação e configuração de eventos estendidos
Na área Biblioteca de eventos, na lista suspensa, escolha Somente nomes de evento.
Criação e configuração de eventos estendidos
Na caixa de texto, digite sql, que filtra e reduz a longa lista de eventos disponíveis, usando um operador
contains.
Criação e configuração de eventos estendidos
Role e clique no evento chamado sql_statement_completed e a seguir no botão de seta para a direita > para
mover o evento para a caixa Eventos selecionados.
Criação e configuração de eventos estendidos
Confirme que o evento foi selecionado.
Criação e configuração de eventos estendidos
Na página Eventos, clique no botão Configurar, posicionado à extrema direita.
Criação e configuração de eventos estendidos
Clique na guia Filtro (Predicado).
Criação e configuração de eventos estendidos
Em seguida, clique em Clique aqui para adicionar uma cláusula para capturar todas as instruções SQL SELECT
que têm uma cláusula GROUP BY.
Criação e configuração de eventos estendidos
Na lista suspensa Campo, escolha sqlserver.sql_text. Em Operador, escolha um operador like e em valor digite
%SELECT%GROUP%.
Criação e configuração de eventos estendidos
No canto superior esquerdo, clique na página Armazenamento de Dados.
Criação e configuração de eventos estendidos
Na área Destinos, clique em Clique aqui para adicionar um destino.
Na lista suspensa Tipo, escolha event_file. Isso significa que os dados de evento serão armazenados em um
arquivo que pode ser exibido.
Criação e configuração de eventos estendidos
Na área Propriedades, digite um nome de arquivo e o caminho completo na caixa de texto Nome de arquivo no
servidor.
A extensão de nome de arquivo deve ser “.xel”.
Criação e configuração de eventos estendidos
No canto superior esquerdo, clique na página Avançado.
Criação e configuração de eventos estendidos
Reduza a Latência máxima de expedição até 3 segundos.
Criação e configuração de eventos estendidos
Por fim, clique no botão OK na parte inferior.
Criação e configuração de eventos estendidos
De volta ao pesquisador de objetos, expanda Gerenciamento/Sessões e veja o novo nó de minhasessao.
Criação e configuração de eventos estendidos
No pesquisador de objetos do SSMS, clique com o botão direito do mouse no nó da sessão de evento e clique
em Iniciar Sessão.
Criação e configuração de eventos estendidos
Execute comandos de SELECT...com group by
Clique com o botão direito do mouse no nó da sessão e clique em Parar Sessão.
Criação e configuraçãode eventos estendidos
Para exibir os eventos capturados clique com o botão direito e selecione Visualizar Dados de Destino.
Criação e configuração de eventos estendidos
Índices clusterizados e não clusterizados
Assista ao vídeo e acompanhe a implementação de índices clusterizados e não clusterizados.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Roteiro de prática
Vamos ver como funcionam os índices clusterizados e os não clusterizados.
Faça conexão ao SGBD com um perfil de administrador com o SSMS.
Crie uma tabela com chave primária.
Valide a criação do índice cluster esquemas.
Insira dados na tabela em sucessivas operações.
Entre cada inserção, recupere os dados da tabela.
•
•
•
•
•
Veja as propriedades do índice criado para a chave primária.
Crie uma tabela sem chave primária.
Crie um índice na coluna chave não clusterizado.
Valide a criação do índice.
Insira dados na segunda tabela em sucessivas operações.
Entre cada inserção, recupere os dados da segunda tabela.
Veja as propriedades do índice criado para a segunda tabela.
Atividade prática
Para fixar o entendimento do funcionamento e as características dos índices clusterizados e não
clusterizados, execute as atividades descritas a seguir.
Acesse o servidor utilizando o SSMS como administrador do servidor.
Conexão ao servidor
Abra uma janela de consulta.
Conexão ao servidor
Acesse o banco BDTUN para isso comande USE BDTUN.
Funcionamento e características dos índices clusterizados e não clusterizados
Crie uma tabela chamada Clientes1 com o seguinte comando:
•
•
•
•
•
•
•
plain-text
CREATE TABLE Clientes1(
Codigo INT PRIMARY KEY,
Nome VARCHAR(40),
Endereco VARCHAR(40)
);
Para ilustrar o processo, confira a imagem a seguir.
Funcionamento e características dos índices clusterizados e não clusterizados
Insira dados na tabela utilizando o seguinte bloco TRANSACT SQL:
plain-text
DECLARE @Counter INT = 1;
WHILE @Counterpropriedades.
Fragmentação e compactação de dados
Na janela de propriedades, clique em fragmentação.
Fragmentação e compactação de dados
Observe que o índice possui uma alta fragmentação devida aos vários insert realizados. Lembre-se de que ele
é um índice cluster e a cada inserção ele necessita acertar a ordem pela coluna de indexação. Observe ainda
a quantidade de páginas.
Reorganize o índice de forma a tratar a fragmentação e para isso:
Clique com o botão direito no nome do índice e escolha reorganizar.
Fragmentação e compactação de dados
Na janela seguinte clique em OK.
Fragmentação e compactação de dados
Feche e abra novamente a janela de propriedades do índice e acesse fragmentação.
Fragmentação e compactação de dados
Note que a fragmentação praticamente sumiu e a quantidade de páginas do índice diminuiu.
Clique com o botão direito no nome da tabela Clientes1 e selecione propriedades.
Fragmentação e compactação de dados
Na janela de propriedades, clique em armazenamento.
Fragmentação e compactação de dados
Perceba o espaço ocupado pela tabela e que a compactação não está sendo utilizada.
Fragmentação e compactação de dados
Na janela do assistente, clique em avançar.
Fragmentação e compactação de dados
Na janela seguinte, selecione o tipo de compactação e clique em calcular.
Fragmentação e compactação de dados
Tipo Row:
Fragmentação e compactação de dados
Tipo Page:
Fragmentação e compactação de dados
Após selecionar o tipo clique em avançar.
Fragmentação e compactação de dados
Na janela seguinte, selecione executar imediatamente e clique em concluir.
Fragmentação e compactação de dados
Na próxima janela será exibido o resumo. Clique em Concluir.
Fragmentação e compactação de dados
Na janela seguinte, será exibido o progresso. Após o término, clique em fechar.
Fragmentação e compactação de dados
Abra novamente a janela de propriedades de Cliente1 e selecione armazenamento.
Fragmentação e compactação de dados
Note que o espaço ocupado pela tabela diminui e o tipo de compactação no caso é page.
3. Fundamentos de tuning de SQL no SQL Server
xecução e otimização de consulta
Assista ao vídeo e compreenda a importância de se monitorar a execução e otimização de consulta no SQL
Server.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Fases da execução de uma consulta
Quando é submetido ao otimizador para determinar o seu plano de execução, o processamento da consulta
passa por quatro fases:
1
Análise sintática
A sintaxe da consulta é validada e a consulta é transformada em uma árvore. Em seguida, é
verificada a validação de todos os objetos do banco de dados referenciados pela consulta.
2
Compilação da consulta
A árvore de consulta é compilada pelo otimizador de consultas.
3
Otimização da consulta
O otimizador de consultas recebe como entrada a árvore de consulta compilada e investiga várias
estratégias de acesso antes de decidir como processar a consulta.
4
Execução da consulta
Após a geração do plano de execução, ele é armazenado permanentemente e executado.
Como funciona a otimização de consultas
A fase de otimização de consultas pode ser dividida em análise da consulta, seleção de índices, seleção da
ordem de junção e definição da técnica de processamento de junção. Vamos detalhar cada uma dessas
etapas.
Análise da consulta
Durante esta etapa, o otimizador examina a consulta em busca de argumentos de busca, do uso do operador
OR e da existência de critérios de junção, nessa ordem. Como o uso do operador OR e a existência de critérios
de junção são autoexplicativos, apenas os argumentos de busca serão discutidos.
Um argumento de busca é a parte de uma consulta que restringe o conjunto de resultados intermediários da
consulta. O principal objetivo dos argumentos de busca é permitir o uso de índices existentes em relação à
expressão fornecida.
Um exemplo de argumento de busca é:
Entretanto, devemos levar em conta que alguns tipos de expressão não podem ser usados pelo otimizador
como argumentos de busca, como expressões com o operador NOT (). Além disso, se você usar a
expressão no lado esquerdo do operador, a expressão existente não poderá ser usada como argumento de
busca.
Exemplos:
NOT IN ('d1', 'd2')
emp_no 9031
budget * 0.59 > 55000
Seleção de índices
A identificação de argumentos de busca permite que o otimizador decida se um ou mais índices existentes
serão utilizados. Nesta etapa, o otimizador verifica cada argumento de busca para ver se existem índices em
relação à expressão correspondente. Se um índice existe, o otimizador decide se irá ou não utilizá-lo. Essa
decisão depende principalmente da seletividade da expressão correspondente, que é definida como a
proporção do número de linhas que satisfazem a condição para o número total de linhas na tabela. Quanto
menor o número de linhas que satisfazem a condição, maior a seletividade.
O otimizador verifica a seletividade de uma expressão com a coluna indexada usando estatísticas criadas em
relação à distribuição de valores em uma coluna. O otimizador de consultas utiliza essas informações para
determinar o plano de execução da consulta mais eficiente, estimando o custo de usar um índice para
executar a consulta.
Seleção da ordem de junção
Geralmente, a ordem na qual duas ou mais tabelas unidas são escritas na cláusula FROM de um comando
SELECT não influencia a decisão tomada pelo otimizador em relação à ordem de processamento delas.
A ordem de junção pode ser influenciada por diversos fatores, sendo o otimizador responsável por determinar
a melhor ordem para acessar as tabelas envolvidas na junção, levando em consideração a estrutura das
tabelas, os índices disponíveis, as estatísticas de distribuição de dados e outras informações relevantes.
Definição da técnica de processamento de junção
A operação de junção é a operação mais demorada no processamento de consultas. O motor de banco de
dados oferece suporte às três técnicas diferentes de processamento de junção, para que o otimizador possa
escolher uma delas, considerando fatores como tamanho das tabelas, índices disponíveis e estatísticas.
Vamos conhecê-las!
s a l a r y >= 50000
•
•
•
Nested loop (laço aninhado)
O otimizador utiliza loops aninhados para
combinar as linhas das tabelas. É uma técnica
simples, na qual para cada linha na primeira
tabela, o otimizador varre toda a segunda
tabela em busca de correspondências. Essa
técnica é eficaz quando uma das tabelas é
muito pequena e a outra é grande.
Merge join (junção de merge)
As tabelas envolvidas na junção devem estar
ordenadas com base nas colunas de junção. O
otimizador, em seguida, percorre ambas as
tabelas simultaneamente, combinando as linhas
correspondentes. É uma técnica eficiente para
junções de tabelas grandes e bem ordenadas.
Hash join (junção de hash)
O otimizador cria tabelas hash temporárias para
ambas as tabelas envolvidas na junção, com
base nas colunas de junção. As tabelas hash
permitem que o otimizador procure
rapidamente linhas correspondentes. É eficaz
para tabelas grandes e para junções não
ordenadas.
Atividade 1
Qual das seguintes afirmações descreve corretamente o processo de análise da consulta durante a otimização
de consultas no SQL Server?
ADurante a análise da consulta, o otimizador verifica a validade sintática da consulta e a transforma em
uma árvore de consulta. Em seguida, avalia a existência de operadores OR e critérios de junção.
B A análise da consulta envolve a compilação da árvore de consulta compilada pelo otimizador de
consultas.
C A análise da consulta verifica a disponibilidade de índices e seleciona os índices apropriados para uso
na consulta.
D A análise da consulta é a etapa final do processo de otimização, em que o plano de execução é
armazenado permanentemente para futuras consultas.
E Durante a análise da consulta, o otimizador examina os resultados intermediários da consulta em busca
de argumentos de busca, operadores OR e critériosde junção.
A alternativa E está correta.
Durante a etapa de análise da consulta no processo de otimização de consultas no SQL Server, o
otimizador examina os resultados intermediários da consulta em busca de argumentos de busca,
operadores OR e critérios de junção. Argumentos de busca são partes da consulta que restringem o
conjunto de resultados intermediários, permitindo o uso de índices existentes ou outras otimizações. Essa
análise ajuda o otimizador a identificar as partes da consulta que podem ser executadas de maneira mais
eficiente, contribuindo para a geração de um plano de execução otimizado.
Planos de execução
Assista ao vídeo e compreenda a importância dos planos de execução no SQL Server Management Studio
(SSMS) como otimizador de consultas do SQL Server.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Tipos de plano de execução
Os planos de execução no SQL Server Management Studio (SSMS) são representações visuais das etapas que
o otimizador de consultas do SQL Server planeja realizar para executar uma consulta. Eles fornecem
informações detalhadas sobre como o SQL Server pretende buscar, acessar e manipular os dados para
atender à consulta.
A análise dos planos de execução pode ajudar a entender como as consultas são processadas pelo banco de
dados e identificar áreas nas quais a otimização pode ser necessária. Vejamos as duas categorias principais
de plano de execução.
Plano de execução estimado
É gerado pelo otimizador de consultas antes da execução real da consulta. Ele fornece uma estimativa de
como o otimizador planeja processar a consulta com base em informações estatísticas sobre as tabelas,
índices, distribuição de dados e outras métricas. O plano de execução estimado é uma representação gráfica
das operações que o otimizador acredita serem as mais eficientes para atender à consulta.
O plano de execução estimado é útil para avaliar a eficiência potencial de uma consulta sem executá-la
fisicamente no banco de dados. Ele ajuda os desenvolvedores a identificarem áreas nas quais a otimização
pode ser necessária antes de comprometer recursos reais para a execução da consulta. Ele é exibido ao
analisar uma consulta no SQL Server Management Studio antes de executá-la.
Plano de execução real
É gerado após a execução física da consulta no banco de dados. Ele mostra as operações reais executadas
pelo SQL Server para processar a consulta e recuperar os resultados. Isso inclui informações detalhadas sobre
o número de linhas processadas, índices usados, tempo de execução e outras métricas de desempenho reais.
O plano de execução real é útil para entender exatamente como o SQL Server processou a consulta em um
ambiente de produção. Ele permite a identificação de gargalos de desempenho, ajustes de índices ou
estratégias de junção, e fornece informações sobre como as estatísticas podem ter afetado a decisão do
otimizador. O plano de execução real é exibido após a execução da consulta no SQL Server Management
Studio ou pode ser capturado por ferramentas de monitoramento e análise.
Visualizando planos de execução com o SSMS
Para visualizar o plano de execução estimado siga os seguintes passos:
1. Inicie o SQL Server Management Studio em seu computador.
2. Conecte-se ao servidor SQL e ao banco de dados em que deseja analisar a consulta e seu plano de
execução.
3. Escreva a consulta que deseja analisar ou abra uma consulta existente no SQL Server Management Studio.
A partir desse ponto, para exibir o plano de execução estimado:
4. Ative a exibição do plano estimado para isso use um dos seguintes métodos.
4.1. Clique no botão mostrar plano de execução estimado, na barra de ferramentas superior (ícone de um
índice e uma lupa), isso fará com que o plano de execução estimado seja exibido no painel Resultado na parte
inferior do SSMS.
4.2. Clique com o botão direito do mouse na consulta no painel de edição. No menu de contexto, selecione
exibir plano de execução estimado.
4.3. Use o atalho de teclado Ctrl + L para exibir o plano de execução estimado.
Para exibir o plano de execução real:
5. Execute a consulta clicando no botão executar ou pressionando F5.
6. Após executar a consulta:
6.1. No painel resultado, clique na guia plano de execução para visualizar o plano detalhado em formato
gráfico.
6.2. Você também pode clicar com o botão direito do mouse na consulta e selecionar mostrar plano de
execução para abrir uma nova janela com o plano de execução.
Atividade 2
Um plano de execução é definido como uma maneira simples do query optimizer calcular o caminho mais
eficiente para implementar uma requisição representada pelo T-SQL. Nesse contexto, qual é a principal função
dos planos de execução no SQL Server Management Studio (SSMS)?
A Armazenar os resultados de consultas executadas no banco de dados.
B Representar visualmente a estrutura interna do banco de dados.
C Ajudar a identificar áreas onde a otimização pode ser necessária.
D Fornecer informações de autenticação e segurança.
E Criar backups automáticos das consultas realizadas.
A alternativa C está correta.
Os planos de execução no SQL Server Management Studio (SSMS) fornecem informações detalhadas
sobre como o SQL Server planeja e executa uma consulta. A análise dos planos de execução ajuda a
entender como as consultas são processadas pelo banco de dados e a identificar áreas nas quais a
otimização pode ser necessária para melhorar o desempenho. Os planos de execução estimado e real
mostram as operações planejadas e executadas, respectivamente, permitindo uma análise detalhada do
processamento da consulta.
Planos de execução e estatísticas dinâmicas
Assista ao vídeo e compreenda a importância do plano de consulta dinâmica, fornecendo informações em
tempo real sobre o processo de execução da consulta no SQL Server.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
Estatísticas dinâmicas
O SQL Server Management Studio fornece a capacidade de exibir o plano de execução ao vivo de uma
consulta ativa. Esse plano de consulta dinâmica fornece informações em tempo real sobre o processo de
execução da consulta, conforme os controles fluem de um operador de plano de consulta para outro. Exibe o
progresso geral da consulta e as estatísticas de tempo de execução do nível de operador, como o número de
linhas produzido, tempo decorrido, progresso do operador etc. Como esses dados estão disponíveis em
tempo real, sem a necessidade de aguardar a conclusão da consulta, essas estatísticas de execução são
extremamente úteis para depurar problemas de desempenho de consulta.
Para exibir estatísticas de consulta ao vivo no SQL Server Management Studio (SSMS), siga estas etapas:
1. Inicie o SQL Server Management Studio e conecte-se ao servidor SQL Server em que deseja exibir as
estatísticas de consulta ao vivo.
2. Ative a captura de estatísticas de consulta ao vivo:
2.1. No menu superior, clique em consulta.
2.2. Selecione estatísticas de consulta dinâmicas. Isso ativará a captura de estatísticas para a janela de
consulta atual.
3. Execute uma consulta.
4. Visualize as estatísticas de consulta ao vivo: à medida que a consulta é executada, você verá uma nova guia
chamada estatísticas de consulta ao vivo na parte inferior da janela do SSMS na qual:
4.1. É exibido um plano de execução em formato de árvore, que mostra as operações sendo executadas pela
consulta.
4.2. Cada operação é representada por um nó na árvore, e você pode clicar nas setas ao lado dos nós para
expandi-los e ver detalhes adicionais.
4.3. O fluxo da consulta é representado pelas setas conectando os nós na árvore.
4.4. Você verá o número atual de linhas processadas em cada operação, bem como a porcentagem de
conclusão da operação.
5. Interaja com as estatísticas de consulta ao vivo. Enquanto a consulta está em execução, você pode interagir
com as estatísticas de consulta ao vivo:
5.1. Pausar/Continuar: Você pode pausara exibição das estatísticas a qualquer momento, o que pode ser útil
para analisar detalhes específicos.
5.2. Zoom: Você pode usar essa função para ampliar ou reduzir a visualização do plano de execução.
5.3. Filtros: Você pode aplicar filtros para exibir apenas as informações relevantes no plano de execução.
Salvando e comparando planos de execução
Para salvar e comparar planos de execução no SQL Server Management Studio (SSMS), você pode realizar as
seguintes ações.
Salvando um plano de execução
Siga as etapas a seguir:
1
Realize a consulta
Execute a consulta para a qual você deseja salvar o plano de execução.
2 Exiba o plano de execução real
Clique em consulta no menu superior.
Selecione incluir plano de execução real.
Execute novamente a consulta.
3
Salve o plano de execução
Após a execução, você verá uma nova guia chamada plano de execução abaixo dos
resultados da consulta.
Clique com o botão direito do mouse na guia e selecione salvar plano de execução como.
Escolha um local e um nome de arquivo para salvar o plano de execução. O plano será salvo
com a extensão “.sqlplan”.
Comparando planos de execução
Siga as etapas a seguir:
1
Realize as consultas
Execute as duas consultas, cujos planos de execução você deseja comparar.
2
Exiba os planos de execução
Para cada consulta, siga os passos 2 e 3 da seção anterior para exibir e salvar os planos de
execução.
3
Comparação de planos
Com os dois planos de execução salvos, você pode compará-los para identificar diferenças.
No SSMS, clique com o botão direito do mouse em um dos planos de execução e selecione
comparar plano de execução.
Na janela de comparação, selecione o segundo plano de execução a ser comparado.
A janela de comparação exibirá os dois planos de execução lado a lado, destacando as diferenças entre eles.
Você poderá ver as operações, índices, estatísticas e outras informações relacionadas ao plano de execução.
Lembre-se de que a comparação de planos de execução é uma ferramenta útil para identificar mudanças no
desempenho e na otimização das consultas. Isso pode ajudá-lo a entender como ajustes nas consultas ou
índices afetam o plano de execução e o desempenho geral das consultas.
•
•
•
•
•
•
•
•
•
Atividade 3
Para poder executar consultas, o mecanismo de banco de dados do SQL Server deve analisar a instrução para
determinar uma maneira eficiente de acessar os dados necessários e processá-los. Essa análise é tratada por
um componente chamado de otimizador de consulta. A entrada do otimizador de consulta consiste em uma
consulta, o esquema de banco de dados (definições de tabela e de índice) e as estatísticas de banco de
dados. O otimizador de consultas cria um ou mais planos de execução de consulta, às vezes chamados de
planos de consulta ou planos de execução. Qual é o procedimento correto para comparar os planos de
execução de duas consultas no SQL Server Management Studio (SSMS)?
A Utilizar uma ferramenta externa de comparação de arquivos para abrir e comparar os resultados das
consultas.
B Copiar e colar os resultados das consultas em um documento de texto e utilizar a função de
comparação de texto do SSMS.
C Executar as consultas e visualizar os planos de execução lado a lado na guia comparar planos de
execução no SSMS.
D Salvar os planos de execução em arquivos “.sqlplan”, clicar com o botão direito do mouse em cada
arquivo e selecionar "Comparar Plano de Execução" no SSMS.
E Abrir as consultas no SSMS, clicar com o botão direito do mouse em cada consulta e selecionar
comparar planos de execução para exibir os planos lado a lado.
A alternativa D está correta.
Comparar planos de execução é uma atividade importante para entender as diferenças de desempenho e
otimização entre consultas. O SQL Server Management Studio (SSMS) oferece uma maneira fácil de
comparar os planos de execução de duas consultas:
Primeiro, é necessário executar as duas consultas, cujos planos de execução você deseja comparar.
Depois de executar as consultas, você verá os planos de execução gerados para cada consulta.
Para salvar os planos de execução, clique com o botão direito do mouse em cada guia de plano de
execução e selecione salvar plano de execução como... Salve os planos de execução em arquivos
“.sqlplan”.
Com os planos de execução salvos, clique com o botão direito do mouse em um dos arquivos
“.sqlplan” e selecione "comparar plano de execução".
Na janela de comparação, selecione o segundo arquivo “.sqlplan” que deseja comparar.
A janela de comparação exibirá os dois planos de execução lado a lado, destacando as diferenças
entre eles. Você poderá ver as operações, índices, estatísticas e outras informações relacionadas ao
plano de execução.
Dicas de otimização (hints)
1.
2.
3.
4.
5.
6.
Assista ao vídeo e compreenda a importância das dicas de otimização, a fim de obter um melhor desempenho
no SQL Server.
Conteúdo interativo
Acesse a versão digital para assistir ao vídeo.
O que são dicas de otimização (hints)
Na maioria dos casos, o otimizador de consultas escolhe o plano de execução mais rápido. No entanto,
existem algumas situações especiais em que o otimizador, por motivos específicos, não consegue encontrar a
solução ideal. Nesses casos, você deve utilizar dicas de otimização para forçá-lo a usar um plano de execução
particular que pode ter um melhor desempenho.
Existem duas razões pelas quais o otimizador, às vezes, não escolhe o plano de execução mais rápido:
O otimizador de consultas não é perfeito.
O sistema não fornece as informações adequadas ao otimizador.
Confira as dicas de otimização.
Dicas de tabela
O sistema oferece suporte a diversas opções relacionadas a tabelas, que podem ser aplicadas da seguinte
maneira:
INDEX
É usada para especificar um ou mais índices utilizados em uma consulta. Essa dica é especificada na
cláusula FROM da consulta e você pode utilizá-la para forçar o acesso a um índice se o otimizador,
por algum motivo, escolher realizar uma varredura completa na tabela para uma consulta.
NOEXPAND
Especifica que qualquer exibição indexada não é expandida para acessar tabelas subjacentes quando
o otimizador de consulta processa a consulta.
FORCESEEK
Força o otimizador a usar apenas uma operação de busca de índice como caminho de acesso aos
dados na tabela (ou exibição) referenciada na consulta. Você pode usar essa dica de tabela para
substituir o plano padrão escolhido pelo otimizador de consulta, a fim de evitar problemas de
desempenho causados por um plano de consulta ineficiente.
Dicas de junção
As dicas de junção instruem o otimizador de consultas sobre como as operações de junção em uma consulta
devem ser executadas. Elas forçam o otimizador a unir tabelas na ordem em que são especificadas na
•
•
cláusula FROM da instrução SELECT ou a usar as técnicas de processamento de junção explicitamente
especificadas na declaração.
O mecanismo de banco de dados oferece suporte a várias opções para otimizar a junção de tabelas em
consultas. Veja quais são!
FORCE ORDER
Força o otimizador a unir tabelas na ordem em que são especificadas em uma consulta.
LOOP
Força o otimizador a usar a técnica de loop aninhado.
HASH
Força o otimizador a usar a técnica de hash join.
MERGE
Força o otimizador a usar a técnica de merge join.
Essas três últimas dicas de junção só podem ser usadas quando a operação de junção está de acordo com o
padrão SQL, ou seja, quando a junção é indicada explicitamente com a palavra-chave JOIN na cláusula FROM
de uma instrução SELECT.
Dicas de consulta
Existem várias dicas de consulta que são usadas para diferentes propósitos. Conheça algumas!
1
FAST n
Especifica que a consulta é otimizada para recuperação rápida das primeiras n linhas. Após as
primeiras n linhas serem retornadas, a consulta continua a execução e produz o conjunto completo
de resultados.
2
OPTIMIZE FOR
Força o otimizador de consulta a usar um valor específico para uma variável local quando a consulta
é compilada e otimizada.