Funcionalidade de manutenção de estatísticas (autostats) no SQL Server

Traduções deste artigo Traduções deste artigo
ID do artigo: 195565 - Exibir os produtos aos quais esse artigo se aplica.
Expandir tudo | Recolher tudo

Neste artigo

Sumário

A funcionalidade de manutenção estatísticas recém-introduzidos, AutoStat, pode gerar sobrecarga indesejada em um sistema de produção executando uma das seguintes ações:
  • Iniciando atualizações estatísticas durante períodos de produção pesado.

    - ou -
  • Iniciando um número excessivamente alto dos processos de UPDATE STATISTICS em um determinado ponto no tempo.
O objetivo deste artigo é detalhar as condições sob as quais você pode esperar ver autostats gerado e UPDATE STATISTICS sendo executado em tabelas em um banco de dados.

Para obter informações sobre autostats no SQL Server 2000, consulte "Estatísticas usadas pelo otimizador de consulta no Microsoft SQL Server 2000" no seguinte site da MSDN:
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx


Observação Se você estiver usando o Microsoft SQL Server 2005, consulte o seguinte documento para obter informações sobre como as estatísticas são usadas pelo otimizador de consultas no SQL Server 2005:
http://technet.microsoft.com/en-us/library/cc966419.aspx

Mais Informações

Informações gerais

SQL Server utiliza um otimizador baseado em custo que pode ser extremamente confidenciais informações estatísticas que é fornecido em tabelas e índices. Sem informações de estatísticas corretas e atualizadas, SQL Server pode ser desafiado para determinar o melhor plano de execução para uma consulta específica.

Estatísticas mantidas em cada tabela no SQL Server para ajudar o otimizador na decisão com base no custo fazer incluem o:
  • Número de linhas na tabela.
  • Número de páginas usadas pela tabela.
  • Número de modificações feitas para as chaves da tabela desde a última atualização para as estatísticas.
Informações adicionais são armazenadas para índices, incluindo (para cada índice):
  • Uma histograma equi-altura na primeira coluna.
  • Densities em todos os prefixos de coluna.
  • Comprimento médio da chave.
Estatísticas sobre índices são criadas automaticamente sempre que um novo índice é criado. Além disso, agora é possível criar e manter as estatísticas em outras colunas bem.

Para manter as informações estatísticas de maneira tão atualizada quanto possível, o SQL Server apresenta AutoStat, que, por meio do SQL Server monitoramento das modificações de tabela, é capaz de atualizar automaticamente as estatísticas para uma tabela quando um determinado limite de alteração foi atingido. Além disso, o SQL Server apresenta automática-criar-estatísticas, que faz com que o servidor para gerar automaticamente todas as estatísticas necessárias para a otimização precisa de uma consulta específica.

Determinar quando AutoStat geração é iminente

Como mencionado acima, AutoStat atualizará automaticamente as estatísticas para uma tabela específica quando um "limite de alteração" foi atingido. A coluna sysindexes.rowmodctr mantém uma execução total de todas as modificações a uma tabela que, ao longo do tempo, pode afetar negativamente processo fazendo de decisão do processador de consulta. Esse contador é atualizado sempre que qualquer um dos seguintes eventos ocorrer:
  • Inserir uma única linha é feita.
  • Excluir uma única linha é feita.
  • Uma atualização para uma coluna indexada é feita.
Observação : TRUNCATE TABLE não atualiza rowmodctr.

Após a atualização estatísticas da tabela, o valor rowmodctr é redefinido como 0 e versão do esquema da tabela de estatísticas é atualizado.

Além disso, em situações em que plano de execução do procedimento armazenado é obtido do cache e esse plano é sensível à estatísticas, a versão de esquema de estatísticas será comparada para a versão atual. Se não houver novas estatísticas disponíveis, o plano para o procedimento armazenado será ser recompilado.

O algoritmo básico de estatísticas de atualização automática é:
  • Se a cardinalidade de uma tabela é menor que seis e a tabela estiver no banco de dados tempdb, auto-atualização com todos os seis modificações à tabela.
  • Se a cardinalidade de uma tabela for maior do que 6, mas menor ou igual a 500, atualizar status cada 500 modificações.
  • Se a cardinalidade de uma tabela é maior do que 500, atualizar estatísticas quando (500 + 20 % da tabela) alterações ocorreram.
  • Para variáveis de tabela, as alterações de cardinalidade não aciona as estatísticas de atualização automática.
Observação : nesse sentido mais estrito, SQL Server conta cardinalidade como o número de linhas na tabela.

Observação : juntamente com o cardinalidade, a seletividade do predicado também afeta AutoStats geração. Isso significa que as estatísticas talvez não seja atualizado afer todas as modificações de 500 se cardinalidade foram < 500 ou para todos os 20 % de alterações se cardinalidade foram > 500. Uma escala de fator (valor varia de 1 a 4, 1 e 4, inclusive) é gerada dependendo a seletividade e um produto desse fator e o número de alterações como obtido o algoritmo seria o número real de modificações necessárias para a geração de AutoStats.

O algoritmo acima pode ser summarised na forma de uma tabela:
_________________________________________________________________________________
 Table Type | Empty Condition | Threshold When Empty |Threshold When Not Empty 
_________________________________________________________________________________
 Permanent  | < 500 rows      | # of Changes >= 500  | # of Changes >= 500 + (20% of Cardinality)
___________________________________________________________________________
 Temporary  | < 6 rows        | # of Changes >= 6    | # of Changes >= 500 + (20% of Cardinality)
___________________________________________________________________________
Table
Variables   | Change in cardinality does not affect AutoStats generation.
___________________________________________________________________________
a seguir está dois exemplos para ajudar a demonstrar esse conceito:

Exemplo 1

Considere a tabela autores no banco de dados pubs, que contém 23 linhas e tem dois índices. O índice em cluster exclusivo, UPKCL_auidind, é indexado em uma coluna, au_id, e um índice que não estão em cluster composto, aunmind, foi criado em colunas au_lname e au_fname. Porque esta tabela contém menos de 500 linhas, AutoStat começará após terem ocorrido 500 alterações aos dados de tabela. As alterações podem ser uma das 500 ou mais insere, exclui, muda para uma coluna indexada, como au_lname ou qualquer combinação destes.

Você pode, portanto, prever quando UPDATE STATISTICS será iniciado, monitorando o valor sysindexes.rowmodctr, que será incrementado após cada atualização. Quando atingir ou exceder 500, você pode esperar UPDATE STATISTICS para ser iniciado.

Exemplo 2

Considere uma segunda tabela, t2, que tem uma cardinalidade de 1.000. Para tabelas com mais de 500 linhas, o SQL Server irá UPDATE STATISTICS quando (500 + 20 %) foram feitas alterações. Fazer o cálculo, 20 por cento de 1.000 é 200, portanto, você pode esperar ver AutoStat iniciar após aproximadamente 700 modificações foram feitas para a tabela.

Automatizando Autostats determinação

Para automatizar a determinação de quando AutoStat será executado, você pode pesquisar a tabela sysindexes e identificar quando as modificações de tabela estão atingindo o ponto de partida. Este é um algoritmo básico para fazer isso:
   if (sysindexes.rows > 500)
      if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
      hours) //500 change leeway
         begin
            disable autostats
            log autostats disable
         end
      else
         begin
            stats ok
         end
   else
      if (sysindexes.rowmodctr >= 425) //75 change leeway
         begin
            disable autostats
            log autostats disable
         end
				

Mais tarde você pode agendar um trabalho para fazer o seguinte:
  • Execute o UPDATE STATISTICS em todas as tabelas para o qual você foi forçado para desativá-los durante o dia.

    - e -
  • Reative AutoStat, porque o contador de modificação de cada tabela será foram redefinida para 0 quando UPDATE STATISTICS foi executado.

Controlar se UPDATE STATISTICS são executados em uma tabela

A solução mais óbvia para essa pergunta, quando AutoStat provou para ser Problematic, é desativar a geração de estatística automática, deixando assim, os administradores de banco de dados gratuito agendar UPDATE STATISTICS durante os períodos menos intrusivos. Você pode fazer isso usando a instrução UPDATE STATISTICS ou o procedimento armazenado sp_autostats. A sintaxe da instrução UPDATE STATISTICS é:
   UPDATE STATISTICS <table>...with NORECOMPUTE
				

A sintaxe para o procedimento armazenado sp_autostats é:
sp_autostats <nome_da_tabela>, <stats_flag>, <index_name>
onde <stats_flag> é "on" ou "desativado".

Você também pode usar sp_dboption para desativar a ocorrência automática de UPDATE STATISTICS ou CREATE STATISTICS em um nível por banco de dados:
sp_dboption <dbname>, 'estatísticas de atualização automática', < on | off >

- ou -

sp_dboption <dbname>, 'automaticamente criar estatísticas', < on | off >

Controlar o número de processos UPDATE STATISTICS simultâneos

Atualmente, curto de desabilitar AutoStat para tabelas específicas, ele não é possível configurar o número de instruções UPDATE STATISTICS automáticas que estão sendo executados simultaneamente (DCR 51539 tenha sido arquivado para isso). O servidor faz, no entanto, limitar o número de processos UPDATE STATISTICS simultâneos a quatro por processador.

Determinar quando Autostats estão sendo executados

Você pode usar o sinalizador de rastreamento 205 para informar quando um procedimento armazenado dependente de estatísticas está sendo recompilado como resultado de AutoStat. Esse sinalizador de rastreamento irá gravar as seguintes mensagens no log de erro:
1998-10-15 11:10:51.98 spid9 recompilar emitido: ProcName: sp_helpindex
LineNo: 75 StmtNo: 29
Quando o sinalizador de rastreamento 205 está habilitado, a seguinte mensagem também será colchete a mensagem AutoStat 8721 quando as estatísticas são atualizadas. A mensagem de abertura do colchete pode ser diferenciada pelo valor RowModCnt, que será maior que 0. O colchete de fechamento, após UPDATE STATISTICS, terá um valor RowModCnt de 0:
Mudança de esquema spid8 11:38:43.68 1998-10-15: Tbl Dbid: Objid 7:
RowModCnt 133575514: RowModLimit 60500: 60499
Para esta mensagem, "RowModCnt" é o número total de modificações para a tabela. "RowModLimit" é o limite que, quando excedido, resulta em uma execução de instrução UPDATE STATISTICS para a tabela.

Também é possível ativar o sinalizador de rastreamento 8721, que irá despejar informações no log de erro quando AutoStat tiver sido executado. Este é um exemplo do tipo de mensagem que você pode esperar para ver:
1998-10 a 14 16:22:13.21 spid13 AUTOSTATS: Tbl atualizado: [autores]
Linhas: 23 Mods: limite 501: 500 duração: 47ms UpdCount: 2
Para esta mensagem, "Mods" é o número total de modificações para a tabela. "Ligado" é o limite de modificação, "Duração" é a quantidade de tempo que a instrução UPDATE STATISTICS necessárias para concluir e "UpdCount" é a contagem de estatísticas atualizadas.

Você também pode usar o SQL Server Profiler para identificar quando instruções UPDATE STATISTICS estão sendo executadas. Para fazer isso, execute as seguintes etapas:
  1. No menu Profiler , clique em Ferramentas e, em seguida, clique em Opções .
  2. Na guia Geral , vá para eventos e, em seguida, selecione Todas as classes de evento .
  3. Definir um novo rastreamento e em eventos , selecione outros , selecione sub-event Estatísticas de actualizações automáticas .
Observação: Se estatísticas estão sendo atualizadas por AutoStat, um grande número de mensagens pode ser gravado no log de erros. Completamente Experimente esses sinalizadores de rastreamento antes de usá-los em qualquer produção ou servidor crítico caso contrário.

Bloqueios de esquema

SQL Server utiliza dois tipos de bloqueios de esquema, que são obtidas quando ele atualiza as estatísticas para uma tabela:
   Sch-S: Schema Stability Lock
   ----------------------------
   This lock ensures that a schema element, such as a table or index, will
   not be dropped while any session holds a schema stability lock on the
   schema element.

   Sch-M-UPD-STATS: Schema Modification Lock
   -----------------------------------------
   This is a non-blocking lock that is used by the system to ensure that
   only one automatic UPDATE STATISTICS process is run against a table at
   any given point in time. The sp_lock stored procedure will report this
   lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M.
				
você pode exibir esses bloqueios executando sp_lock ou selecionando da tabela syslockinfo.

Propriedades

ID do artigo: 195565 - Última revisão: sexta-feira, 2 de novembro de 2007 - Revisão: 7.6
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
Palavras-chave: 
kbmt kbinfo KB195565 KbMtpt
Tradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine Translation ou MT), não tendo sido portanto traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 195565

Submeter comentários

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com