DBCC SHOWCONTIG (Transact-SQL)

Aplica-se a:SQL ServerInstância Gerenciada de SQL do Azure

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

Aplica-se a: SQL Server 2008 (10.0.x) e versões posteriores

Convenções de sintaxe de 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 a verificar em busca de 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 tabela ou exibir a ID, use a função OBJECT_ID.

index_name | index_id

O índice a verificar em busca de 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 de 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. Um exame rápido não lê as páginas de nível folha ou de dados 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 que ALL_LEVELS seja especificado, somente o nível folha do índice ou o nível de dados da tabela é 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.

Quando table_id e FAST estiverem especificados, DBCC SHOWCONTIG retornará um conjunto de resultados somente com as seguintes colunas:

• 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 colunas a seguir e as nove colunas descritas na tabela anterior.

Quando WITH TABLERESULTS e FAST são especificados, o conjunto de resultados é o mesmo de quando WITH TABLERESULTS é especificado, exceto pelas seguintes colunas, que terão valores nulos:

Comentários

A instrução DBCC SHOWCONTIG percorre a cadeia de páginas no nível folha do índice especificado quando index_id é especificado. Se apenas table_id for especificado ou se index_id for 0, as páginas de dados da tabela especificada serão verificadas. 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. No entanto, se você estiver usando o comando somente para avaliar a fragmentação, use a opção WITH FAST a fim de otimizar o desempenho. Um exame rápido não lê as páginas de nível folha ou de dados do índice. A opção WITH FAST não é aplicada ao heap.

Restrições

DBCC SHOWCONTIG não exibe dados com os tipos de dados ntext, text e image. Isso ocorre porque os índices de texto que armazenam dados de texto e imagem já não existem mais.

Além disso, DBCC SHOWCONTIG não é compatível com alguns novos recursos. Por exemplo:

• Se a tabela ou índice especificado for particionado, DBCC SHOWCONTIG exibirá somente a primeira partição dele.
• DBCC SHOWCONTIG não exibe informações de armazenamento de estouro de linha e outros novos tipos de dados fora de linha, como nvarchar(max), varchar(max), varbinary(max) e xml.
• Índices espaciais não são compatíveis com DBCC SHOWCONTIG.

Todos os novos recursos são totalmente compatíveis com a exibição de gerenciamento dinâmico 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 normalmente não são distribuídas de maneira uniforme entre as linhas da tabela, o preenchimento de cada página pode variar ao longo do 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 o exame paralelo 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 com 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 do índice for interrompida, ele 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 faz um trabalho tão satisfatório de reorganização de dados quanto uma operação de remoção ou recriação de índice clusterizado.

• Reconstruir o índice.

  Use ALTER INDEX com REBUILD para recompilar o índice. Para mais informações, consulte ALTERAR ÍNDICE (Transact-SQL).

O número da Média de bytes livres por página e Média de densidade de página (completa) no conjunto de resultados indicam o preenchimento de 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 média de página (completa) deve ser alto para um índice que não terá muitas inserções aleatórias. Remover e recriar um índice com a opção FILLFACTOR especificada pode melhorar as estatísticas. Da mesma forma, ALTER INDEX com REORGANIZE compactará um índice, levando em conta o respectivo FILLFACTOR, e melhorará as estatísticas.

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

• Comparando os valores de Opções de extensão e Extensões verificadas.

  O valor de Opções de extensão deve ser o mais próximo possível do valor de Extensões verificadas. Essa taxa é calculada como o valor de 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.

• Entendendo os valores de Fragmentação da Verificação Lógica e de Fragmentação da Verificação de Extensão.

  Os valores de Fragmentação da Verificação Lógica e, em um âmbito 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 de Fragmentação da Verificação de Extensão será alto se o índice abranger vários arquivos. Para reduzir esses valores, você deve reduzir a fragmentação de índice.

Permissões

O usuário precisa ser o proprietário da tabela ou ser 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. Exibir informações de fragmentação para uma tabela

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

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('HumanResources.Employee');
GO

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

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

USE AdventureWorks2022;
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. Exibir 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 AdventureWorks2022.

USE AdventureWorks2022;
GO
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;
GO

D. Exibir o conjunto de resultados completo para cada índice em cada tabela de 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 AdventureWorks2022.

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

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

O exemplo a seguir mostra uma maneira simples de desfragmentar todos os índices em 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

Confira também

• 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)