Comandos de administração e tuning de banco de dados no SQL Server

Prévia do material em texto

DESCRIÇÃO
As melhores práticas para criação de objetos de banco de dados e para realização de tuning
da carga de trabalho submetida ao SQL Server, através de estatísticas e índices.
PROPÓSITO
Aprender a utilizar os comandos de criação de tabelas, visões, procedimentos armazenados,
gatilhos, funções, índices e identificar as funções que cada objeto desempenha no banco de
dados. Observar como as consultas e atualizações utilizam os objetos do banco de dados e
aprender a utilizar técnicas de tuning para alterar o comportamento da execução dessas
operações, com o objetivo de melhorar o desempenho das solicitações emitidas por usuários e
aplicações.
PREPARAÇÃO
Antes de iniciar o conteúdo deste tema, é necessário ter uma instalação de SQL Server 2017
Express Edition ou superior, e a ferramenta de trabalho SQL Server Management Studio, além
dos scripts disponíveis para download nos arquivos abaixo, que serão utilizados no decorrer do
tema.
Criacao_tabelas.sql
Criacao_objetos.sql
Tuning_estatisticas_indices.sql
Caso não seja possível utilizar uma máquina própria para instalar o SQL Server, é possível a
utilização de provedores de nuvem, como Azure ou AWS (Amazon Web Services), para
implantar uma instância do banco de dados na camada free tier desses provedores, que
permitem o uso do produto sem custos por um período predeterminado.
OBJETIVOS
MÓDULO 1
Reconhecer boas práticas para a criação de tabelas no SQL Server
MÓDULO 2
Executar comandos de criação de objetos no SQL Server, como views, stored procedures,
triggers, functions e índices
MÓDULO 3
Aplicar técnicas de tuning com estatísticas e índices
https://stecine.azureedge.net/repositorio/02146/docs/Criacao_tabelas.zip
https://stecine.azureedge.net/repositorio/02146/docs/Criacao_objetos.zip
https://stecine.azureedge.net/repositorio/02146/docs/Tuning_estatisticas_indices.zip
INTRODUÇÃO
Uma parte fundamental da utilização do SGBD (Sistema Gerenciador de Banco de Dados) SQL
Server para desenvolvimento de aplicações é a criação e manutenção dos objetos que
compõem o banco de dados. Nas empresas, é comum encontrar profissionais concentrados
em Administração de Dados, responsáveis por criar e manter as estruturas que suportarão o
armazenamento e o processamento dos dados. Entre essas estruturas, estão as tabelas, suas
propriedades, colunas e restrições. No primeiro módulo deste tema, aprenderemos a criá-las, a
especificar suas propriedades e a aplicar as melhores práticas para sua utilização.
No segundo módulo, trataremos de outros objetos muito utilizados em bancos de dados SQL
Server, como views e stored procedures. Identificaremos as funcionalidades que são
implementadas por cada um deles, utilizando exemplos reais de implementação.
No terceiro e último módulo, construiremos conhecimento sobre tuning no SQL Server,
atividade desempenhada principalmente por administradores de banco de dados (DBA ‒
Database Administrator). O tuning, ou sintonia fina, permite ao DBA alterar como uma consulta
é executada pelo otimizador do banco de dados e melhorar o seu desempenho. Para isso,
vamos nos apoiar no uso de estatísticas e índices.
As capturas de telas que aparecem nas imagens ao longo deste conteúdo são resultantes de
comandos elaborados pelo autor no SQL Server Management Studio.
MÓDULO 1
 Reconhecer boas práticas para a criação de tabelas no SQL Server
CRIAÇÃO DE TABELAS
Quando uma tabela é criada, alguns metadados devem ser especificados, como o esquema, o
nome da tabela, suas colunas e propriedades.
Se o esquema não for explicitamente informado, será utilizado o esquema default do usuário
que está criando a tabela.
Desenvolvedores de sistemas e administradores de dados garantem que esses metadados
sejam definidos e validados para que a tabela possa ser criada corretamente.
No exemplo a seguir, estamos conectados a uma instância SQL Server 2017 através do SQL
Server Management Studio (SSMS).

Inicialmente, por meio do comando USE, alteramos o banco de dados corrente.

O primeiro comando executado, CREATE SCHEMA, cria o esquema CRM.
Vamos utilizar esse esquema para criar todos os objetos, em todos os módulos. No comando
CREATE TABLE mostrado a seguir, a tabela Pessoa é criada, no esquema CRM, de acordo
com a especificação de suas colunas e propriedades.
 
 Criação de tabela CRM Pessoa. 
Captura de tela do software SQL Server Management Studio (SSMS).
Para cada coluna, devemos definir o tipo do dado que será armazenado.
1
No exemplo, id_pessoa tem o tipo int, de forma que essa coluna armazena números inteiros.
A coluna tipo_pessoa tem o tipo varchar e quantidade de caracteres 2, de forma que ela
armazena até 2 caracteres (no caso, “PF” ou “PJ”).
2
3
Nome_pessoa é do tipo varchar e pode armazenar até 50 caracteres e data_nascimento é do
tipo date, que armazena uma data (no formato padrão do banco de dados).
As colunas nome_pai e nome_mae são do tipo varchar e armazenam até 50 caracteres, e a
coluna renda_mensal é do tipo decimal com até 18 dígitos e precisão de 2 casas decimais.
4
5
Finalmente, a coluna observacao é do tipo varchar e armazena até 300 caracteres.
 COMENTÁRIO
Note que todas as colunas são nomeadas de acordo com o dado que armazenam. Por
exemplo, nome_pessoa é naturalmente o nome da pessoa. Já data_nascimento é a data de
nascimento da pessoa. É uma boa prática especificar nomes de colunas de forma que os
dados ali contidos possam ser identificados rapidamente.
Mais na frente, quando você estiver trabalhando com consultas em bancos de dados que
possuem, tipicamente, dezenas ou centenas de tabelas, é importante saber quais dados estão
sendo solicitados apenas com a leitura de um comando SELECT.
No SQL Server, o tamanho máximo de nomes de colunas é 128 caracteres e, portanto, não há
necessidade de utilizar abreviações.
Além disso, é importante determinar quais são as colunas obrigatórias. Para isso, utiliza-se a
cláusula not null. Todas as colunas que possuem essa cláusula devem ser informadas na
inserção de uma linha na tabela; caso contrário, um erro será gerado pelo SGBD. Aquelas
colunas que possuem a especificação null (ou não possuem tal especificação) são
consideradas opcionais. No exemplo, todas as colunas são obrigatórias, exceto nome_pai,
nome_mae, renda_mensal e observacao.
USO DE RESTRIÇÕES
Informação essencial para a criação de uma tabela é a sua chave primária.
É essa chave que vai identificar a linha da tabela, assumindo função primordial tanto na
implementação do modelo relacional, quanto na implementação das estruturas de dados e seu
uso pelo otimizador do SGBD, conforme veremos no módulo de Tuning.
Para a tabela CRM.Pessoa, conforme mostrado na imagem anterior, a PRIMARY KEY está
definida na coluna id_pessoa e seu valor será incrementado automaticamente a cada inserção.
De acordo com a cláusula IDENTITY(1,1), id_pessoa vai começar com o valor 1 e será
incrementado de 1 em 1. Assim, toda vez que uma linha for inserida na tabela, o valor de
id_pessoa será igual ao último valor inserido na tabela, mais 1.
Em seguida, vamos criar uma tabela para armazenar os documentos de uma pessoa. O
comando de criação da tabela deve especificar, além das colunas e seus tipos, as chaves da
tabela por meio da cláusula CONSTRAINT, que define uma restrição. Além da chave primária,
temos uma chave estrangeira que implementa o relacionamento entre o documento e a
pessoa. É isso que faz a restrição FOREIGN KEY: implementa chave estrangeira
determinando que o valor da coluna id_pessoa da tabela CRM.Pessoa_Documento deve ser
um valor existente na coluna id_pessoa da tabela CRM.Pessoa.
Na imagem, você pode ver também o uso de ALTER TABLE. É comum que alterações em
tabelas existentes sejam necessárias no decorrer da vida útil de um esquema.
 COMENTÁRIO
Novas colunas e novas restrições costumam ser necessárias em manutenções evolutivas.
Na construção dada como exemplo, veja como é um ALTER TABLE para adicionar a constraint
da chaveestrangeira correspondente à coluna id_pessoa.
 
 Criação das restrições da tabela CRM.Pessoa_documento. 
Captura de tela do software SQL Server Management Studio (SSMS).
Veja nos endereços a seguir:
Uma referência completa sobre o comando CREATE TABLE.
https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-table-transact-sql?view=sql-server-
ver15,
Uma referência completa sobre o comando ALTER TABLE.
javascript:void(0);
https://docs.microsoft.com/pt-br/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-
ver15
ATIVIDADE PRÁTICA
Considerando que as tabelas mostradas anteriormente estão criadas no seu banco de dados,
crie a tabela CRM.Pessoa_Endereco, com as seguintes características:
A chave primária da tabela deve ser gerada automaticamente por meio de incremento de
1 sobre o último valor inserido na tabela;
Deve existir uma chave estrangeira que estabeleça o relacionamento com a tabela
CRM.Pessoa;
As colunas das tabelas devem ser tipo_endereco (somente os valores Residencial,
Comercial ou Outro), tipo_logradouro, logradouro, número, complemento, bairro, cidade,
estado e CEP.
O valor da coluna [complemento] deve ser opcional. Todos os outros devem ser
obrigatórios.
SOLUÇÃO
 
 Solução da Atividade Prática, elaborada por Antony Seabra de Medeiros. 
Captura de tela do software SQL Server Management Studio (SSMS).
javascript:void(0);
INSERÇÃO E RECUPERAÇÃO DE
INFORMAÇÕES
Com as tabelas já criadas no banco de dados, é possível inserir dados por meio do comando
INSERT e recuperá-los através do comando SELECT.
Com respeito aos tipos de dados e às restrições impostas para cada coluna da tabela, o
comando INSERT informa os dados da linha a ser inserida.
 
 Inserção de dados nas tabelas CRM.Pessoa e CRM.Pessoa_documento. 
Captura de tela do software SQL Server Management Studio (SSMS).
 ATENÇÃO
Observe que, na inserção da linha na tabela Pessoa, os valores para as colunas nome_pai,
nome_mae e observacao não foram informados. O INSERT é executado com sucesso porque
essas não são colunas obrigatórias.
Ainda, na inserção da linha na tabela CRM.Pessoa_Documento, a data é informada de acordo
com o formato padrão do banco de dados. No exemplo, está sendo utilizado o formato norte-
americano [YYYY-MM-DD], mas você pode utilizar o formato mais adequado para sua
aplicação por meio da função CONVERT.
Veja no endereço a seguir todos os estilos de data possíveis e como utilizar a função
CONVERT:
https://docs.microsoft.com/pt-br/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-
server-ver15
A recuperação dos dados é realizada por meio do comando SELECT. Na imagem anterior,
estão recuperadas todas as colunas [*] da tabela CRM.Pessoa e da tabela
CRM.Pessoa_Documento.
 DICA
Quando for necessário recuperar dados de duas ou mais tabelas, utilizamos o operador JOIN
informando como as tabelas se relacionam.
No exemplo mostrado na próxima imagem, para recuperar o nome da pessoa, sua data de
nascimento e os documentos da pessoa, com o tipo e data de expedição, utilizamos o operador
JOIN entre as tabelas CRM.Pessoa e CRM.Pessoa_Documento, especificando como elas se
relacionam.
 
 Seleção de dados nas tabelas CRM.Pessoa e CRM.Pessoa_documento. 
Captura de tela do software SQL Server Management Studio (SSMS).
A cláusula ON informa que o relacionamento entre as duas tabelas ocorre quando o valor de
id_pessoa em CRM.Pessoa é igual ao valor de id_pessoa em CRM.Pessoa_Documento.
Muito embora tenhamos definido uma restrição do tipo chave estrangeira na definição da tabela
CRM.Pessoa_Documento, não é essa restrição que torna o relacionamento possível. Ele
javascript:void(0);
poderia ser feito dessa mesma forma, mesmo que não tivéssemos definido a chave
estrangeira.
Entretanto, é uma boa prática que todos os relacionamentos entre as tabelas sejam definidos
por meio de chaves estrangeiras. Essa boa prática garante que os valores da coluna id_pessoa
em CRM.Pessoa_Documento existam, de fato, na tabela CRM.Pessoa.
USO DE IMAGENS EM TABELAS
Uma necessidade bastante comum é o armazenamento de imagens em bancos de dados. No
SQL Server, podemos lançar mão de colunas VARBINARY(MAX) e, opcionalmente, a
especificação FILESTREAM.
A diferença básica entre elas é que:
A imagem é armazenada como um BLOB (Binary Large Object) dentro do banco de dados
quando usamos somente VARBINARY(MAX)
E a imagem é armazenada fora do banco de dados quando usamos FILESTREAM, como um
arquivo do sistema operacional.
Para exemplificar o uso de VARBINARY(MAX), vamos criar uma tabela para armazenar as
imagens associadas aos documentos das pessoas. A tabela vai se chamar
CRM.Pessoa_Imagem e vai conter uma coluna que aponta para CRM.Pessoa_Documento, e
outra coluna para armazenar a imagem propriamente dita.
Veja como fica o comando para criação da tabela na imagem a seguir:
 
 Criação da tabela CRM.Pessoa_Imagem. 
Captura de tela do software SQL Server Management Studio (SSMS).
Para inserir uma linha nessa tabela, vamos utilizar a função OPENROWSET passando o
caminho da imagem que queremos inserir na coluna, além do índice do documento.
 COMENTÁRIO
Observe que cuidamos para que a coluna fosse não obrigatória, para o caso de imagens que
não estão associadas a documentos.
Veja a seguir como fica a inserção da linha e, logo após, um SELECT com JOIN nas tabelas
CRM.Pessoa, CRM.Pessoa_Documento e CRM.Pessoa_Imagem, para recuperar as imagens
associadas a documentos.
 
 SELECT recuperando a coluna imagem. 
Captura de tela do software SQL Server Management Studio (SSMS).
O retorno mostra o valor hexadecimal correspondente ao tipo BLOB armazenado na coluna.
Esse valor é uma representação da imagem que, se convertido apropriadamente, gera a
imagem original.
Veja mais detalhes sobre VARBINARY(MAX) e outros tipos de dados avançados no endereço a
seguir:
https://docs.microsoft. com/pt-br/sql/connect/jdbc/using -advanced-data-types ?view=sql-server-
ver15
Da mesma forma que importamos um arquivo de imagem para dentro do banco de dados,
podemos exportá-lo de volta para um arquivo imagem, permitindo que uma aplicação possa
exibi-la. Essa exportação da imagem para o arquivo pode ser feita pelo utilitário BCP do SQL
Server.
Na construção a seguir, a string @sql é criada com o comando BCP, recebendo o valor
hexadecimal que retorna da coluna imagem e escrevendo no arquivo especificado em
QUERYOUT.
javascript:void(0);
O arquivo .fmt é o arquivo auxiliar de formato gerado automaticamente em uma exportação do
BCP e o parâmetro –S especifica o servidor onde ele será executado; no exemplo,
@@SERVERNAME especifica o próprio servidor onde estamos conectados com o SSMS.
O último passo é executar o conteúdo de @sql através da procedure do SQL Server,
xp_cmdshell.
Após essa execução, a imagem está pronta no caminho especificado.
 
 Exportação da imagem com BCP. 
Captura de tela do software SQL Server Management Studio (SSMS).
As imagens em uma coluna do tipo VARBINARY(MAX) podem ser armazenadas fora do banco
de dados, por meio da especificação FILESTREAM. Você pode realizar as mesmas operações
que fizemos até aqui; a diferença está na criação da tabela. Veja a seguir:
 
 Criação da tabela utilizando FILESTREAM. 
Captura de tela do software SQL Server Management Studio (SSMS).
Observe a especificação FILESTREAM logo após o tipo da coluna VARBINARY(MAX). Quando
a tabela é criada com esse comando, o SQL Server verifica se existe um FILEGROUP no
banco de dados criado especialmente para o armazenamento de filestreams. Caso exista,
todas as imagens são armazenadas no caminho correspondente ao FILEGROUP.
Você pode ver no script do módulo uma construção preparada para criar um banco de dados
habilitado para FILESTREAM e mais detalhes sobre o uso de filestreams no SQL Server no
endereço:
https://docs.microsoft. com/pt-br/sql/relational-databases /blob/filestream-sql -server?view=sql-
server-ver15
CRIAÇÃO DE TABELAS NO SQL SERVERNo vídeo a seguir, demonstramos a criação de tabelas no SQL Server utilizando um ambiente
real e captura dos procedimentos.
VERIFICANDO O APRENDIZADO
javascript:void(0);
1. A RESPEITO DOS CONCEITOS E MELHORES PRÁTICAS PARA
CRIAÇÃO DE TABELAS NO SQL SERVER, ASSINALE A AFIRMATIVA
FALSA.
A) É uma boa prática nomear as colunas de uma tabela de acordo com o dado que ali será
armazenado. Isso vai permitir identificar rapidamente qual informação está sendo solicitada em
uma consulta, por exemplo.
B) A criação de tabelas no SQL Server tem papel fundamental tanto na implantação de um
modelo relacional quanto na execução das solicitações submetidas pelos usuários ao banco de
dados.
C) O uso de restrições na construção de tabelas é uma boa prática na criação e manutenção
das tabelas do banco de dados. Chaves primárias e chaves estrangeiras são duas das
principais restrições utilizadas.
D) Uma constraint do tipo CHECK permite enumerar os valores que uma dada coluna pode
conter.
E) Entre os metadados que devem ser informados na criação de uma tabela, estão o esquema
e o nome da tabela. Caso um deles não seja informado, será gerado um erro pelo SGBD.
2. CONSIDERE QUE AS SEGUINTES ALTERAÇÕES FORAM REALIZADAS
NAS ESTRUTURAS DAS TABELAS CRM.PESSOA E
CRM.PESSOA_DOCUMENTO: 
 
• COLUNA [ESTADO CIVIL] ADICIONADA À TABELA CRM.PESSOA, COM
OS VALORES POSSÍVEIS RESTRITOS AOS ESTADOS [SOLTEIRO,
CASADO, SEPARADO, DIVORCIADO, VIÚVO]. 
• TABELA DE CRM.ORGAOS CRIADA NO BANCO DE DADOS. 
• COLUNA [ORGAO_EXPEDIDOR] ADICIONADA À TABELA
CRM.PESSOA_DOCUMENTO. SEUS VALORES POSSÍVEIS SÃO
SOMENTE AQUELES EXISTENTES NA TABELA CRM.ORGAOS. 
 
ASSINALE A AFIRMATIVA INCORRETA EM RELAÇÃO ÀS ALTERAÇÕES
NO BANCO DE DADOS.
A) Foi criada uma chave estrangeira na coluna [Orgao_Expedidor] da tabela
CRM.Pessoa_Documento, apontando para a tabela CRM.Orgaos.
B) A tabela CRM.Orgaos foi criada no banco de dados, contendo uma coluna para armazenar
o órgão expedidor.
C) Foi utilizada uma restrição do tipo CHECK para assegurar que somente os estados civis
enumerados possam ser utilizados.
D) A chave primária da tabela CRM.Orgaos deve ser um código numérico que identifique
unicamente o órgão expedidor, podendo ser gerado automaticamente pela função IDENTITY.
E) A lista de estados civis poderia ser inserida em uma tabela CRM.Estado_Civil e a coluna
[Estado_Civil] poderia ter uma chave estrangeira apontando para esta tabela.
GABARITO
1. A respeito dos conceitos e melhores práticas para criação de tabelas no SQL Server,
assinale a afirmativa falsa.
A alternativa "E " está correta.
 
O esquema default, do usuário que está criando a tabela, será utilizado se o esquema não tiver
sido explicitamente informado.
Para que a tabela seja criada de forma correta, os desenvolvedores de sistemas e
administradores de dados procuram garantir que esses metadados sejam definidos e
validados.
2. Considere que as seguintes alterações foram realizadas nas estruturas das tabelas
CRM.Pessoa e CRM.Pessoa_Documento: 
 
• Coluna [Estado Civil] adicionada à tabela CRM.Pessoa, com os valores possíveis
restritos aos estados [Solteiro, Casado, Separado, Divorciado, Viúvo]. 
• Tabela de CRM.Orgaos criada no banco de dados. 
• Coluna [Orgao_Expedidor] adicionada à tabela CRM.Pessoa_Documento. Seus valores
possíveis são somente aqueles existentes na tabela CRM.Orgaos. 
 
Assinale a afirmativa INCORRETA em relação às alterações no banco de dados.
A alternativa "D " está correta.
 
A chave primária da tabela CRM.Orgaos pode ser o próprio [Orgao_Expedidor]. Não é
necessário que a chave primária seja um código numérico.
MÓDULO 2
 Executar comandos de criação de objetos no SQL Server, como views, stored
procedures, triggers, functions e índices
CRIAÇÃO DE VIEWS
As views, ou visões, permitem que uma consulta SQL seja apresentada como uma tabela do
banco de dados, de modo que desenvolvedores e usuários possam utilizá-las sem conhecer os
detalhes que tornam a consulta possível.
No módulo anterior, criamos uma consulta que faz um JOIN entre as tabelas CRM.Pessoa e
CRM.Pessoa_Documento para recuperar os documentos de uma pessoa. Será que toda vez
que uma consulta desse tipo for necessária, será preciso escrevê-la novamente, conhecendo
as tabelas, suas colunas e as colunas de correlação entre as tabelas?
Veja o exemplo a seguir:
 
 Criação da view CRM.vw_pessoa_documento. 
Captura de tela do software SQL Server Management Studio (SSMS).
Observe que podemos usar a visão CRM.Pessoa_documento sem conhecer os detalhes da
sua implementação; apenas utilizamos suas colunas para, adicionalmente, filtrar os resultados
e/ou ordená-los. Outra consequência relevante para a administração do banco de dados é que
o acesso de leitura para um usuário pode ser concedido na view, sem necessariamente
conceder o acesso às tabelas. Isso faz com que o usuário possa executar o SELECT na view,
mas não possa executar o SELECT nas tabelas que a view utiliza.
Em bancos de dados que possuem dezenas ou centenas de tabelas, as visões desempenham
papel fundamental para desenvolvedores e usuários. Tornam as solicitações mais simples,
enquanto promovem boas práticas de administração e segurança.
Para ver mais detalhes sobre a criação de visões no SQL Server, acesse o endereço:
https://docs.microsoft.com/ pt-br/sql/t-sql/statements/ create-view-transact-sql? view=sql-server-
ver15
ATIVIDADE PRÁTICA
Considerando que as tabelas Pessoa, Pessoa_Documento e Pessoa_Endereco estão criadas
em seu banco de dados, crie uma visão com as seguintes características:
javascript:void(0);
Retorna todos os dados de Pessoa;
Permite filtrar pelo tipo do documento;
Retorna somente o endereço residencial da pessoa.
SOLUÇÃO
 
 Solução da Atividade Prática, elaborada por Antony Seabra de Medeiros. 
Captura de tela do software SQL Server Management Studio (SSMS).
CRIAÇÃO DE STORED PROCEDURES
As stored procedures, ou procedimentos armazenados, permitem que um conjunto de tarefas
seja implementado (em linguagem SQL) e armazenado no banco de dados, criando um
procedimento que pode ser executado por meio de uma única chamada.
 EXEMPLO
Exemplos clássicos são os procedimentos de inserção, atualização e deleção de linhas de uma
tabela. Esses procedimentos, tipicamente, fazem críticas dos dados a serem inseridos ou
atualizados, podendo ainda fazer transformações ou cálculos para gerar novos dados.
Veja a seguir uma possível implementação do procedimento de inserção na tabela
CRM.Pessoa.
 
 Criação da stored procedure CRM.proc_insere_pessoa. 
Captura de tela do software SQL Server Management Studio (SSMS).
O procedimento determina quais parâmetros de entrada são esperados, precedidos do
caractere @, e também o tipo de dado esperado para cada um deles.
Caso o procedimento seja chamado com um tipo não correspondente, o próprio SGBD gera um
erro.
Caso todos os parâmetros atendam aos requisitos especificados, o procedimento pode ser
executado.
Seu corpo, codificado entre o BEGIN e o END, faz, inicialmente, uma crítica em relação aos
parâmetros @tipo_pessoa, @nome_pessoa e @data_nascimento, verificando se algum deles
contém o valor nulo e, em caso positivo, emite uma mensagem de erro (comando
RAISERROR).
Na sequência, verifica se existe na tabela CRM.Pessoa uma linha que contenha os valores de
@nome_pessoa e @data_nascimento.
Em caso negativo, insere a linha na tabela; em caso positivo, retorna uma mensagem
informando “Pessoa já cadastrada. ”.
 
 Execução da stored procedure CRM.proc_insere_pessoa. 
Captura de tela do software SQL Server Management Studio (SSMS).
A chamada ao procedimento armazenado é realizada com o comando EXECUTE (ou EXEC),
informando o nome do procedimento e os parâmetros correspondentes. Conforme pode ser
constatado na imagem anterior, após a execução do procedimento, a linha correspondente ao
id_pessoa = 2 é inserido na tabela CRM.Pessoa.
Uma stored procedure responsável pela deleção de uma linha deve verificar,por exemplo, se
realmente existe uma linha na tabela com os valores passados como parâmetros. Caso exista,
prossegue com o DELETE na tabela. Veja um exemplo a seguir, considerando que somente a
chave primária id_pessoa será utilizada como parâmetro.
 
 Criação e execução da stored procedure CRM.proc_deleta_pessoa. 
Captura de tela do software SQL Server Management Studio (SSMS).
Uma execução do procedimento armazenado, passando como parâmetro o valor 2, faz com
que seja verificada a existência da linha correspondente a id_pessoa = 2. Como a linha, de
fato, existe, será executado o comando DELETE na tabela CRM.Pessoa, com a cláusula
WHERE definindo o valor da chave primária.
O procedimento para atualização de linhas da tabela, por sua vez, precisa checar, além dos
parâmetros obrigatórios, a existência da linha correspondente a id_pessoa. Caso exista, será
executado o comando UPDATE na tabela CRM.Pessoa, com a cláusula WHERE definindo o
valor da chave primária. Veja na imagem a seguir:
 
 Criação da stored procedure CRM.proc_atualiza_pessoa. 
Captura de tela do software SQL Server Management Studio (SSMS).
Caso o SELECT 1 não retorne nenhuma linha, então o UPDATE não é realizado e a
mensagem 'Pessoa não cadastrada.' é retornada.
 COMENTÁRIO
Você deve ter observado, em todas as procedures que criamos até aqui, o uso desse SELECT
1 para verificar a existência de uma determinada linha. Essa é uma boa prática quando
queremos apenas verificar se uma ou mais linhas existem, sob determinadas condições.
Nesses casos, não queremos recuperar qualquer informação, apenas verificar que a linha
existe. Assim, não faz sentido enumerar colunas a serem recuperadas. Caso a linha exista, o
valor 1 será retornado e o EXISTS será avaliado como true. Caso contrário, nada será
retornado e o EXISTS será avaliado como false.
Veja mais detalhes sobre stored procedures e seus usos no endereço:
https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-procedure-transact-sql?view=sql-
server-ver15
CRIAÇÃO DE TRIGGERS
Os triggers, ou gatilhos, permitem que uma tarefa seja disparada na ocorrência de um evento,
como uma inserção, deleção ou atualização.
Essa tarefa pode ser desde um pequeno código SQL informado na própria definição do trigger
até a execução de uma stored procedure que codifica diversas tarefas adicionais.
De fato, é bastante comum o uso de gatilhos para automatizar tarefas que devem ser
executadas antes ou depois dos principais eventos de manipulação de dados em tabelas,
sendo que no SQL Server só é possível criar gatilhos AFTER (ou FOR) e INSTEAD OF; não é
possível criar um gatilho do tipo BEFORE, muito embora seja possível simular a sua execução.
 COMENTÁRIO
Entre as tarefas que podem ser implementadas por meio de gatilhos, podemos encontrar
auditoria de dados, integração entre sistemas e monitoração da atualização de dados.
Em relação à auditoria de dados, alguns sistemas precisam saber sobre as datas em que as
linhas são criadas e/ou atualizadas nas tabelas. Para implementar uma funcionalidade desse
tipo, pode-se utilizar triggers. Logo após a inserção ou atualização de um registro, um código
SQL é executado para gravar a data e hora do sistema na coluna data_ultima_atualizacao da
tabela correspondente. Dessa forma, toda vez que um INSERT ou UPDATE na tabela forem
executados, o SGBD atualiza essa coluna com a data e hora atuais.
Para implementar o gatilho em nossa tabela CRM.Pessoa, vamos criar primeiro a coluna
data_ultima_atualizacao com o tipo datetime.
Em seguida, vamos criar o trigger na tabela, conforme a imagem a seguir:
javascript:void(0);
 
 Criação do trigger CRM.trg_Pessoa_data_atualizacao. 
Captura de tela do software SQL Server Management Studio (SSMS).
A implementação do trigger conta com uma tabela especial, mantida pelo próprio SGBD,
chamada Inserted.
Inserted é uma tabela virtual e contém a imagem da linha inserida ou atualizada, com as
mesmas colunas da tabela original.
É por meio dela que recuperamos o valor de id_pessoa, para que o UPDATE da coluna
data_ultima_atualizacao possa ser executado na linha correta, ou seja, na linha que acabou de
ser atualizada. Executando-se a stored procedure para atualizar uma linha da tabela, o gatilho
é disparado e o código correspondente, executado.
Veja, a seguir, a imagem final da linha na tabela CRM.Pessoa.
 
 Teste de execução do trigger CRM.trg_Pessoa_data_atualizacao. 
Captura de tela do software SQL Server Management Studio (SSMS).
Existe também a tabela virtual Deleted, que contém a imagem da linha antes de ser excluída
por um comando DELETE.
 EXEMPLO
Você pode utilizar a tabela Deleted em um trigger para criar uma rotina de archive dos seus
registros.
Muitos sistemas implementam uma funcionalidade desse tipo, pois devem armazenar os
registros apagados por um determinado período para fins de auditoria. O primeiro passo é criar
uma tabela idêntica à original.
Na imagem a seguir, você pode ver o CREATE TABLE da tabela CRM.Pessoa_archive. É nela
que serão gravadas as linhas removidas da tabela CRM.Pessoa.
 
 Criação da tabela CRM.Pessoa_archive. 
Captura de tela do software SQL Server Management Studio (SSMS).
O segundo passo é criar o trigger de delete na tabela CRM.Pessoa, pois queremos criar um
registro na tabela CRM.Pessoa_archive quando houver uma deleção na tabela CRM.Pessoa.
Em outras palavras, queremos copiar o registro da CRM.Pessoa para a tabela
CRM.Pessoa_archive quando esse registro for excluído. Sua implementação utiliza uma
construção INSERT SELECT. Com ela, você pode inserir uma linha em tabela utilizando linhas
de outra. É exatamente o que queremos: inserir uma linha na tabela CRM.Pessoa_archive,
utilizando os dados da linha que foi removida da tabela CRM.Pessoa. Esses dados estão na
tabela Deleted. Veja a seguir o código.
 
 Criação do trigger CRM.trg_Pessoa_deleta. 
Captura de tela do software SQL Server Management Studio (SSMS).
O gatilho trg_Pessoa_deleta é disparado após o delete na tabela CRM.Pessoa e executa um
INSERT na tabela de archive utilizando todas as colunas da tabela Deleted.
Para testá-lo, executamos a stored procedure CRM.proc_deleta_pessoa, passando como
parâmetro o valor 2.
Isso faz com que a linha com id_pessoa=2 seja deletada da tabela CRM.Pessoa e, logo após,
inserida na tabela CRM.Pessoa_archive.
Veja o SELECT nas duas tabelas logo após a execução do delete:
 
 Teste de execução do trigger CRM.trg_Pessoa_deleta. 
Captura de tela do software SQL Server Management Studio (SSMS).
Como esperávamos, a linha removida da tabela original foi inserida na tabela de archive,
preservando os dados para consultas sob demanda. Muitos sistemas utilizam essa mesma
abordagem de archive para remover da tabela principal dados muito antigos, salvando-os em
uma outra tabela, de onde possam ser consultados caso haja necessidade.
Veja mais detalhes sobre triggers e seus usos no endereço:
https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-
ver15
javascript:void(0);
CRIAÇÃO DE FUNCTIONS
As functions, ou funções, assim como as stored procedures, permitem que um conjunto de
tarefas seja implementado (em linguagem SQL) e armazenado no banco de dados.
A principal diferença entre as functions e as stored procedures é que a função deve,
necessariamente, retornar um valor.
Outra característica importante das funções no SQL Server é que elas não podem alterar
dados em tabelas, ou seja, não são permitidos INSERTs, UPDATEs ou DELETEs.
Vamos ver o exemplo de uma função que, dada uma data de nascimento, retorna o valor da
idade correspondente.
 
 Criação da função CRM.fn_idade. 
Captura de tela do software SQL Server Management Studio (SSMS).
A função recebe uma data como parâmetro e retorna um valor inteiro. Essas são as duas
primeiras informações que podemos extrair ao ler o cabeçalho de definição da função. A
cláusula RETURNS especifica que tipo de dadoa função retorna.
No SQL Server, o retorno de uma função pode ser um valor escalar ou uma tabela.
A função com valor escalar retorna um único valor e a função com valor de tabela retorna um
conjunto de linhas. No exemplo anterior, vamos retornar um único valor inteiro, que é a idade
correspondente à data de nascimento passada como parâmetro.
Para realizar o cálculo, o código utiliza a função DATEDIFF para calcular a diferença entre a
data de nascimento e a data atual do sistema, em anos. É exatamente esse o valor atribuído à
variável @idade, que é o valor de retorno da função.
Veja o seu uso na imagem a seguir:
 
 Utilização da função CRM.fn_idade. 
Captura de tela do software SQL Server Management Studio (SSMS).
A função pode ser utilizada diretamente no SELECT. Na imagem anterior, você pode ver que
recuperamos nome_pessoa e data_nascimento de CRM.Pessoa. Passando o valor de
data_nascimento para a função CRM.fn_idade no SELECT, podemos recuperar a idade
correspondente de cada pessoa selecionada.
Veja a referência de CREATE FUNCTION no endereço:
https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-function-transact-sql?view=sql-
server-ver15
CRIAÇÃO DE ÍNDICES
Quando uma consulta SQL é executada no banco de dados, o SGBD pode percorrer a tabela
inteira para recuperar os dados solicitados. Vamos rever o último SELECT executado no tópico
anterior:
SELECT nome_pessoa, data_nascimento, CRM.fn_idade(data_nascimento) FROM
CRM.Pessoa
Para executar essa consulta, o SGBD percorre todas as linhas da tabela CRM.Pessoa, uma a
uma. Para cada uma delas, recupera as colunas solicitadas e invoca a função CRM.fn_idade
para calcular a idade da pessoa. No final, mostra todas as linhas da tabela e as colunas
solicitadas.
Agora, veja este outro exemplo a seguir:
SELECT nome_pessoa, data_nascimento, CRM.fn_idade(data_nascimento) FROM
CRM.Pessoa WHERE data_nascimento >= '2020/01/01'
javascript:void(0);
Para executar essa consulta, o SGBD percorre todas as linhas da tabela CRM.Pessoa, uma a
uma.
Para cada uma delas, verifica se o valor de data_nascimento é maior ou igual a '2020/01/01'.
Em caso positivo, recupera as colunas solicitadas e invoca a função CRM.fn_idade para
calcular a idade da pessoa.
No final, mostra todas as linhas da tabela e as colunas solicitadas, somente para as linhas que
satisfizeram a condição da cláusula WHERE.
Observe que, em ambas as consultas, o SGBD percorre a tabela inteira para recuperar os
resultados.
Na primeira consulta, o solicitante, de fato, deseja recuperar todas as linhas, pois não há
qualquer condição definida.
Entretanto, na segunda, existe uma condição definida por data_nascimento >= '2020/01/01'.
O solicitante deseja recuperar apenas as linhas que satisfazem essa condição. Para isso, o
SGBD precisa realmente percorrer todas as linhas da tabela para verificar aquelas que
satisfazem a condição?
Índices são estruturas de acesso especiais, que permitem ao SGBD localizar as linhas de uma
tabela baseado em uma ou mais colunas.
Podemos criar um índice na coluna data_nascimento, de forma que o SELECT não tenha que
percorrer toda a tabela para encontrar linhas que satisfaçam a uma condição dada, baseada
nessa coluna.
 EXEMPLO
O SGBD pode utilizar esse índice para localizar mais rapidamente apenas as linhas com
data_nascimento >= '2020/01/01.
Para trabalhar adequadamente com índices e mostrar o real impacto que ele pode trazer para
as consultas submetidas a um banco de dados, vamos executar uma carga de dados na tabela
CRM.Pessoa.
Observe o código SQL a seguir:
 
 Carga na tabela CRM.Pessoa. 
Captura de tela do software SQL Server Management Studio (SSMS).
Queremos que a tabela tenha um total de 100.000 linhas. Como ela já tem 2 (recriamos a linha
de Ada Lovelace deletada anteriormente), o loop WHILE começa em 2 e vai até 100000.
Em cada iteração, a variável é incrementada e a função NEWID() é utilizada para gerar um
valor aleatório do tipo uniqueidentifier.
Você pode testar seu uso em conjunto com a função CHECKSUM e ABS, conforme mostrado a
seguir:
 SELECT ABS(CHECKSUM(NEWID())
A função NEWID() gera um uniqueidentifier de 16 bytes.
A função CHECKSUM converte esse valor em um número com 9 ou 10 dígitos.
A função ABS retorna seu valor absoluto.
Com esse valor, podemos gerar outros valores, de acordo com o tipo de dado.
Para @nome_pessoa, usamos a função CONVERT para converter esse valor em uma
string de até 50 caracteres.
Para @data_nascimento, dividimos o valor gerado por 100.000 e multiplicamos por -1,
para que seja diminuído da data atual, recuperada por meio da função GETDATE(),
convertida para um tipo date.
Para @renda_mensal, o valor aleatório é simplesmente dividido por 100.000.
Após a execução do código, podemos ver o resultado.
 
 Validação da carga da tabela CRM.Pessoa. 
Captura de tela do software SQL Server Management Studio (SSMS).
COUNT(*) confirma que temos 100.000 linhas na tabela. Executando um SELECT TOP(10),
temos os valores gerados para as colunas nome_pessoa, data_nascimento e renda_mensal
das 10 primeiras linhas, confirmando também os valores aleatórios gerados.
Em seguida, vamos observar o comportamento da consulta dada como exemplo anteriormente.
 
 Seleção de dados da tabela CRM.Pessoa após a carga. 
Captura de tela do software SQL Server Management Studio (SSMS).
Observe, no canto inferior direito, o total de 2.294 linhas que satisfazem a condição
especificada para a coluna data_nascimento. Isso nos diz que, das 100.000 linhas da tabela,
2.294 possuem data_nascimento >= '2020/01/01'.
Para ver como o SGBD resolve a consulta, você pode usar as teclas CTRL-L, com a query
selecionada, para ver o seu Plano de Execução.
 
 Plano de execução utilizando Scan. 
Captura de tela do software SQL Server Management Studio (SSMS).
Observe o operador Clustered Index Scan, que indica uma varredura completa (Scan) na
tabela (índice clusterizado). Toda tabela que contém uma chave primária é criada como um
índice clusterizado. Sua ordenação física nos blocos de disco segue a ordenação da chave
primária.
 COMENTÁRIO
Quando não há uma chave primária, a tabela é criada como uma heap. Como não existe um
índice pela coluna data_nascimento, essa é a única opção que o SGBD tem para recuperar os
dados da consulta.
Para criar um índice nessa coluna, vamos usar o comando CREATE INDEX, conforme a
imagem a seguir:
 
 Criação do índice não clusterizado. 
Captura de tela do software SQL Server Management Studio (SSMS).
Com essa instrução, estamos criando o índice ind_Pessoa_data_nascimento (não clusterizado)
para a tabela CRM.Pessoa, na coluna data_nascimento. Após a criação, o SGBD pode usar o
índice para resolver a consulta SQL.
Fazendo essa mesma consulta com diferentes valores de data, podemos ver quando o SGBD
utiliza uma varredura completa na tabela (Scan) ou uma busca pelo índice (Seek).
Veja, a seguir, um exemplo de uso do índice que acabamos de criar.
 
 Plano de execução utilizando Seek. 
Captura de tela do software SQL Server Management Studio (SSMS).
O que o plano de execução nos mostra é o uso do índice não clusterizado
ind_Pessoa_data_nascimento. Para cada chave encontrada nesse índice que satisfaz a
condição especificada no WHERE, é realizada a pesquisa da chave primária na tabela
CRM.Pessoa, e a recuperação dos dados solicitados.
 ATENÇÃO
Note que essa estratégia é bem diferente da varredura completa da tabela. Por meio do índice,
o SGBD foi capaz de localizar diretamente as chaves que satisfizeram a condição
data_nascimento>= '2021/05/01'.
Para mais detalhes sobre a criação de índices, consulte o endereço a seguir:
https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-index-transact-sql?view=sql-server-
ver15
javascript:void(0);
OS PARADIGMAS DE COMUNICAÇÃO EM
COMPUTAÇÃO DISTRIBUÍDA
A criação de objetos no SQL Server, utilizando um ambiente real, e a captura dos
procedimentos são apresentadasno vídeo a seguir.
VERIFICANDO O APRENDIZADO
1. A RESPEITO DO USO DE VIEWS, STORED PROCEDURES, TRIGGERS E
FUNCTIONS NO SQL SERVER, ASSINALE A ALTERNATIVA INCORRETA.
A) As visões são uma boa prática de programação em bancos de dados SQL Server,
especialmente quando há muitas tabelas envolvidas em consultas frequentes. Nesses casos,
cria-se uma visão genérica que implementa os joins das tabelas, permitindo ao desenvolvedor
utilizar a visão e especificar seus critérios na cláusula WHERE.
B) Trigger é um recurso muito utilizado na implementação de bancos de dados SQL Server.
Pode-se, por exemplo, criar um gatilho para executar um código SQL antes ou após a
execução de um comando INSERT, UPDATE ou DELETE.
C) No SQL Server, as stored procedures são muito utilizadas para implementar tarefas que,
frequentemente, contém diversas atualizações de tabelas do banco de dados. Elas podem
receber parâmetros e retornar resultados.
D) A diferença entre uma stored procedure e uma função é que a última deve necessariamente
retornar um resultado.
E) No SQL Server, as funções podem retornar um valor escalar ou um resultset, que
corresponde a um valor de tabela.
2. UMA TABELA FOI CRIADA NO SQL SERVER, CONTENDO UMA CHAVE
PRIMÁRIA INTEIRA. QUE TIPO DE ESTRUTURA DE DADOS IMPLEMENTA
A TABELA?
A) Índice clusterizado
B) Índice não clusterizado
C) Heap
D) Tabela Hash
E) Índice binário
GABARITO
1. A respeito do uso de views, stored procedures, triggers e functions no SQL Server,
assinale a alternativa INCORRETA.
A alternativa "B " está correta.
 
No SQL Server, não há triggers BEFORE; existem somente os tipos AFTER (ou FOR) e
INSTEAD OF.
2. Uma tabela foi criada no SQL Server, contendo uma chave primária inteira. Que tipo de
estrutura de dados implementa a tabela?
A alternativa "A " está correta.
 
O índice clusterizado é a estrutura de dados padrão para uma tabela que possui chave primária
definida.
MÓDULO 3
 Aplicar técnicas de tuning com estatísticas e índices
SELETIVIDADE
O componente do SGBD responsável por gerar o plano de execução de uma consulta é o
otimizador de consultas.
A atividade de tuning, também chamada de sintonia fina, consiste, essencialmente, em alterar
o comportamento do otimizador do SGBD, visando melhorar o desempenho das consultas
submetidas ao banco de dados.
Entretanto, como o otimizador decide qual plano de execução usar? Como ele decide se vai
varrer uma tabela inteira ou usar um índice?
Voltando ao exemplo dado no módulo anterior, por que o otimizador decidiu usar o índice, em
vez de varrer a tabela inteira, quando o predicado data_nascimento >= '2020/01/01' foi
substituído por data_nascimento >= '2021/05/01'?
O conceito de seletividade é fundamental para responder a essa pergunta.
Dizemos que um predicado tem seletividade alta quando faz a consulta retornar um percentual
pequeno de linhas da tabela.
Por outro lado, dizemos que um predicado tem seletividade baixa quando faz a consulta
retornar um percentual grande de linhas da tabela.
Quanto maior a seletividade, maior a probabilidade do otimizador escolher usar o índice
correspondente.
Por que o índice foi utilizado com o predicado data_nascimento >= '2021/05/01'?
Porque a seletividade foi alta o suficiente para fazer com que o otimizador escolhesse esse
plano de execução, que utiliza o índice não clusterizado. Na verdade, o que realmente importa
para otimizador é que essa seletividade faz com que o custo do plano de execução, que utiliza
o índice não clusterizado, seja menor que o custo do plano de execução que utiliza o índice
clusterizado.
Vamos, então, visualizar os custos que o próprio SQL Server nos mostra. Posicionando o
cursor sobre o operador Scan do plano de execução da consulta correspondente ao predicado
data_nascimento >= '2020/01/01', temos a janela mostrada a seguir:
 
Imagem: Antony Seabra de Medeiros.
 Custos e estimativas do operador Scan.
Observe os custos e as estimativas. Há um custo associado ao processamento (CPU) e outro
associado à leitura de dados (E/S).
Note que o maior custo está, de fato, na leitura dos dados, que representa aproximadamente
87% do custo total.
Em relação às estimativas, a informação fornecida ao otimizador é que ele vai percorrer 2.282
linhas, de um total de 100.000, cada uma com tamanho médio de 39 bytes. Com base nesses
custos e estimativas, o otimizador decide varrer a tabela inteira por meio de um clustered
index scan.
Em seguida, vamos verificar o plano de execução da consulta correspondente ao predicado
data_nascimento >= '2021/05/01'. Posicionando o cursor sobre os dois operadores do plano,
temos as janelas mostradas a seguir:
 
Imagem: Antony Seabra de Medeiros.
 Custos e estimativas do operador Seek.
Vamos começar pelo operador Index Seek. De fato, é por ele que a busca será iniciada.
Observe os custos e as estimativas. Há um custo associado ao processamento (CPU) e outro
associado à leitura de dados (E/S).
Novamente, o maior custo está associado à leitura dos dados, que representa
aproximadamente 93% do custo total. Em relação às estimativas, a informação que o
otimizador tem é que vai percorrer 42 linhas, cada uma com tamanho médio de 14 bytes.
Nessa estratégia do otimizador, para cada chave recuperada do índice, será realizada uma
pesquisa na tabela principal (lookup), para recuperar a linha correspondente. Para esse
operador, que representa 97% do custo total do plano, temos a estimativa de leitura de 1 linha
para cada uma das 42 execuções.
 ATENÇÃO
Com base nesses custos e estimativas, o otimizador decide percorrer o índice por meio de um
non-clustered index seek e, para cada linha encontrada que satisfaça à condição estabelecida,
fazer o lookup na tabela principal.
O que chama nossa atenção nesse plano é seu custo total, quando comparado ao do plano
anterior que utilizou o Scan. Muito embora estejamos comparando consultas diferentes, é
possível ter uma ideia da diferença associada ao uso do índice não clusterizado.
Note que o primeiro plano, que percorre a tabela inteira, tem um custo estimado em
aproximadamente 0,83.
Já o segundo plano, que utiliza os operadores Seek e Lookup, tem um custo total estimado em
aproximadamente 0,13, o que representa cerca de 15% do primeiro.
Naturalmente, você poderá encontrar valores diferentes nos seus testes e, mesmo executando
as consultas em uma única máquina, custos e estimativas vão variar ao longo do tempo,
fazendo com que o otimizador tome decisões diferentes, de acordo com os dados que
fornecidos. Imagine que, durante a vida útil de um banco de dados, as tabelas vão sendo
atualizadas e linhas vão sendo inseridas e removidas.
A distribuição dos dados pelos volumes de armazenamento dos servidores sofre
fragmentações, o que altera, sobremaneira, os custos de E/S associados às leituras e
atualizações, da mesma forma que altera também os custos de processamento. Logo, é de se
esperar que o otimizador também varie a sua decisão, de acordo com esses parâmetros que
vão sendo alterados ao longo do tempo.
 COMENTÁRIO
É por isso que o desempenho das cargas de trabalho tende a variar; é preciso que os
profissionais estejam atentos e tomem as ações necessárias para mantê-lo adequado às
expectativas de usuários e aplicações.
Contudo, resta uma pergunta: quem fornece as informações de custos e estimativas para o
otimizador?
No primeiro plano, como ele sabe, a priori, que vai encontrar 2.282 linhas que satisfazem a
condição estabelecida se ele ainda não percorreu a tabela?
No segundo plano, como ele sabe, a priori, que vai encontrar 42 linhas que satisfaçam a
condição dada se ele ainda não percorreu o índice?
ESTATÍSTICAS
As informações que o otimizador tem para analisar planos de execução estão nas estatísticas
do banco de dados. O otimizador utiliza as estatísticas coletadas dos objetos para gerar seus
planos de execução. O número de linhas em uma tabela e a distribuição dos valores nas
colunas da tabela (histograma)são mantidos nessas estatísticas, de forma que o otimizador
possa selecionar os operadores mais apropriados para os planos de execução.
Vamos observar as estatísticas das tabelas que criamos:
O primeiro passo é recuperar o object_id de cada objeto.
Para isso, consultamos a tabela sys.objects, que faz parte do catálogo do SQL Server. Com o
object_id, podemos consultar a tabela sys.stats, que contém as informações sobre as
estatísticas coletadas do objeto.
Na imagem a seguir, você pode observar as tabelas que criamos e os valores de object_id
correspondentes no resultado do primeiro SELECT.
 
 Informações sobre estatísticas de objetos. 
Captura de tela do software SQL Server Management Studio (SSMS).
No segundo SELECT, utilizamos o object_id para recuperar as informações sobre as
estatísticas coletadas para todos os objetos associados à tabela CRM.Pessoa, incluindo os
índices clusterizado e não clusterizado.
 COMENTÁRIO
Note que cada um deles contém um stats_id, que identifica as estatísticas que foram coletadas
para cada um deles.
Por meio desse stats_id, podemos recuperar as informações sobre as coletas realizadas.
 
 Informações sobre estatísticas de objetos. 
Captura de tela do software SQL Server Management Studio (SSMS).
A sys.stats traz informações muito importantes, como a data da última atualização das
estatísticas, o número de linhas da tabela e o número de linhas utilizado para gerar as
estatísticas (rows_sampled).
 COMENTÁRIO
Esse resultado nos mostra que as estatísticas estão desatualizadas, pois o número de linhas
exibido (1.000) não corresponde ao número de linhas atual da tabela (100.000).
Por que isso aconteceu?
As estatísticas não são atualizadas a todo momento.
Em intervalos regulares, o SQL Server atualiza os dados varrendo um percentual das linhas
das tabelas. Os dados coletados são armazenados nas estatísticas e disponibilizados para o
otimizador. Quanto mais atualizadas as estatísticas, melhor para o otimizador, pois ele vai
utilizar informações mais precisas para selecionar os planos de execução das consultas.
Veja, no endereço a seguir, mais detalhes sobre estatísticas do banco de dados:
https://docs.microsoft.com/pt-br/sql/relational-databases/statistics/statistics?view=sql-server-
ver15
 
 Histograma da chave primária da tabela CRM.Pessoa. 
Captura de tela do software SQL Server Management Studio (SSMS).
Podemos avançar mais na exibição das estatísticas por meio da imagem do comando anterior.
Ele mostra o histograma da coluna, isto é, a distribuição dos valores na coluna.
No primeiro DBCC, estamos visualizando o histograma da chave primária da tabela
CRM.Pessoa, organizado por faixas.
javascript:void(0);
As faixas são determinadas pelo valor mais alto (RANGE_HI_KEY).
Para cada faixa, você pode ver o número de linhas dentro da faixa (RANGE_ROWS) e o
número de linhas cujo valor é igual ao valor mais alto da faixa (EQ_ROWS).
O histograma possui ainda o número de valores distintos na faixa (DISTINCT_RANGE_ROWS)
e o número médio de linhas para cada valor da faixa (AVG_RANGE_ROWS).
Na próxima imagem, podemos ver o histograma da coluna data_nascimento.
 
 Histograma da coluna data_nascimento da tabela CRM.Pessoa. 
Captura de tela do software SQL Server Management Studio (SSMS).
Pelo histograma da coluna, podemos inferir que há um maior número de valores distintos
dentro de cada faixa, quando comparamos com o histograma anterior, exceto pelas datas não
geradas automaticamente pelo script que executamos no módulo 2.
Também chama a atenção um valor diferenciado para AVG_RANGE_ROWS, resultado do
descasamento entre RANGE_ROWS e DISTINCT_RANGE_ROWS, observado nas outras
faixas. Isso nos indica que, nessa faixa, existem 2 datas de nascimento iguais.
Como apontamos anteriormente, as estatísticas estão desatualizadas. Precisamos atualizá-las
e, para isso, vamos utilizar o comando UPDATE STATISTICS.
Veja mais detalhes sobre o comando UPDATE STATISTICS no endereço a seguir:
https://docs.microsoft.com/pt-br/sql/t-sql/statements/update-statistics-transact-sql?view=sql-
server-ver15
javascript:void(0);
 
 Geração dos comandos UPDATE STATISTICS. 
Captura de tela do software SQL Server Management Studio (SSMS).
A construção mostrada na imagem anterior percorre as tabelas do catálogo do SQL Server
para gerar um comando UPDATE STATISTICS para todas as tabelas presentes no banco de
dados.
 COMENTÁRIO
Repare que a cláusula WHERE garante que somente as chaves primárias das tabelas do
banco de dados serão selecionadas para a construção dos comandos. Note também que o
comando é gerado com a opção FULL SCAN, determinando que todas as linhas da tabela
sejam verificadas para a coleta das estatísticas.
Para executar a atualização, basta copiar e colar o comando gerado, e, depois, executá-lo.
 
 Comando UPDATE STATISTICS com FULL SCAN. 
Captura de tela do software SQL Server Management Studio (SSMS).
Após a execução da atualização, podemos repetir o comando para exibir os dados da coleta.
Veja que, agora, esses dados mostram que as 100.000 linhas foram consideradas na coleta.
 
 Comando UPDATE STATISTICS com FULL SCAN. 
Captura de tela do software SQL Server Management Studio (SSMS).
ÍNDICES
Vimos, no módulo anterior, como criar índices e como eles são importantes para o
desempenho das consultas no SQL Server.
Os índices podem ser muito benéficos se, de fato, forem utilizados pelo otimizador,
especialmente quando criados sobre colunas que tendem a ter seletividade alta para a maior
parte dos seus valores.
Em contraposição, eles podem ser maléficos se criados em colunas de seletividade baixa.
Exemplos clássicos incluem colunas como sexo ou estado_civil. Há poucas opções de valor
para essas colunas, de modo que elas terão seletividade baixa e seus índices raramente serão
utilizados.
Uma questão interessante é: por que seriam maléficos, já que não serão utilizados?
Há um custo associado à manutenção do índice. Toda vez que uma atualização é realizada
sobre uma tabela, todos os índices podem ser atualizados também. Tipicamente, vamos
encontrar tabelas com dezenas de índices não clusterizados, e atualizá-los tem,
principalmente, custo de E/S associado.
Garantir que os índices existentes estejam, de fato, sendo utilizados é fundamental para o bom
desempenho das atualizações no banco de dados, além de evitar desperdício de volume de
armazenamento.
Ainda que índices ocupem bem menos espaço que as tabelas, você não vai querer uma tabela
com 70 índices, sendo que 40 não são utilizados. Veja a seguir como verificar se seus índices
estão sendo utilizados.
 
 Uso de índices. 
Captura de tela do software SQL Server Management Studio (SSMS).
A consulta mostra, para cada índice da tabela CRM.Pessoa, além do seu tipo e tamanho, o
número de Seeks, Scans, Lookups e Updates. Em nosso exemplo, não resta dúvida de que o
índice não clusterizado na coluna data_nascimento está servindo ao seu propósito: há um alto
número de Seeks e poucos Scans.
Já o índice clusterizado contém muitos Lookups e poucos Scans, o que também é um indicador
positivo, garantindo planos de execução que não varrem completamente a tabela.
 ATENÇÃO
Caso você encontre, em um relatório desse tipo, índices não clusterizados com poucos Seeks,
você deve verificar seu custo de manutenção e considerar sua remoção. Caso encontre índices
clusterizados com muitos Scans e poucos Lookups, você deve suspeitar da falta de índices que
melhorem os planos de execução gerados pelo otimizador.
Veja mais detalhes sobre o uso de índices nos endereços a seguir: 
https://docs.microsoft .com/pt-br/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-
ver15
https://github.com /microsoft/sql-server-samples/tree/master/ samples/databases
javascript:void(0);
javascript:void(0);
Além da questão do uso dos índices, há o problema da fragmentação. Como comentamos
anteriormente, durante o ciclo de vida útil de um banco de dados, linhassão inseridas,
removidas e atualizadas nas tabelas, e isso provoca as mesmas operações nos índices
associados. Quanto maior o volume de atualizações, maior a fragmentação das estruturas de
dados, cujas páginas sofrem frequentes splits nas inserções e atualizações.
Quanto mais fragmentada estiver uma estrutura, maior o número de páginas necessárias para
satisfazer uma consulta e, portanto, maior o seu custo de E/S e menor o seu desempenho.
Por isso, como não podemos evitar a fragmentação, devemos monitorá-la e executar as ações
apropriadas quando necessário. Vamos checar como está a fragmentação em nossos objetos.
 
 Fragmentação de índices. 
Captura de tela do software SQL Server Management Studio (SSMS).
Podemos observar, pelo resultado apresentado, que os índices clusterizados das tabelas têm
pouca ou nenhuma fragmentação, mas o índice não clusterizado ind_Pessoa_data_nascimento
tem um índice maior que 98%, o que significa que a quase totalidade do índice está
fragmentada, ou seja, quase todas as páginas de dados estão em uma ordem diferente da
ordenação física da tabela. No SQL Server, as manobras possíveis para resolver esse
problema são o REORGANIZE e o REBUILD.
No REBUILD, o índice é excluído e criado novamente. Quando isso acontece, garante-se que o
percentual de fragmentação vai retornar a zero.
No REORGANIZE, o índice não é excluído e a reorganização é realizada por meio de
operações como movimentação de linhas e realocação de páginas.
Ambas as operações vão impactar as cargas de trabalho; portanto, é importante ter uma janela
para realizar a operação.
 DICA
Sugere-se um REORGANIZE para índices de fragmentação pequenos. Para índices maiores,
como o que observamos em ind_Pessoa_data_nascimento, é melhor executar um REBUILD.
 
 Rebuild de índice. 
Captura de tela do software SQL Server Management Studio (SSMS).
O REBUILD pode ser executado em modo OFF-LINE ou ON-LINE.
No modo OFF-LINE, o acesso à tabela correspondente ao índice é bloqueado para que a
operação seja executada mais rapidamente.
No modo ON-LINE, o acesso continua disponível porque o índice atual permanece operacional
enquanto o novo é criado.
Após a execução do REBUILD, podemos checar novamente o nível de fragmentação:
 
 Fragmentação após Rebuild de índice. 
Captura de tela do software SQL Server Management Studio (SSMS).
A fragmentação retornou de fato a zero; nesse momento, todas as páginas estão ordenadas de
acordo com a ordenação física do índice e, portanto, não há fragmentação.
Para conhecer todas as opções existentes para REBUILD e REORGANIZE, visite o endereço:
https://docs.microsoft.com/pt-br/sql/relational-databases/indexes/reorganize-and-rebuild-
indexes?view=sql-server-ver15
Para mais detalhes sobre o comando ALTER INDEX, visite o endereço:
https://docs.microsoft.com/pt-br/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-
ver15
ATIVIDADE PRÁTICA
Verifique o plano de execução da consulta que forma a visão criada na atividade prática do
módulo anterior, a CRM.vw_pessoa_documento_endereco.
É possível melhorar esse plano e os custos associados a ele?
SOLUÇÃO
 
 Solução da atividade prática, elaborada por Antony Seabra de Medeiros. 
Captura de tela do software SQL Server Management Studio (SSMS).
O plano de execução apresenta um scan no índice clusterizado da tabela
CRM.Pessoa_Endereco, onde é realizado o WHERE na coluna tipo_endereco. Um índice não
clusterizado nessa coluna é uma alternativa, caso a seletividade desse predicado seja alta o
suficiente para que o otimizador o selecione para o plano.
Outras manobras clássicas são a atualização das estatísticas de todas as tabelas envolvidas e
a desfragmentação dos índices, caso necessário.
javascript:void(0);
javascript:void(0);
OS PARADIGMAS DE COMUNICAÇÃO EM
COMPUTAÇÃO DISTRIBUÍDA
No vídeo a seguir, assista a uma demonstração de tuning no SQL Server utilizando estatísticas
e índices, um ambiente real e captura dos procedimentos.
VERIFICANDO O APRENDIZADO
1. AS ESTATÍSTICAS DE UM BANCO DE DADOS FORNECEM
INFORMAÇÕES IMPORTANTES PARA QUE O OTIMIZADOR SELECIONE
UM PLANO DE EXECUÇÃO PARA CADA CONSULTA SUBMETIDA A ELE.
ENTRE AS INFORMAÇÕES FORNECIDAS PELAS ESTATÍSTICAS, ESTÃO,
EXCETO:
A) Número de linhas.
B) Tamanho médio da linha.
C) Distribuição de valores na coluna.
D) Última data da coleta.
E) Próxima data da coleta.
2. A FRAGMENTAÇÃO DE ÍNDICES PODE DEGRADAR O DESEMPENHO
DE SCANS E SEEKS, NA MEDIDA EM QUE AS PÁGINAS DE DADOS
FICAM DESORDENADAS NO DISPOSITIVO DE ARMAZENAMENTO.
ENTRE AS AÇÕES QUE PODEMOS TOMAR PARA DESFRAGMENTAR UM
ÍNDICE, ESTÃO, EXCETO:
A) Remover o índice e criá-lo novamente.
B) Fazer um refresh.
C) Fazer um rebuild on-line.
D) Fazer um rebuild off-line.
E) Fazer um reorganize.
GABARITO
1. As estatísticas de um banco de dados fornecem informações importantes para que o
otimizador selecione um plano de execução para cada consulta submetida a ele. Entre
as informações fornecidas pelas estatísticas, estão, EXCETO:
A alternativa "E " está correta.
 
A próxima data da coleta das estatísticas não é informada; apenas a última data é mostrada
quando consultamos a sys.dm_db_stats_properties.
2. A fragmentação de índices pode degradar o desempenho de Scans e Seeks, na
medida em que as páginas de dados ficam desordenadas no dispositivo de
armazenamento. Entre as ações que podemos tomar para desfragmentar um índice,
estão, EXCETO:
A alternativa "B " está correta.
 
Não existe uma operação de refresh de índice. O que existe, e pode ser utilizado para
desfragmentar um índice, é um rebuild ou um reorganize.
CONCLUSÃO
CONSIDERAÇÕES FINAIS
Neste tema, vimos como os principais objetos de um banco de dados SQL Server podem ser
criados e utilizados de acordo com a sua funcionalidade. É fundamental, tanto para o
desenvolvedor de sistemas quanto para o administrador de banco de dados, relacionar esses
objetos com o seu propósito, de maneira que cada peça do banco de dados possa ser utilizada
adequadamente. Casos de uso clássicos foram citados, como o de triggers, para gerar um
archive de dados, e o de índices, para melhorar o desempenho de consultas, em uma atividade
que chamamos de tuning.
Estamos certos de que os módulos do presente conteúdo foram proveitosos, principalmente
com o uso dos scripts disponibilizados. Experimentar as manobras e observar os resultados é a
melhor maneira de avançar na construção do conhecimento, especialmente quando os
conceitos e as técnicas, como as apresentados para o SQL Server, dependem muito do
ambiente de execução. O uso de estatísticas, sua atualização e o impacto que um índice tem
nas decisões do otimizador só podem ser compreendidos se você experimentar suas diversas
facetas, usando a sua própria instância de SQL Server.
AVALIAÇÃO DO TEMA:
REFERÊNCIAS
ASSAF, W.; WEST, R.; AELTERMAN, S.; CURNUTT, M. SQL Server 2017 Administration
Inside Out. 1 ed. Microsoft Press, 2017.
DELANEY, K.; FREEMAN, C. Microsoft SQL Server 2012 Internals. 1 ed. Microsoft Press,
2012.
RAMAKRISHNAN, R.; GEHRKE, J. Database Management Systems. 3. ed. Nova York:
McGraw Hill, 2002.
EXPLORE+
Explore o GitHub da Microsoft, no site da Microsoft SQL ‒ Documentação. Lá, você vai
encontrar scripts de instalação de bancos de dados, como o adventure-works e o wide-world-
importers, clássicos do mundo SQL Server. Implante o último em sua instância. Ele contém
estruturas e procedimentos preparados para mostrar tanto cargas transacionais quanto
analíticas, sendo possível acompanhar a criação de todos os tipos de objetos estudados neste
conteúdo.
Além da criação e manutenção dos objetos, as consultas preparadas para o banco de dados
contêm planos de execução diversos. Você vai poder verificá-los e propor ajustes de tuning,
com atualização das estatísticas do banco de dados e a criação de índices não clusterizados,
caso necessário.
Experimente! Certamente, você vai avançar nos seus conhecimentos ao observar como a
própriaMicrosoft estruturou o banco de dados mais estudado na atualidade por profissionais
especializados em SQL Server.
CONTEUDISTA
Antony Seabra de Medeiros