DetailPage-MSS-KB

Base de Dados de Conhecimento

Artigo: 195565 - Última revisão: sexta-feira, 2 de Novembro de 2007 - Revisão: 7.6

 

Nesta página

Sumário

A funcionalidade de manutenção estatísticas recentemente introduzido, AutoStat, poderá gerar sobrecarga indesejável no sistema de produção efectuando uma das seguintes acções:
  • Iniciar estatísticas actualizações durante períodos de produção muito.

    - ou -
  • Iniciar um número demasiado alto de processos UPDATE STATISTICS num determinado ponto no tempo.
O objectivo deste artigo é para as condições nas quais pode esperar que autostats gerado e UPDATE STATISTICS a ser executada tabelas numa base de dados de detalhe.

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


Nota Se estiver a utilizar o Microsoft SQL Server 2005, consulte a seguinte técnica da Microsoft para obter informações sobre como as estatísticas são utilizadas pelo optimizador de consultas no SQL Server 2005:
http://technet.microsoft.com/en-us/library/cc966419.aspx (http://technet.microsoft.com/en-us/library/cc966419.aspx)

Mais Informação

Informações gerais

SQL Server utiliza um optimizador baseado em custos que pode ser extremamente sensível informações estatísticas que é fornecido em tabelas e índices. Sem informações estatísticas correctas e actualizadas, o SQL Server pode ser solicitado para determinar o melhor plano de execução de uma consulta específica.

Estatísticas mantidas em cada tabela no SQL Server para ajudar o optimizador baseado em custos decisão efectuar incluem o:
  • Número de linhas na tabela.
  • Número de páginas utilizadas pela tabela.
  • Número de modificações efectuadas às chaves da tabela desde a última actualização para as estatísticas.
Obter informações adicionais são armazenadas para índices, incluindo (para cada índice):
  • Uma histograma equivalência altura na primeira coluna.
  • Densities todos os prefixos de coluna.
  • Comprimento médio da chave.
Estatísticas sobre os índices são criadas automaticamente sempre que é criado um novo índice. Além disso, agora é possível criar e manter estatísticas em outras colunas bem.

Para manter as informações estatísticas de uma forma tão actualizada quanto possível, SQL Server introduz AutoStat, que, através do SQL Server monitorização de alterações da tabela, é capaz de actualizar automaticamente as estatísticas de uma tabela quando um determinado limite de alteração foi atingido. Além disso, SQL Server introduz automática-criar-estatísticas, que faz com que o servidor gerar automaticamente todas as estatísticas necessárias para a optimização precisa de uma consulta específica.

Determinar quando for iminente AutoStat geração

Tal como foi mencionado acima, AutoStat actualizará automaticamente as estatísticas de uma tabela específica quando atingiu um "limite de alteração". A coluna sysindexes.rowmodctr mantém a executar um total de todas as modificações a uma tabela que, ao longo do tempo, pode afectar negativamente decisão efectuar processo o processador de consultas. Este contador é actualizado sempre que um dos seguintes eventos ocorre:
  • Inserir uma única linha é efectuada.
  • Eliminar um única linha é efectuado.
  • É efectuada uma actualização para uma coluna indexada.
Nota : TRUNCATE TABLE não actualiza rowmodctr.

Depois de estatísticas das tabelas foram actualizadas, o valor de rowmodctr é reposto para 0 e é actualizado a versão do esquema da tabela de estatísticas.

Além disso, nas situações em que o plano de execução de um procedimento armazenado é retirado da cache e esse plano é sensível às estatísticas, a versão de esquema de estatísticas será comparada com a versão actual. Se não existirem novas estatísticas disponíveis, o plano para o procedimento armazenado vai ser novamente compilado.

O algoritmo de básico para as estatísticas de actualização automática é:
  • Se a cardinalidade de uma tabela é inferior a seis e a tabela está na base de dados tempdb, automática actualização com todos os seis modificações à tabela.
  • Se a cardinalidade de uma tabela é maior que 6, mas menor que ou igual a 500, actualizar o estado todas as 500 modificações.
  • Se a cardinalidade de uma tabela é maior do que 500, actualizar as estatísticas quando (500 + 20 por cento da tabela) ocorreram alterações.
  • Para variáveis de tabela, as alterações de cardinalidade não activa as estatísticas de actualização automática.
Nota : neste sentido strictest, SQL Server conta cardinalidade como o número de linhas na tabela.

Nota : adição a cardinalidade, a selecção do predicado também afecta AutoStats geração. Isto significa que as estatísticas pode não estar actualizado afer todas as 500 modificações se cardinalidade < 500 ou para todos os 20 % de alterações se cardinalidade foram > 500. É gerada uma escala de factor (intervalos de valores de 1 a 4, 1 e 4 inclusive) dependendo da selecção e um produto deste factor e o número de alterações como obtidos a partir do algoritmo seria o número real de modificações necessárias para geração de AutoStats.

O algoritmo acima pode ser summarised sob a 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.
___________________________________________________________________________
o seguem-se dois exemplos para ajudar a demonstrar este conceito:

Exemplo 1

Considere tabela autores a base de dados pubs, que contém 23 linhas e tiver dois índices. O índice clusterizado único, UPKCL_auidind, é indexado numa coluna, id_au, e foi criado um índice agrupado composto, aunmind, nas colunas at_nome e at_apelido. Uma vez que esta tabela contém menos de 500 linhas, AutoStat irá começar após tem ocorrido 500 alterações aos dados tabela. As alterações podem ser um dos 500 ou mais insere, elimina, muda para uma coluna indexada como at_nome ou qualquer combinação dos mesmos.

Por conseguinte, pode prever quando UPDATE STATISTICS serão iniciadas monitorizando o valor sysindexes.rowmodctr, que será incrementado após cada actualização. Quando atingir ou exceder 500, pode esperar que UPDATE STATISTICS a ser iniciado.

Exemplo 2

Considere uma segunda tabela, t2, que tem uma cardinalidade de 1.000. Para tabelas com maior do que 500 linhas, o SQL Server irá UPDATE STATISTICS quando (500 + 20 por cento) tiverem sido efectuadas alterações. Efectuar a matemática, 20 por cento de 1.000 é 200, pelo que pode esperar que consulte AutoStat iniciar depois de aproximadamente 700 modificações efectuadas a tabela.

Automatizar Autostats determinação

Para automatizar a determinação da quando AutoStat será executado, pode consultar a tabela sysindexes e identificar quando modificações tabela estão a ligar o ponto de partida. Segue-se um algoritmo de básico para o fazer:
   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
				

Posteriormente pode agendar uma tarefa para efectuar o seguinte:
  • Execute todas as tabelas para a qual foram forçado para desactivá-los durante o dia UPDATE STATISTICS.

    - e -
  • Reactive AutoStat, porque o contador de modificação de cada tabela irá foi reposto para 0 quando UPDATE STATISTICS foi executado.

Controlar se UPDATE STATISTICS são executados numa tabela

A solução mais óbvia para esta pergunta, quando tem comprovada AutoStat seja Problematic, consiste em desactivar a estatística de geração automática, deixando, assim, os administradores de base de dados livres para agendar UPDATE STATISTICS horas menos perturbadora. Pode efectuar este procedimento utilizando a instrução UPDATE STATISTICS ou o procedimento armazenado sp_autostats. A sintaxe para a instrução UPDATE STATISTICS é:
   UPDATE STATISTICS <table>...with NORECOMPUTE
				

A sintaxe para o procedimento armazenado sp_autostats é:
sp_autostats <table_name>, <stats_flag>, <index_name>
em que <stats_flag> é 'on' ou 'off'.

Pode também utilizar sp_dboption para desactivar a ocorrência automática de UPDATE STATISTICS ou CREATE STATISTICS num nível por base de dados:
sp_dboption <dbname>, 'Estatísticas de actualização automática', < no | desactivar >

- ou -

sp_dboption <dbname>, 'Estatísticas de criação automática', < no | desactivar >

Controlar o número de processos UPDATE STATISTICS concorrentes

Actualmente, falta de desactivação AutoStat para tabelas específicas, não é possível configurar o número de instruções UPDATE STATISTICS automáticas que estão a ser executado em simultâneo (DCR 51539 foi arquivado para este). O servidor tiver, no entanto, limitar o número de processos UPDATE STATISTICS simultâneos para quatro por processador.

Determinar quando Autostats estão a ser executado

Pode utilizar o sinalizador de rastreamento 205 para indicar quando um procedimento armazenado estatísticas dependente é sejam novamente compilado como resultado de AutoStat. Este sinalizador de rastreio escrever as seguintes mensagens de registo de erros:
1998-10-15 11:10:51.98 spid9 recompilação emitida: ProcName: sp_helpindex
LineNo: 75 StmtNo: 29
Quando o sinalizador de rastreamento 205 está activado, a seguinte mensagem será também parêntesis recto a mensagem AutoStat 8721 quando as estatísticas são actualizadas. A mensagem de abertura do Parêntese pode ser distinguida pelo valor RowModCnt, que é maior que 0. O parêntese de fecho depois STATISTICS UPDATE, terá um RowModCnt valor de 0:
1998-10-15 11:38:43.68 spid8 esquema alterar: Tbl Dbid: Objid 7:
RowModCnt 133575514: RowModLimit 60500: 60499
Para esta mensagem, "RowModCnt" é o número total de modificações à tabela. "RowModLimit" é o limite que, quando excedido, resulta numa execução de instrução UPDATE STATISTICS para a tabela.

Também é possível activar o sinalizador de rastreamento 8721, que vai copiar informações para o registo de erros quando AutoStat foi executado. Segue-se um exemplo do tipo de mensagem que pode esperar que consulte:
1998-10 a 14 16:22:13.21 spid13 AUTOSTATS: Tbl actualizado: [autores]
Linhas: 23 modificações: ligação 501: duração 500: 47ms UpdCount: 2
Para esta mensagem, "Modificações" é o número total de modificações à tabela. "Dependente" é o limite de modificação, "Duração" é a quantidade de tempo que a instrução UPDATE STATISTICS necessário para concluir e "UpdCount" é o número de estatísticas actualizadas.

Também pode utilizar o SQL Server Profiler para identificar quando instruções UPDATE STATISTICS estão a ser executadas. Para efectuar este procedimento, execute os seguintes passos:
  1. No menu Profiler , clique em Ferramentas e, em seguida, clique em Opções .
  2. No separador Geral , vá para eventos e, em seguida, seleccione Todas as classes de eventos .
  3. Definir um rastreio de novo e em eventos , seleccione informações , seleccione sub-event Estatísticas de actualizações automáticas .
NOTA: Se estatísticas estão a ser actualizadas por AutoStat, um grande número de mensagens pode ser escrito no registo de erros. Experimente exaustivamente estes sinalizadores de rastreio antes de utilizá-los em qualquer produção ou servidor crítico caso contrário.

Bloqueios de esquema

SQL Server utiliza dois tipos de bloqueios de esquema, sendo que ambos são retirados quando actualiza as estatísticas de 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.
				
pode visualizar estes bloqueios executando sp_lock ou seleccionando a tabela syslockinfo.

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áticaTraduçã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 revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática… erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 195565  (http://support.microsoft.com/kb/195565/en-us/ )
Partilhar
Opções de suporte adicionais
Fóruns de Suporte da Comunidade Microsoft
Contacte-nos directamente
Encontre um parceiro certificado Microsoft
Loja Microsoft