ID do artigo: 195565 - �ltima revis�o: sexta-feira, 2 de novembro de 2007 - Revis�o: 7.6

Funcionalidade de manuten��o de estat�sticas (autostats) no SQL Server

Clique aqui para exibir o artigo traduzido e o artigo original em ingl�s, lado a lado.

Exibir Aviso de Isen��o de Tradu��o Autom�tica

Exibir os produtos aos quais esse artigo se aplica.

Dica do SistemaEste artigo aplica-se a um sistema operativo diferente do que est� a utilizar. Foi desactivado o conte�do do artigo, que pode n�o ser relevante para si.

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 (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 (http://technet.microsoft.com/en-us/library/cc966419.aspx)

Voltar para o in�cio

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.

Voltar para o in�cio

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:

Voltar para o in�cio

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.

Voltar para o in�cio

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.

Voltar para o in�cio

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.

Voltar para o in�cio

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 >

Voltar para o in�cio

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.

Voltar para o in�cio

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:

No menu Profiler , clique em Ferramentas e, em seguida, clique em Op��es .
Na guia Geral , v� para eventos e, em seguida, selecione Todas as classes de evento .
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.

Voltar para o in�cio

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.

Voltar para o in�cio

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

Voltar para o in�cio

kbmt kbinfo KB195565 KbMtpt

Voltar para o in�cio

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� (http://support.microsoft.com/kb/195565/en-us/ )

Voltar para o in�cio