DBCC SHOWCONTIG (Transact-SQL)

Exibe informações de fragmentação para os dados e índices da tabela ou exibição especificada.

Importante
Esse recurso será removido na próxima versão do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam. Use sys.dm_db_index_physical_stats.

Convenções de sintaxe Transact-SQL

Sintaxe

DBCC SHOWCONTIG 
[ ( 
    { table_name | table_id | view_name | view_id } 
    [ , index_name | index_id ] 
) ] 
    [ WITH 
        { 
         [ , [ ALL_INDEXES ] ] 
         [ , [ TABLERESULTS ] ] 
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ] 
         [ NO_INFOMSGS ]
         }
    ]

Argumentos

• table_name | table_id | view_name | view_id
  É a tabela ou exibição de verificação das informações de fragmentação. Se não for especificado, serão verificadas todas as tabelas e exibições indexadas no banco de dados atual. Para obter a ID de tabela ou de exibição, use a função OBJECT_ID.

• index_name | index_id
  É o índice de verificação das informações de fragmentação. Se não for especificado, a instrução processará o índice base da tabela ou exibição especificada. Para obter a ID de índice, use a exibição do catálogo sys.indexes.

• WITH
  Especifica opções para o tipo de informações retornado pela instrução DBCC.

• FAST
  Especifica se deve ser executada uma verificação rápida das informações mínimas de índice e de saída. Uma verificação rápida não lê as páginas em nível de dados nem folha do índice.

• ALL_INDEXES
  Exibe resultados para todos os índices das tabelas e exibições especificadas, até mesmo se um índice particular for especificado.

• TABLERESULTS
  Exibe resultados como um conjunto de linhas, com informações adicionais.

• ALL_LEVELS
  Mantido somente para compatibilidade com versões anteriores. Mesmo se ALL_LEVELS for especificado, só o nível folha de índice ou o nível de dados de tabela será processado.

• NO_INFOMSGS
  Suprime todas as mensagens informativas com níveis de severidade de 0 a 10.

Conjuntos de resultados

A tabela a seguir descreve as informações do conjunto de resultados.

Estatística	Descrição
Páginas Verificadas	Número de páginas na tabela ou no índice.
Extensões Verificadas	Número de extensões na tabela ou no índice.
Opções de Extensão	O número de vezes que a instrução DBCC foi movida de uma extensão para outra enquanto atravessava as páginas da tabela ou do índice.
Média de Páginas por Extensão	Número de páginas por extensão na cadeia de páginas.
Densidade da Verificação [Melhor Contagem: Contagem Real]	É uma porcentagem. É a relação entre a Melhor Contagem e a Contagem Real. Esse valor será 100 se tudo for contíguo; se ele for menor que 100, isso indicará que existe alguma fragmentação. A Melhor Contagem será o número ideal de alterações de extensão se tudo for vinculado contiguamente. A Contagem Real é o número real de alterações de extensão.
Fragmentação da Verificação Lógica	Porcentagem de páginas com problema retornadas da verificação de páginas de folha de um índice. Esse número não é relevante para heaps. Uma página com problema é uma página para a qual a próxima página física alocada ao índice não é a página apontada pelo ponteiro próxima páginaa na página de folha atual.
Fragmentação da Verificação de Extensão	Porcentagem de extensões com problemas na verificação de páginas de folha de um índice. Esse número não é relevante para heaps. Uma extensão com problema é a para a qual a extensão que contém a página atual de um índice não é fisicamente a próxima extensão depois da extensão que contém a página anterior de um índice. Observação Esse número não tem sentido quando o índice se estende a vários arquivos.
Média de Bytes Livres por Página	Número médio de bytes livres em páginas verificadas. Quanto maior o número, mais vazias ficarão as páginas. Números inferiores serão melhores se o índice não tiver muitas inserções aleatórias. Esse número também é afetado pelo tamanho da linha; uma linha grande pode gerar um número maior.
Densidade de Página Média (completa)	Densidade média da página, como uma porcentagem. Esse valor leva em consideração o tamanho de linha. Por isso, o valor é uma indicação mais precisa de quão cheias estão as páginas. Quanto maior a porcentagem, melhor.

Quando table_id e FAST são especificados, DBCC SHOWCONTIG retorna um conjunto de resultados apenas com as colunas seguintes.

• Páginas Verificadas

• Opções de Extensão

• Densidade da verificação [Melhor contagem: Contagem real]

• Fragmentação da Verificação de Extensão

• Fragmentação da Verificação Lógica

Quando TABLERESULTS é especificado, DBCC SHOWCONTIG retorna as seguintes colunas e também as nove colunas descritas na tabela anterior.

Estatística	Descrição
Nome do Objeto	Nome da tabela ou exibição processada.
ObjectId	ID do nome do objeto.
IndexName	Nome do índice processado. É NULL para um heap.
IndexId	ID do índice. É 0 para um heap.
Nível	Nível do índice. Nível 0 é o nível folha ou dados do índice. Nível é 0 para um heap.
Páginas	Número de páginas que compõem o nível do índice ou de todo o heap.
Linhas	Número de dados ou registros de índice no nível do índice. Para um heap, esse valor é o número de registros de dados em todo o heap. Para um heap, o número de registros retornados de sua função pode não corresponder ao número de linhas retornadas devido à execução de SELECT COUNT(*) relacionada ao heap. Isso porque uma linha pode conter vários registros. Por exemplo, em algumas situações de atualização, uma única linha de heap pode ter um registro de encaminhamento e um registro encaminhado como resultado de uma operação de atualização. Da mesma forma, a maior parte das linhas de LOB grandes é dividida em vários registros no armazenamento LOB_DATA.
MinimumRecordSize	Tamanho mínimo do registro no nível do índice ou de todo o heap.
MaximumRecordSize	Tamanho máximo do registro no nível do índice ou de todo o heap.
AverageRecordSize	Tamanho médio do registro no nível do índice ou de todo o heap.
ForwardedRecords	Número de registros encaminhados no nível do índice ou de todo o heap.
Extensões	Número de extensões no nível do índice ou de todo o heap.
ExtentSwitches	O número de vezes que a instrução DBCC foi movida de uma extensão para outra enquanto atravessava as páginas da tabela ou do índice.
AverageFreeBytes	Número médio de bytes livres em páginas verificadas. Quanto maior o número, mais vazias ficarão as páginas. Números inferiores serão melhores se o índice não tiver muitas inserções aleatórias. Esse número também é afetado pelo tamanho da linha; uma linha grande pode gerar um número maior.
AveragePageDensity	Densidade média da página, como uma porcentagem. Esse valor leva em consideração o tamanho de linha. Por isso, o valor é uma indicação mais precisa de quão cheias estão as páginas. Quanto maior a porcentagem, melhor.
ScanDensity	É uma porcentagem. É a relação entre a BestCount e a ActualCount. Esse valor será 100 se tudo for contíguo; se ele for menor que 100, isso indicará que existe alguma fragmentação.
BestCount	Será o número ideal de alterações de extensão se tudo for vinculado contiguamente.
ActualCount	É o número real de alterações de extensão.
LogicalFragmentation	Porcentagem de páginas com problema retornadas da verificação de páginas de folha de um índice. Esse número não é relevante para heaps. Uma página com problema é uma página para a qual a próxima página física alocada ao índice não é a página apontada pelo ponteiro próxima página na página de folha atual.
ExtentFragmentation	Porcentagem de extensões com problemas na verificação de páginas de folha de um índice. Esse número não é relevante para heaps. Uma extensão com problema é aquela para a qual a extensão que contém a página atual de um índice não é fisicamente a próxima extensão depois da extensão que contém a página anterior de um índice. Observação Esse número não tem sentido quando o índice se estende a vários arquivos.

Quando WITH TABLERESULTS e FAST forem especificados, o conjunto de resultados será o mesmo de quando WITH TABLERESULTS for especificado, exceto que as seguintes colunas terão valores nulos:

Linhas	Extensões
MinimumRecordSize	AverageFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
ForwardedRecords

Comentários

A instrução DBCC SHOWCONTIG atravessará a cadeia de páginas no nível folha do índice especificado quando index_id for especificado. Se apenas table_id for especificado ou se index_id for 0, serão verificadas as páginas de dados da tabela especificada. A operação somente requer um bloqueio de tabela de tentativa compartilhada (IS). Desse modo, podem ser executadas todas as atualizações e inserções, exceto as que exigirem um bloqueio de tabela exclusivo (X). Isso permite um equilíbrio entre a velocidade da execução e nenhuma redução da simultaneidade em relação ao número de estatísticas retornadas. Entretanto, se o comando estiver sendo usado apenas para medir a fragmentação, recomendamos o uso da opção WITH FAST para obter um desempenho melhor. Uma verificação rápida não lê as páginas em nível de dados ou folha do índice. A opção WITH FAST não se aplica a um heap.

O algoritmo para calcular fragmentação é mais preciso no SQL Server 2008 do que no SQL Server 2000. Como resultado, os valores de fragmentação parecerão mais altos. Por exemplo, no SQL Server 2000, uma tabela não será considerada fragmentada se suas páginas 11 e 13 tiverem a mesma extensão, mas a 12 não. Entretanto, para acessar essas duas páginas, são necessárias duas operações de E/S físicas; portanto, isso é contado como fragmentação no SQL Server 2008.

Restrições

DBCC SHOWCONTIG não exibe dados com os tipos de dados ntext, text e image. Isso ocorre porque os índices de texto (ID de índice 255 no SQL Server 2000) que armazenam dados de texto e imagem já não existem mais. Para obter mais informações sobre a ID de índice 255, consulte sys.sysindexes (Transact-SQL).

Da mesma forma, DBCC SHOWCONTIG não oferece suporte a alguns recursos novos. Por exemplo:

• Se a tabela ou o índice especificado for particionado, DBCC SHOWCONTIG exibirá apenas a primeira partição da tabela ou índice especificado.

• DBCC SHOWCONTIG não exibe informações de armazenamento de dados de estouro de linha e outros tipos de dados novos fora da linha, como nvarchar(max), varchar(max), varbinary(max) e xml.

• Não há suporte para índices de espaço pelo DBCC SHOWCONTIG.

Há suporte completo para todos os recursos novos pela exibição de gerenciamento dinâmica sys.dm_db_index_physical_stats (Transact-SQL).

Fragmentação de tabela

DBCC SHOWCONTIG determina se a tabela está muito fragmentada. A fragmentação da tabela ocorre pelo processo de modificações de dados (instruções INSERT, UPDATE e DELETE) efetuado na tabela. Como essas modificações não são distribuídas uniformemente entre as linhas da tabela, o preenchimento de cada página pode variar com o tempo. Para consultas que verificam parte de uma tabela ou toda ela, tal fragmentação de tabela pode causar leituras de página adicionais. Isso impede a verificação paralela de dados.

Quando um índice está bastante fragmentado, as opções a seguir são disponibilizadas para reduzir a fragmentação:

• Descartar e recriar um índice clusterizado.

  Recriar um índice clusterizado reorganiza os dados e gera páginas de dados cheias. O nível de preenchimento pode ser configurado usando a opção FILLFACTOR em CREATE INDEX. As desvantagens desse método são que o índice fica offline durante o ciclo de descarte ou recriação e que a operação é atômica. Se a criação de índice for suspensa, o índice não será recriado.

• Reordenar as páginas de nível folha do índice em uma ordem lógica.

  Use ALTER INDEX.REORGANIZE para reordenar as páginas de nível folha do índice em uma ordem lógica. Por essa operação ser online, o índice estará disponível quando a instrução estiver em execução. A operação também pode ser interrompida sem perda de trabalho concluído. A desvantagem desse método é que ele não reorganiza muito bem os dados como uma operação de recriação ou descarte de índice clusterizado.

• Reconstruir o índice.

  Use ALTER INDEX com REBUILD para reconstruir o índice. Para obter mais informações, consulte ALTER INDEX (Transact-SQL).

As estatísticas Média de bytes livres por página e Densidade de página média (completa) no conjunto de resultados indicam o preenchimento das páginas de índice. O número de Média de bytes livres por página deve ser baixo, e o número de Densidade de página média (completa), alto para um índice que não terá muitas inserções aleatórias. Descartar e recriar um índice com a opção de FILLFACTOR especificada pode melhorar as estatísticas. Da mesma forma, ALTER INDEX com REORGANIZE compactará um índice, levando em conta seu FILLFACTOR e melhorará as estatísticas.

Observação
Um índice que tem muitas inserções aleatórias e páginas muito cheias terá um número maior de separações de página. Isso causa mais fragmentação.

O nível de fragmentação de um índice pode ser determinado dos seguintes modos:

• Pela comparação dos valores de Opções de Extensão e Extensões Verificadas.

  O valor de Opções de Extensão deverá ser o mais próximo possível do de Extensões Verificadas. Essa taxa é calculada como o valor Densidade da Verificação. Esse valor deve ser o mais alto possível e pode ser melhorado reduzindo-se a fragmentação de índice.

  Observação
  Esse método não funcionará se o índice se estender a vários arquivos.

• Pela compreensão dos valores Fragmentação da Verificação Lógica e Fragmentação da Verificação de Extensão.

  Os valores Fragmentação da Verificação Lógica e, em uma extensão menor, Fragmentação da Verificação de Extensão são os melhores indicadores do nível de fragmentação de uma tabela. Esses dois valores devem ser o mais próximo possível de zero, embora um valor de 0 a 10% possa ser aceito.

  Observação
  O valor Fragmentação da Verificação de Extensão será alto se o índice se estender a vários arquivos. Para reduzir esses valores, você deve reduzir a fragmentação de índice.

Permissões

O usuário deve possuir a tabela ou ser um membro da função de servidor fixa sysadmin, da função de banco de dados fixa db_owner ou da função de banco de dados fixa db_ddladmin.

Exemplos

A. Exibindo informações de fragmentação de uma tabela

O exemplo a seguir exibe informações de fragmentação da tabela Employee.

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("HumanResources.Employee");
GO

B. Usando OBJECT_ID para obter a ID de tabela e sys.indexes para obter a ID de índice

O exemplo a seguir usa a exibição do catálogo OBJECT_ID e sys.indexes para obter a ID de tabela e a ID de índice para o índice AK_Product_Name da tabela Production.Product no banco de dados AdventureWorks .

USE AdventureWorks;
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('Production.Product')
SELECT @indid = index_id 
FROM sys.indexes
WHERE object_id = @id 
   AND name = 'AK_Product_Name'
DBCC SHOWCONTIG (@id, @indid);
GO

C. Exibindo um conjunto de resultados abreviado para uma tabela

O exemplo a seguir retorna um conjunto de resultados abreviado para a tabela Product no banco de dados AdventureWorks .

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("Production.Product", 1) WITH FAST;
GO

D. Exibindo o conjunto de resultados completo de todos os índices de todas as tabelas em um banco de dados

O exemplo a seguir retorna um conjunto de resultados de tabela completo de todos os índices em todas as tabelas no banco de dados AdventureWorks .

USE AdventureWorks;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO

E. Usando DBCC SHOWCONTIG e DBCC INDEXDEFRAG para desfragmentar os índices em um banco de dados

O exemplo a seguir mostra uma forma simples de desfragmentar todos os índices de um banco de dados que estão fragmentados acima de um limite declarado.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

Consulte também

Referência

ALTER INDEX (Transact-SQL)

CREATE INDEX (Transact-SQL)

DBCC (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

OBJECT_ID (Transact-SQL)

sys.indexes (Transact-SQL)