quando fazer

Um dos primeiros passos para validar a lentidão é analisar e recriar as estatísticas dos índices do banco de dados


Firebird

O otimizador no Firebird (e no InterBase) usa 2 critérios principais ao analisar uma consulta e escolher um plano de execução — o tamanho das tabelas (cardinalidade) e a seletividade dos índices (seletividade).

A seletividade dos índices é calculada como 1 dividido pelo número de valores únicos na coluna indexada (ou colunas). Por exemplo, se este é um índice em uma chave primária, e há 10 mil registros na tabela, então sua seletividade será igual a 1 / 10000 = 0.0001. E se a mesma tabela tem uma coluna com 10 valores diferentes, então o índice nesta coluna terá uma seletividade de 1 / 10 = 0.1. Para o otimizador, quanto menor a seletividade do índice, melhor.

Se o otimizador sempre determina o tamanho da tabela mais ou menos precisamente, então a seletividade dos índices não é recalculada automaticamente. Como resultado, o otimizador poderia estimar a seletividade que está longe da realidade. Então, a seletividade precisa ser recalculada manualmente.

O recálculo da seletividade dos índices ocorre quando eles são criados, quando ALTER INDEX ACTIVE, e quando SET STATISTICS INDEX nomedoindice.

Por exemplo, você criou uma tabela, criou índices, e então preencheu a tabela com dados. A seletividade de todos os índices desta tabela permanecerá 0 até você recalcular a seletividade.

Ou, você criou uma tabela, índices, preencheu-a com dados, fez um backup-restauração (quando restaurando, os índices são recriados, e a seletividade será relevante). E então você mudou 30-40% dos dados em algumas tabelas (adicionados, deletados ou modificados). E a seletividade também será irrelevante.

Você pode visualizar a seletividade armazenada dos índices com uma consulta (a seletividade dos índices inativos será igual a -1)

select i.rdb$relation_name, i.rdb$index_name, i.rdb$statistics from rdb$indices i -- do not show indices for system tables -- where coalesce (rdb$system_flag, 0) = 0 order by 1, 2

 Recalcular as estatísticas dos índices no banco de dados Firebird é uma maneira de melhorar o desempenho de suas consultas. As estatísticas informam ao Firebird quão seletivos são seus índices e ajudam a escolher o melhor plano de execução. Aqui estão os passos para recalcular as estatísticas dos índices no banco de dados Firebird:

• Conecte-se ao seu banco de dados usando uma ferramenta como isql, DBeaver, IBExpert, etc.

• Execute o comando SET STATISTICS INDEX ; para cada índice que deseja atualizar. Você também pode usar ALL em vez de para atualizar todos os índices no banco de dados. 

• Confirme suas alterações com o comando COMMIT;

• Você pode verificar as estatísticas atualizadas com o comando SHOW INDEX ; ou SHOW INDEX ALL;

É isso. Você recalculou as estatísticas dos índices no banco de dados Firebird. Você pode fazer isso periodicamente, especialmente após grandes alterações em seus dados, para manter seus índices eficientes.


Segue abaixo um comando que recalcula todas as estatísticas do banco Firebird

EXECUTE BLOCK AS
DECLARE VARIABLE IDX VARCHAR(60);
BEGIN
FOR SELECT RDB$INDEX_NAME
FROM RDB$INDICES
INTO :IDX
DO EXECUTE STATEMENT 'SET STATISTICS INDEX ' || :IDX;
END;

SQL server 

O otimizador de consulta do SQL Server usa estatísticas para criar planos de consulta que melhoram o desempenho das consultas. Para a maioria das consultas, o otimizador de consulta já gera as estatísticas necessárias para um plano de consulta de alta qualidade. Em alguns casos, é necessário criar estatísticas adicionais ou modificar o design da consulta para obter melhores resultados. Este artigo aborda os conceitos de estatísticas e fornece diretrizes para o uso eficiente de estatísticas de otimização de consulta.

Estatísticas

As estatísticas de otimização de consulta são BLOBs (objetos binários grandes) que contêm informações estatísticas sobre a distribuição de valores em uma ou mais colunas de uma tabela ou exibição indexada. O otimizador de consulta usa essas estatísticas para estimar a cardinalidade ou o número de linhas no resultado de consulta. Essas estimativas de cardinalidade permitem ao otimizador de consulta criar um plano de consulta de alta qualidade. Por exemplo, dependendo dos predicados, o Otimizador de Consulta pode usar estimativas de cardinalidade para escolher o operador Index Seek em vez de o operador Index Scan, que utiliza mais recursos, melhorando com isso o desempenho das consultas.

Cada objeto de estatísticas é criado em uma lista de uma ou mais colunas de tabela e inclui um histograma que exibe a distribuição de valores na primeira coluna. Os objetos de estatísticas em várias colunas também armazenam informações estatísticas sobre a correlação de valores entre as colunas. Essas estatísticas de correlação, ou densidades, são derivadas do número de linhas distintas de valores de coluna.

Histograma

Um histograma mede a frequência de ocorrência de cada valor distinto em um conjunto de dados. O Otimizador de Consulta calcula um histograma com base nos valores de coluna na primeira coluna de chave do objeto de estatísticas, selecionando os valores de coluna por amostragem estatística das linhas ou pela execução de uma verificação completa de todas as linhas na tabela ou na exibição. Se o histograma for criado com base em um conjunto amostrado de linhas, os totais armazenados para o número de linhas e o número de valores distintos são estimativas e não precisam ser números inteiros.

Para criar o histograma, o Otimizador de Consulta classifica os valores de colunas, calcula o número de valores que correspondem a cada valor de coluna distinta e agrega os valores de colunas em um máximo de 200 etapas de histograma contíguas. Cada etapa do histograma inclui uma gama de valores de coluna seguidos por um valor de coluna de limite superior. O intervalo inclui todos os possíveis valores de coluna entre valores de limite, excluindo-se os próprios valores de limite em si. O mais baixo dos valores de coluna classificados é o valor do limite superior da primeira etapa do histograma.


Vetor de densidade

Densidade são informações sobre o número de duplicatas em determinada coluna ou em uma combinação de colunas e ele é calculada como 1/(número de valores distintos). O Otimizador de Consulta usa densidades para aprimorar as estimativas de cardinalidade de consultas que retornam várias colunas da mesma tabela ou exibição indexada. Conforme a densidade diminui, aumenta a seletividade de um valor. Por exemplo, em uma tabela que representa carros, muitos carros têm o mesmo fabricante, mas cada carro tem um VIN (número de identificação de veículo) exclusivo. Um índice no VIN é mais seletivo que um índice no fabricante, porque o VIN tem densidade menor que o fabricante.


Para consultar as estatísticas do SQL Server podemos usar o comando abaixo

SELECT OBJECT_NAME(s.object_id) AS object_name, COL_NAME(sc.object_id, sc.column_id) AS column_name, s.name AS statistics_name FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id WHERE s.name like '_WA%' ORDER BY s.name;

Já para atualizar as estatísticas de uma determinada tabela podemos usar o comando

UPDATE STATISTICS table_or_indexed_view_name

 Ou podemos recalcular as estatísticas de todas as tabelas com o comando

sp_updatestats

Referencias

Estatísticas - SQL Server | Microsoft Learn

5 Primeiros Passos Para Melhorar o Performance do Firebird


  • Sem rótulos