Artigo: 924947 - �ltima revis�o: ter�a-feira, 20 de Novembro de 2007 - Revis�o: 2.5

SQL Server aumenta significativamente o espa�o n�o utilizado para algumas tabelas

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

Ver isen��o de responsabilidades para tradu��o autom�tica

Ver produtos para os quais este 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.

Este artigo descreve um cen�rio em que o Microsoft SQL Server aumenta significativamente o espa�o n�o utilizado para algumas tabelas. Em seguida, o artigo descreve os seguintes dois m�todos que pode utilizar para contornar este problema:

Converta tabelas de pilha para tabelas que utilizam �ndices agrupados.
Defina a op��o de configura��o abrir objectos para um valor elevado.

Voltar ao topo

Sintomas

Considere o seguinte cen�rio no SQL Server:

Uma inst�ncia do SQL Server tem uma ou mais utilizador bases de dados.
O n�mero cumulativo de tabelas destas bases de dados � maior do que o limiar espec�fico listados na tabela no final desta sec��o. Estas tabelas incluem a tabela de sistema, a tabela de utilizador e a tabela tempor�ria.
As aplica��es que est�o ligadas � inst�ncia do SQL Server a maior parte destas tabelas de refer�ncia.

Neste cen�rio, poder� detectar os seguintes sintomas:

Aumenta o espa�o que consomem estas bases de dados do utilizador a uma velocidade � muito mais r�pida do que a taxa normal. Consoante as defini��es Autogrowth para estas bases de dados do utilizador, os ficheiros de base de dados podem aumentar at� mais frequentemente que crescem casos normal.
A parte n�o utilizada o espa�o que consomem estas bases de dados do utilizador ser� maior do que a parte n�o utilizada normal.
Quando visualiza as propriedades das estruturas de armazenamento para estas bases de dados utilizador, tais como a estrutura de armazenamento de pilha, a �rvore de imagem de texto e o �ndice clusterizado, ver� muito espa�o n�o utilizado.
O espa�o reservado para as entradas de �ndice na tabela de sysindexes aumenta em m�ltiplos de 8. No entanto, o espa�o utilizado para as entradas de �ndice na tabela de sysindexes aumenta apenas um pequeno n�mero, tal como 1 ou 2. Isto �, para cada oito p�ginas que est�o atribu�das de uma nova extens�o, apenas algumas p�ginas s�o nunca utilizadas a partir dessa extens�o.

O limiar abordada neste artigo varia, consoante as seguintes condi��es:

A edi��o espec�fica do SQL Server que est� a utilizar
A mem�ria que configurou para SQL Server

A tabela seguinte lista estes limites para diferentes edi��es do SQL Server.

Reduzir esta tabelaExpandir esta tabela

edi��o	mem�ria (bytes)	limiar
Empresa/P/Developer	Mais 671,088,640	8192
Empresa/P/Developer	Menor que 671,088,640	2048
Empresa/P/Developer	Menor que 67,108,864	512
Empresa/P/Developer	Menor que 8,388,608	128
Pessoal/MSDE	N�o aplic�vel	128

A mem�ria corresponde � defini��o mem�ria do servidor m�ximo para a inst�ncia do SQL Server que est� a utilizar. Pode utilizar o procedimento armazenado do sistema de sp_configure para configurar a defini��o de mem�ria do servidor m�ximo .

Nota No SQL Server 2005, pode consultar a vista de cat�logo sys.dm_db_partition_stats para obter as informa��es de espa�o utilizado e as informa��es reservadas. No SQL Server 2000, pode consultar a tabela sysindexes para obter estas informa��es.

Voltar ao topo

Causa

SQL Server mant�m dois tipos de informa��es em cache para atribui��o de espa�o na mem�ria: atribui��o de cache e cache de espa�o livre. A cache de atribui��o cont�m o ID de extens�o para extens�es recentemente atribu�das para um �ndice espec�fico. A cache de espa�o livre cont�m n�meros de p�gina para p�ginas recentemente atribu�dos de estes atribu�do extens�es. Tamb�m contenha o estado das p�ginas. Se estes caches ficar vazias, os problemas que s�o descritos na sec��o "Sintomas" ocorrem.

Estes caches ficar vazias pelas seguintes raz�es:

Executar comandos diferentes que tem de executar sincroniza��es entre as informa��es de metadados na mem�ria e as informa��es no disco. Por exemplo, execute um dos seguintes comandos:
- DBCC UPDATEUSAGE
- ACTUALIZA��O DE ESTAT�STICAS
Os descritores objecto configuradas utilizando a op��o abrir objectos s�o utilizados. Por conseguinte, n�o descritores livres est�o dispon�veis.

Voltar ao topo

Como contornar

Para contornar este problema, utilize um dos seguintes m�todos:

Converta tabelas de pilha para tabelas que utilizam �ndices agrupados.
Defina a op��o de configura��o abrir objectos para um valor elevado.

Estes m�todos podem reduzir a taxa de crescimento para o espa�o utilizado � atribu�do a uma tabela. As seguintes sec��es descrevem estes dois m�todos. As sec��es tamb�m descrevem porque � que estes m�todos ajudarem a resolver o problema descrito neste artigo.

Voltar ao topo

Converter tabelas de pilha a tabelas que utilizam �ndices agrupados

Nota Depois de converter as tabelas de pilha a tabelas que utilizam �ndices agrupados, pode efectuar as opera��es de manuten��o do �ndice periodicamente para recuperar qualquer espa�o n�o utilizado desnecessariamente. Por exemplo, pode executar os seguintes comandos:

DBCC DBREINDEX
DBCC INDEXDEFRAG

segue � uma sequ�ncia t�pica das opera��es que ocorrem quando insere um registo numa tabela pilha:

1. tente inserir uma linha na tabela.
2. consulte a cache de espa�o livre para o �ndice de id 0 desta tabela.
3. determine se as p�ginas v�lidas presentes na cache de espa�o livre.
4. se Sim:
- 4.1. se n�o existir espa�o suficiente na p�gina, inserir dados na p�gina.
5. se n�o:
- 5.1. SQL Server tem de atribuir uma nova p�gina para esta linha.
- 5.2. consulte a cache de atribui��o de id de �ndice 0 desta tabela.
- 5.3. determine se extens�es v�lidas est�o presentes na cache de atribui��o.
- 5.4. se Sim:
  - 5.4.1. determine se qualquer uma das oito p�ginas na extens�o pode ser utilizado para este novo pedido de atribui��o de p�gina.
  - 5.4.2. se Sim:
    - 5.4.2.1. alocar uma p�gina de este j� atribu�do extens�o.
    - 5.4.2.2. v� para o passo 5.5.5.
  - 5.4.3. se n�o:
    - 5.4.3.1. v� para o passo 5.5.1.
- 5,5. se n�o:
  - 5.5.1. utilize os dados a partir do mapa de atribui��o global (GAM) e o mapa de atribui��o global (SGAM) secund�rio para localizar uma nova extens�o a ser atribu�do.
  - 5.5.2. atribuir uma extens�o de nova.
  - 5.5.3. utilize uma p�gina desta nova extens�o para satisfazer o pedido de atribui��o p�gina nova a partir do passo 5.1.
  - 5.5.4. preencha as informa��es sobre esta extens�o rec�m-alocado na cache de atribui��o.
  - 5.5.5. preencher as informa��es sobre este recentemente atribuir a p�gina na cache de espa�o livre.

Se a cache de atribui��o e a cache de espa�o vazios entre opera��es de inser��o posteriores, o SQL Server atribuir� novas p�ginas de extens�es de novas para que as opera��es de inser��o podem ter �xito. Quando os metadados da tabela � removido da mem�ria, a cache de atribui��o e a cache de espa�o livre tamb�m s�o removidos. Deste modo, da pr�xima vez que efectua uma opera��o de inser��o que referencia a tabela, estes caches estar�o vazias. Nesta situa��o, SQL Server tem de efectuar o passo 5 e, em seguida, passo 5.5. Este comportamento faz com que as extens�es recentemente atribu�das mostrar que oito p�ginas s�o atribu�das quando � utilizada apenas uma p�gina. Um cen�rio do pior, poder� ser desperdi�ados 56 kilobytes (KB) de espa�o para cada opera��o de inser��o que efectua na tabela.

Segue-se a sequ�ncia das opera��es que ocorrem quando inserir dados numa tabela que tenha um �ndice agrupado normal.

1. tente inserir uma linha na tabela.
2. atravessar a �rvore de B para localizar a p�gina de dados na qual do SQL Server deve armazenar a chave clustering.
3. determine se existe espa�o suficiente na p�gina da nova linha.
4. se Sim:
- 4.1. inserir dados nesta p�gina.
5. se n�o:
- 5.1. SQL Server tem de atribuir uma nova p�gina para esta linha.
- 5.2. consulte a cache de atribui��o de id de �ndice 1 desta tabela.
- 5.3. determine se extens�es v�lidas est�o presentes na cache de atribui��o.
- 5.4. se Sim:
  - 5.4.1. determina se � poss�vel utilizar qualquer uma das oito p�ginas na extens�o para este novo pedido de atribui��o de p�gina.
  - 5.4.2. se Sim:
    - 5.4.2.1. alocar uma p�gina de este j� atribu�do extens�o.
  - 5.4.3. se n�o:
    - 5.4.3.1. v� para o passo 5.5.1.
- 5,5. se n�o:
  - 5.5.1. utilize os dados da GAM e SGAM para encontrar uma nova extens�o a ser atribu�do.
  - 5.5.2. atribuir uma extens�o de nova.
  - 5.5.3. utilize uma p�gina desta extens�o para satisfazer o pedido de atribui��o p�gina nova a partir do passo 5.1.
  - 5.5.4. preencha as informa��es sobre esta extens�o rec�m-alocado na cache de atribui��o.

Se a cache de atribui��o e a cache de espa�o livre s�o limpos por motivos s�o descritos na sec��o "Causa", n�o � necess�rio imediato para atribuir uma nova p�gina entre opera��es de inser��o posteriores para esta tabela. Isto acontece, desde que os dados inseridos caibam na p�gina existente onde a chave de cluster espec�fica fisicamente tem de residir. Quando a p�gina de dados fica cheia e se as caches est�o vazias, SQL Server deve efectuar o passo 5 e, em seguida, passo 5.5. Como indica neste artigo, se utilizar um �ndice agrupado, o cen�rio em que s�o atribu�das novas p�ginas ocorre com muito menos frequ�ncia do que o cen�rio onde um registo � inserido numa tabela da pilha.

Seguem-se dois casos em que o problema ocorrer e a cache de atribui��o e a cache de espa�o livre est�o vazias. Pressup�e-se que permite que de esquema a tabela para 100 linhas caber na p�gina de dados.

Se a tabela tiver apenas uma estrutura de armazenamento de pilha, SQL Server pode atribuir uma extens�o de nova para cada opera��o de inser��o e utilize apenas uma p�gina nessa extens�o.
Se a tabela tem um �ndice agrupado, SQL Server pode atribuir uma nova extens�o para todas as opera��es de 100 inser��o e utilize apenas uma p�gina nessa extens�o.

Voltar ao topo

Definir objectos abertos op��o de configura��o para um valor elevado

Este problema ocorre principalmente porque o SQL Server utiliza todas as ranhuras na �rea de mem�ria designado com base na op��o abrir objectos . Quando detecta o problema descrito neste artigo, pode definir o valor desta op��o para acomodar quase todas as tabelas que v�o ser referenciadas na inst�ncia espec�fica do SQL Server.

Para determinar um valor de op��o de abrir objectos impedir� que este problema, siga estes passos:

Determine o n�mero total de tabelas que est�o presentes na inst�ncia espec�fica do SQL Server.

Nota As tabelas incluem tabelas de utilizador e tabelas de sistema. Tem de incluir tabelas de bases de dados do sistema.
Calcular um tamanho de mem�ria interm�dia que fornece espa�o para as tabelas tempor�rias e worktables consultas diferentes e as aplica��es podem utilizar.
Adicione o n�mero de tabelas de utilizador e tabelas de sistema para a mem�ria interm�dia para determinar o n�mero total de tabelas que pode ser acedido nesta inst�ncia do SQL Server. Este n�mero total � o valor que deve ser definido para a op��o de configura��o abrir objectos .

Uma vez que o consumo de mem�ria que � pr�-atribu�do durante o SQL Server arranque � com base neste valor, n�o tem de definir a op��o de configura��o abrir objectos para um valor muito elevado. Se definir a op��o abrir objectos para um valor muito elevado, mem�ria que foi originalmente utilizada para outros fins, como, por exemplo, para execu��o da consulta e de mem�rias interm�dias de dados, � utilizada para manter os metadados da tabela na �rea de mem�ria para a op��o abrir objectos .

N�o recomendamos que altere a configura��o de objectos abertos em circunst�ncias normais. Altere este valor apenas se tiver a certeza de que tenham o problema descrito neste artigo.

Voltar ao topo

Ponto Da Situa��o

A Microsoft confirmou que este � um problema nos produtos da Microsoft listados na sec��o "Aplica-se a".

Voltar ao topo

Mais Informa��o

Porque � que as defini��es de op��o objectos abertos provocar este problema

Sempre que uma consulta referencia um objecto espec�fico (tabela), o SQL Server carrega informa��es sobre o objecto na mem�ria. Todos os metadados do �ndice tem refer�ncias para as informa��es de objecto do SQL Server foi carregado na mem�ria. As caches de atribui��o que s�o discutidas neste artigo est�o associadas um �ndice espec�fico. A op��o abrir objectos controla o n�mero de descritores de que a inst�ncia do SQL Server pode carregar na mem�ria.

Quando o SQL Server � iniciado, SQL Server atribui um conjunto espec�fico de ranhuras ou descritores , mantenha a vers�o de mem�ria dos metadados de objecto. O n�mero de ranhuras atribu�dos depende da edi��o do SQL Server e a mem�ria que esteja configurado para o SQL Server, conforme descrito na sec��o ? Sintomas ?. Todas estas ranhuras originalmente fazem parte de uma lista de livres. Um algoritmo espec�fico � utilizado para atribuir ranhuras da lista de livres para os metadados do objecto. O algoritmo tem os seguintes dois modos de opera��o.

Modo 1

Depois do SQL Server ser iniciado, utiliza dos descritores de mem�ria da lista de livres sempre que um novo objecto � referenciado. SQL Server continua a utilizar todas as ranhuras existentes quando carrega metadados sobre um objecto que � referenciado numa consulta. Uma vez que ainda existem entradas de ranhura na lista de livres, SQL Server continua a utilizar estes ranhuras. Para ranhuras existentes, SQL Server n�o reutilizar mem�ria que foi utilizada por outro objecto.

Modo 2

Se um objecto est� a ser recentemente referenciado numa consulta e a lista de livres tornou-se vazio, SQL Server verifica a lista existente de todos os objectos para determinar se existe uma ranhura podem ser removidos cujo metadados de objecto de mem�ria. SQL Server tem de manter os metadados do objecto na ranhura apenas enquanto uma consulta referencia a tabela. Quando essa consulta estiver conclu�da, SQL Server pode reutilizar a ranhura se uma nova consulta faz refer�ncia a uma tabela diferente.

Quando os metadados de objecto � removido da mem�ria, tamb�m ser� removido todos os respectivos metadados do �ndice associado. Quando este comportamento ocorre, o SQL Server tamb�m perde a cache de atribui��o e a cache de espa�o livre. Em seguida, quando uma consulta diferente referencia novamente esta tabela, todas as caches de atribui��o est�o vazias. Por conseguinte, da pr�xima vez que o SQL Server tenta atribuir uma p�gina, SQL Server tenta atribuir uma nova extens�o.

Se continuar do SQL Server recuperar ranhuras que contenham metadados objecto at� que o SQL Server n�o � poss�vel recuperar quaisquer ranhuras, SQL Server aumenta o tamanho da lista de livres.

Voltar ao topo

Refer�ncias

Para obter mais informa��es sobre como utilizar o procedimento armazenado sp_configure para alterar op��es de configura��o, visite o seguinte Web site da Microsoft Developer Network (MSDN):

http://msdn2.microsoft.com/en-us/library/ms188787.aspx (http://msdn2.microsoft.com/en-us/library/ms188787.aspx)

Para mais informa��es sobre a op��o abrir objectos , visite o seguinte site da Web MSDN:

http://msdn2.microsoft.com/en-us/library/aa196701(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa196701(SQL.80).aspx)

Para obter mais informa��es sobre a op��o de configura��o de mem�ria do servidor m�ximo , visite o seguinte site da Web MSDN:

http://msdn2.microsoft.com/en-us/library/ms180797.aspx (http://msdn2.microsoft.com/en-us/library/ms180797.aspx)

Para mais informa��es sobre a arquitectura de tabela e o �ndice no SQL Server, visite o seguinte site da Web MSDN:

http://msdn2.microsoft.com/en-us/library/ms208356.aspx (http://msdn2.microsoft.com/en-us/library/ms208356.aspx)

Voltar ao topo

A informa��o contida neste artigo aplica-se a:

Microsoft SQL Server 2005 Developer Edition
Microsoft SQL 2005 Server Enterprise
Microsoft SQL Server 2005 Express Edition
Microsoft SQL Server 2005 Standard Edition
Microsoft SQL 2005 Server Workgroup
Microsoft SQL Server 2005 Enterprise X64 Edition
Microsoft SQL Server 2005 Standard X64 Edition
Microsoft SQL Server 2005 Enterprise Edition for Itanium Based Systems
Microsoft SQL Server 2005 Standard Edition for Itanium Based Systems
Microsoft SQL Server 2000 Developer Edition
Microsoft SQL Server 2000 Enterprise Edition
Microsoft SQL Server 2000 Personal Edition
Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 Workgroup Edition

Voltar ao topo

kbmt kbexpertiseadvanced kbtshoot kbsql2005engine kbprb KB924947 KbMtpt

Voltar ao topo

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

Voltar ao topo