Índices
Os índices são um dos recursos mais importantes do SQL Server e de outros sistemas de gerenciamento de bancos de dados relacionais, eles são uma forma de acelerar consultas ao banco de dados, permitindo que as buscas sejam executadas mais rapidamente e com maior eficiência, são semelhantes aos índices em livros, que ajudam a encontrar rapidamente a informação desejada.
Os índices são criados em colunas de tabelas e contêm uma lista de valores únicos associados a um conjunto de registros correspondentes. Em termos simples, um índice é uma estrutura de dados que organiza os registros de uma tabela em uma ordem lógica que facilita a pesquisa.
Os índices podem ser classificados em dois tipos principais: índices clusterizados e índices não clusterizados.
O índice clusterizado é criado em uma coluna da tabela que determina a ordem física dos dados na tabela, cada tabela pode ter apenas um índice clusterizado e os dados são organizados em ordem crescente ou decrescente com base na coluna escolhida.
Já os índices não clusterizados são criados em colunas diferentes daquelas utilizadas para criar o índice clusterizado. Eles também possuem uma lista de valores únicos associados a um conjunto de registros correspondentes, mas não determinam a ordem física dos dados na tabela.
Criando um índice não clusterizado
Para criar um índice não clusterizado em uma tabela, você pode utilizar o comando CREATE INDEX.
Por exemplo, suponha que você tenha uma tabela chamada Clientes com uma coluna Nome que é frequentemente usada em consultas.
Você pode criar um índice não clusterizado nessa coluna da seguinte forma:
Isso criará um índice não clusterizado chamado idx_Clientes_Nome na tabela Clientes, usando a coluna Nome como chave de índice.
Criando um índice clusterizado
Para criar um índice clusterizado em uma tabela, você pode utilizar o comando CREATE CLUSTERED INDEX. Suponha que você tenha uma tabela chamada Pedidos com uma coluna IDPedido que é única e frequentemente usada em consultas. Você pode criar um índice clusterizado nessa coluna da seguinte forma:
Isso criará um índice clusterizado chamado idx_Pedidos_IDPedido na tabela Pedidos, usando a coluna IDPedido como chave de índice.
Um exemplo de como os índices podem ser utilizados para melhorar o desempenho de consultas é em uma tabela de vendas de uma loja. Suponha que o gerente da loja queira saber o total de vendas por produto em um determinado período.
Sem um índice, o SQL Server precisaria percorrer todos os registros da tabela de vendas, o que pode ser bastante demorado em uma tabela com muitos registros. Com um índice na coluna de código do produto, no entanto, o SQL Server pode localizar rapidamente todos os registros correspondentes e calcular o total de vendas de cada produto.
Outra maneira como os índices ajudam a melhorar o desempenho de consultas é permitindo que o banco de dados utilize um plano de execução otimizado, quando um índice é criado em uma ou mais colunas relevantes para uma consulta, o banco de dados pode usar o índice para criar um plano de execução otimizado para essa consulta.
Monitorando o desempenho de índices
Para monitorar o desempenho dos índices em uma tabela, você pode utilizar a função sys.dm_db_index_usage_stats. Essa função retorna informações sobre o uso de índices em uma instância do SQL Server, incluindo informações como a última vez que um índice foi usado, o número de vezes que foi usado, o número de leituras físicas e lógicas, entre outras.
Por exemplo, para verificar o desempenho do índice idx_Clientes_Nome na tabela Clientes, você pode executar a seguinte consulta:
Essa consulta retornará informações sobre o uso do índice idx_Clientes_Nome na tabela Clientes.
O plano de execução é uma representação interna do caminho que o banco de dados seguirá para executar a consulta. Quando um índice relevante é usado na consulta, o plano de execução pode ser otimizado para usar o índice da maneira mais eficiente possível.
Isso pode incluir a seleção de um índice diferente, a mudança da ordem de junção das tabelas e outras otimizações que ajudam a melhorar a velocidade da consulta.
No entanto, a criação de índices não deve ser feita de forma indiscriminada, pois pode levar a um consumo excessivo de recursos de memória e disco. É importante considerar cuidadosamente quais colunas da tabela realmente precisam de um índice e quais consultas são mais frequentes e críticas em termos de desempenho.
Desvantagens associadas ao uso de índices:
· A criação de índices pode aumentar o tempo de inserção de dados na tabela, já que o SQL Server precisa atualizar os índices a cada nova inserção. Além disso, índices ocupam espaço em disco, o que pode ser um problema em sistemas com restrições de espaço.
Algumas das vantagens do índices são:
· A utilização de índices pode trazer diversas vantagens para um sistema de gerenciamento de banco de dados, uma delas é a melhora no desempenho de consultas, uma vez que o banco de dados pode encontrar os registros relevantes mais rapidamente.
· a possibilidade de realizar buscas em colunas específicas com maior eficiência, sem precisar percorrer todos os registros da tabela.
· índices podem ajudar a garantir a integridade dos dados, por exemplo, é possível criar um índice em uma coluna que não permite valores duplicados, garantindo que não haja registros duplicados na tabela.
· Os índices podem ser criados em diferentes tipos de colunas de uma tabela, incluindo colunas de chave primária, colunas de chave estrangeira e colunas de dados.
· Eles podem ser criados de forma manual, usando instruções SQL específicas, ou automaticamente pelo SQL Server, em resposta a determinadas consultas.
Em resumo, os índices são estruturas de dados importantes em SQL Server que podem melhorar significativamente a performance de consultas em tabelas grandes. Eles são criados em colunas de tabelas e ajudam a localizar rapidamente os registros que correspondem a uma determinada condição da consulta.
Embora haja algumas desvantagens associadas ao uso de índices, as vantagens geralmente superam os potenciais problemas, tornando os índices uma ferramenta valiosa para os desenvolvedores de bancos de dados.
Particionamento de Dados
O particionamento é uma técnica que consiste em dividir grandes tabelas ou índices em partes menores, chamadas de partições. Essa técnica é fundamental na modelagem de dados, uma vez que possibilita o gerenciamento mais eficiente e escalável de grandes volumes de informações.
O particionamento dos dados é baseado em um atributo, ou seja, um campo da tabela que é verificado no momento da inserção. Com isso, o motor do SQL Server sabe para qual filegroup enviar o registro. Além disso, o particionamento também pode ser aplicado em índices e views indexadas, funcionando de maneira semelhante à tabela. Quando um índice segue a mesma regra de particionamento da tabela, dizemos que eles estão alinhados, ou seja, os dados da tabela e do índice são armazenados no mesmo filegroup.
Para implementar o particionamento em uma tabela, índice ou view indexada, é necessário criar uma função de partição e um esquema de partição no banco de dados. A função de partição é um objeto independente que estabelece os limites (intervalo) dos dados a serem inseridos nas tabelas e que pode ser reutilizado em mais de um particionamento. Já o esquema de partição é um objeto que está diretamente relacionado à função de partição e que aponta para o intervalo correspondente ao filegroup no qual os dados devem ser armazenados. Esse esquema também é declarado no momento da criação da tabela, índice ou view indexada.
Para criar uma tabela, índice ou view indexada no banco de dados, é necessário criar uma função de partição e um esquema de partição. A função de partição é um objeto independente que define os limites dos dados a serem inseridos nas tabelas e pode ser reutilizado em vários particionamentos. O esquema de partição é um objeto relacionado diretamente à função de partição e indica em qual filegroup os dados devem ser armazenados, além de ser declarado durante a criação da tabela, índice ou view indexada.
Particionando Tabelas em SQL Server
O particionamento de tabelas é uma técnica utilizada para dividir uma tabela extensa em segmentos menores e mais fáceis de gerenciar, sem a necessidade de criar tabelas distintas para cada segmento. Os registros em uma tabela particionada são armazenados fisicamente em grupos de linhas chamados partições, e cada partição pode ser acessada e mantida independentemente das demais.
Por exemplo:
- Todas as linhas com data anterior a 2012 estão alocadas na primeira partição
- Todas as linhas com data anterior a 2013 estão alocadas na segunda partição
- Todas as linhas com data anterior a 2014 estão alocadas na terceira partição
- Todas as linhas com data 2015 ou superior, estão alocadas na quarta partição
* Se a coluna tiver algum valor nulo, este estará alocado na primeira partição.
Função de Particionamento
Ao implementar o particionamento no SQL Server, o primeiro procedimento após identificar a tabela e a coluna de referência é criar uma função de particionamento, que tem como principal objetivo estabelecer os limites de cada partição.
-- Cria uma função de particionamento para ser usada em coluna do tipo DATE usando a opção RIGHT
CREATE PARTITION FUNCTION pfSales (DATE) AS RANGE RIGHT FOR VALUES ('2013-01-01', '2014-01-01', '2015-01-01')
Range LEFT ou RIGHT
O exemplo acima usa a opção RANGE RIGHT para informar a função que deve ser considerada como parte da partição o valor informado e todos os valores maiores do que ele, na opção LEFT, seria o inverso. Na imagem fica mais claro, na sequência como seria a partição com LEFT e RIGHT
Esquema de Partição
Com a função de particionamento já criada, é viável definir o plano de particionamento que irá atribuir os intervalos de valores às partições que serão alocadas nos filegroups, que foram anteriormente criados e associados a arquivos de dados.
-- Cria o esquema que utiliza os limites definidos na função de particionamento e mapeia os filegroups para cada RANGE criado CREATE PARTITION SCHEME myRangePS AS PARTITION pfSales TO (test1fg, test2fg, test3fg, test4fg) ;
Tabela Particionada
Com a função e o esquema criados, agora é hora de criar a tabela e apontar para o esquema configurado anteriormente.
-- Cria uma tabela particionada e utiliza a coluna col1 para segmentar de acordo com o esquema
CREATE TABLE PartitionTable (id int PRIMARY KEY, col1 date)) ON myRangePS (col1) ; GO
Em resumo o processo consiste em criar os filegroups, definir a função, o esquema e criar a tabela utilizando o esquema.
A função de partição é responsável por determinar como uma tabela será particionada com base nos valores da coluna de partição. A tabela particionada é então criada no esquema de partição, que utiliza a função de partição para associar as partições lógicas aos grupos de arquivos físicos correspondentes.
Caso cada partição seja mapeada para um grupo de arquivos diferente, é possível alocar as partições em discos com diferentes velocidades de leitura e gravação, dependendo da frequência com que elas são acessadas. Além disso, as partições históricas podem ser configuradas como somente leitura, e as partições individuais podem ser copiadas e restauradas de forma independente, com base na criticidade dos dados armazenados em cada uma delas.
Algumas das vantagens do particionamento são:
· Melhora o desempenho das consultas: Ao particionar grandes tabelas, as consultas podem ser direcionadas para uma partição específica, o que pode aumentar significativamente a velocidade de recuperação dos dados.
· Facilita a manutenção e backup dos dados: As partições podem ser gerenciadas de forma independente, o que permite fazer backup, recuperação ou migração de uma partição sem afetar o restante dos dados.
· Melhora a escalabilidade: Particionar as tabelas permite distribuir os dados em vários dispositivos ou servidores, o que aumenta a capacidade de armazenamento e a capacidade de processamento.
Além disso, o particionamento também pode ser usado para organizar os dados por data, geolocalização, ou outras características, tornando mais fácil e rápido para os usuários encontrar e analisar os dados relevantes para as análises.
Autores:
Eder do Prado Borges
Leandro Lourenço Malaquias
Área: Analytics
Função: Engenheiro de Dados
Data: 30/03/2023
REFERENCIAS
http://leandrobarbieri.blogspot.com/2019/04/particionando-tabelas-em-sql-server.html
https://www.cathrinewilhelmsen.net/table-partitioning-in-sql-server/