DBCC SHOWCONTIG (Transact-SQL)

Aplica-se a:SQL Server Azure SQL Managed Instance

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

Importante

Esse recurso será removido em uma versão futura do SQL Server. Evite usar esse recurso em novos trabalhos de desenvolvimento e planeje modificar aplicativos que atualmente usam esse recurso. Use sys.dm_db_index_physical_stats em vez disso.

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

Transact-SQL convenções de sintaxe

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 para verificar informações de fragmentação. Se não for especificado, todas as tabelas e exibições indexadas no banco de dados atual serão verificadas. Para obter a tabela ou a ID de visualização, utilize a função OBJECT_ID.

index_name | index_id

O índice para verificar informações de fragmentação. Se não for especificada, a instrução processa o índice base para a tabela ou exibição especificada. Para obter a ID do índice, use o sys.indexes exibição de catálogo.

COM

Especifica opções para o tipo de informação retornada pela instrução DBCC.

RÁPIDO

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

ALL_INDEXES

Exibe resultados para todos os índices das tabelas e exibições especificadas, mesmo que um determinado índice seja especificado.

TABELARESULTADOS

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

ALL_LEVELS

Mantido apenas para compatibilidade com versões anteriores. Mesmo que ALL_LEVELS seja especificado, apenas o nível de folha de índice ou o nível de dados da tabela é processado.

NO_INFOMSGS

Suprime todas as mensagens informativas com níveis de gravidade de 0 a 10.

Conjuntos de resultados

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

Estatística	Descrição
Páginas digitalizadas	Número de páginas na tabela ou índice.
Extensões digitalizadas	Número de extensões na tabela ou índice.
Extensão muda	Número de vezes que a instrução DBCC passou de uma extensão para outra enquanto a instrução percorria as páginas da tabela ou índice.
Páginas médias por extensão	Número de páginas por extensão na cadeia de páginas.
densidade de varredura [melhor contagem: contagem real]	Uma percentagem. É a relação de melhor contagem para contagem real. Este valor é 100 se tudo for contíguo; Se este valor for inferior a 100, existe alguma fragmentação. Best Count é o número ideal de mudanças de extensão se tudo estiver contíguamente ligado. Contagem Real é o número real de alterações de extensão.
de fragmentação da varredura lógica	Porcentagem de páginas fora de ordem retornadas da verificação das páginas de folha de um índice. Este número não é relevante para pilhas. Uma página fora de ordem é uma página para a qual a próxima página física alocada ao índice não é a página apontada pelo ponteiro dede próxima pag e na página folha atual.
Fragmentação da varredura	Porcentagem de extensões fora de ordem na varredura das páginas de folha de um índice. Este número não é relevante para pilhas. Uma extensão fora de ordem é aquela para a qual a extensão que contém a página atual de um índice não é fisicamente a extensão seguinte após a extensão que contém a página anterior de um índice. Nota: Este número não faz sentido quando o índice abrange vários ficheiros.
Média de Bytes Grátis por Página	Número médio de bytes livres nas páginas digitalizadas. Quanto maior o número, menos cheias são as páginas. Números mais baixos são melhores se o índice não tiver muitas inserções aleatórias. Este número também é afetado pelo tamanho da linha; Um tamanho de linha grande pode causar um número maior.
Densidade média da página (completa)	Densidade média de páginas, em percentagem. Esse valor leva em conta o tamanho da linha. Portanto, o valor é uma indicação mais precisa de quão cheias suas páginas estão. Quanto maior a porcentagem, melhor.

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

Páginas digitalizadas
Extensão muda
densidade de varredura [melhor contagem:contagem real]
Fragmentação da varredura
de fragmentação da varredura 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 vista processada.
ObjectId	ID do nome do objeto.
IndexName	Nome do índice processado. NULL para uma pilha.
IndexId	ID do índice. 0 para uma pilha.
Nível	Nível do índice. Nível 0 é o nível folha, ou dados, do índice. O nível é 0 para uma pilha.
Páginas	Número de páginas que compõem esse nível do índice ou heap inteiro.
Linhas	Número de dados ou registos de índice a esse nível do índice. Para uma pilha, esse valor é o número de registros de dados em toda a pilha. Para uma pilha, o número de registros retornados dessa função pode não corresponder ao número de linhas retornadas executando um `SELECT COUNT(*)` na pilha. Isso ocorre porque uma linha pode conter vários registros. Por exemplo, em algumas situações de atualização, uma única linha de pilha pode ter um registro de encaminhamento e um registro encaminhado como resultado da operação de atualização. Além disso, a maioria das linhas LOB grandes são divididas em vários registros no armazenamento LOB_DATA.
MinimumRecordSize	Tamanho mínimo do registo nesse nível do índice ou da pilha inteira.
MaximumRecordSize	Tamanho máximo do registo nesse nível do índice ou da pilha inteira.
AverageRecordSize	Tamanho médio do registro nesse nível do índice ou pilha inteira.
ForwardedRecords	Número de registros encaminhados nesse nível do índice ou heap inteiro.
Extensões	Número de extensões nesse nível do índice ou pilha inteira.
ExtentSwitches	Número de vezes que a instrução DBCC passou de uma extensão para outra enquanto a instrução percorria as páginas da tabela ou índice.
MédiaFreeBytes	Número médio de bytes livres nas páginas digitalizadas. Quanto maior o número, menos cheias são as páginas. Números mais baixos são melhores se o índice não tiver muitas inserções aleatórias. Este número também é afetado pelo tamanho da linha; Um tamanho de linha grande pode causar um número maior.
AveragePageDensity	Densidade média de páginas, em percentagem. Esse valor leva em conta o tamanho da linha. Portanto, o valor é uma indicação mais precisa de quão cheias suas páginas estão. Quanto maior a porcentagem, melhor.
ScanDensity	Uma percentagem. É a proporção BestCount para ActualCount. Este valor é 100 se tudo for contíguo; Se este valor for inferior a 100, existe alguma fragmentação.
BestCount	O número ideal de extensão muda se tudo estiver ligado de forma contígua.
ActualCount	O número real de extensão muda.
LogicalFragmentation	Porcentagem de páginas fora de ordem retornadas da verificação das páginas de folha de um índice. Este número não é relevante para pilhas. Uma página fora de ordem é 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 folha atual.
ExtentFragmentation	Porcentagem de extensões fora de ordem na varredura das páginas de folha de um índice. Este número não é relevante para pilhas. Uma extensão fora de ordem é aquela para a qual a extensão que contém a página atual de um índice não é fisicamente a extensão seguinte após a extensão que contém a página anterior de um índice. Nota: Este número não faz sentido quando o índice abrange vários ficheiros.

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

Linhas	Extensões
MinimumRecordSize	MédiaFreeBytes
MaximumRecordSize	AveragePageDensity
AverageRecordSize	ExtentFragmentation
ForwardedRecords

Comentários

A instrução DBCC SHOWCONTIG atravessa a cadeia de páginas no nível de 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 requer apenas um bloqueio de tabela de intenção compartilhada (IS). Desta forma, todas as atualizações e inserções podem ser realizadas, exceto aquelas que exigem um bloqueio de tabela exclusivo (X). Isso permite uma compensação entre a velocidade de execução e nenhuma redução na simultaneidade em relação ao número de estatísticas retornadas. No entanto, se o comando estiver sendo usado apenas para avaliar a fragmentação, recomendamos que você use a opção WITH FAST para obter um desempenho ideal. Uma verificação rápida não lê as páginas de folha ou de nível de dados do índice. A opção WITH FAST não se aplica a uma pilha.

Restrições

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

Além disso, DBCC SHOWCONTIG não suporta alguns novos recursos. Por exemplo:

Se a tabela ou í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 estouro de linha e outros novos tipos de dados fora da linha, como nvarchar(max), varchar(max), varbinary(max)e xml.
Os índices espaciais não são suportados pelo DBCC SHOWCONTIG.

Todos os novos recursos são totalmente suportados pelo sys.dm_db_index_physical_stats (Transact-SQL) visualização de gerenciamento dinâmico.

Fragmentação da tabela

DBCC SHOWCONTIG determina se a tabela está fortemente fragmentada. A fragmentação da tabela ocorre através do processo de modificações de dados (instruções INSERT, UPDATE e DELETE) feitas na tabela. Como essas modificações normalmente não são distribuídas igualmente entre as linhas da tabela, a plenitude de cada página pode variar ao longo do tempo. Para consultas que verificam parte ou a totalidade de uma tabela, essa fragmentação da tabela pode causar leituras de página adicionais. Isso dificulta a varredura paralela de dados.

Quando um índice está fortemente fragmentado, as seguintes opções estão disponíveis para reduzir a fragmentação:

Solte e recrie um índice clusterizado.

A recriação de um índice clusterizado reorganiza os dados e causa páginas de dados completas. O nível de plenitude pode ser configurado usando a opção FILLFACTOR em CREATE INDEX. As desvantagens desse método são que o índice está offline durante o ciclo de queda ou recriação e que a operação é atômica. Se a criação do índice for interrompida, o índice não será recriado.
Reordene as páginas de nível de folha do índice em uma ordem lógica.

Use ALTER INDEX...REORGANIZE para reordenar as páginas de nível de folha do índice em uma ordem lógica. Como essa operação é uma operação online, o índice está disponível quando a instrução está em execução. A operação também é interruptível sem perda de trabalho concluído. A desvantagem desse método é que o método não faz um trabalho tão bom de reorganizar os dados quanto uma operação de queda de índice clusterizado ou recriação.
Reconstrua o índice.

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

A Bytes médios livres por página e Densidade média de páginas (completa) estatística no conjunto de resultados indicam a plenitude das páginas de índice. O Bytes médios livres por página número deve ser baixo e o Densidade média de páginas (completa) número deve ser alto para um índice que não terá muitas inserções aleatórias. Soltar e recriar um índice com a opção FILLFACTOR especificada pode melhorar as estatísticas. Além disso, ALTER INDEX com REORGANIZE compactará um índice, tendo em conta a sua 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 divisões de página. Isso causa mais fragmentação.

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

Comparando os valores de Interruptores de Extensão e Extensões Digitalizadas.

O valor de Extent Switches deve ser o mais próximo possível do valor de Extents Scanned. Esta relação é calculada como o valor densidade de varredura. Este valor deve ser o mais elevado possível e pode ser melhorado reduzindo a fragmentação do índice.

Observação

Esse método não funciona se o índice abrange vários arquivos.
Compreendendo de fragmentação da varredura lógica e os valores de da fragmentação da varredura de extensão.

de Fragmentação de Varredura Lógica e, em menor grau, valores de de Fragmentação de Varredura de Extensão são os melhores indicadores do nível de fragmentação de uma tabela. Ambos os valores devem ser o mais próximo possível de zero, embora um valor de 0 a 10% possa ser aceitável.

Observação

O valor Fragmentação da varredura de extensão será alto se o índice abranger vários arquivos. Para reduzir esses valores, você deve reduzir a fragmentação do índice.

Permissões

O usuário deve 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

Um. Exibir informações de fragmentação para uma tabela

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

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

B. Use OBJECT_ID para obter o ID da tabela e sys.indexes para obter o ID do í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 de índice para o índice de AK_Product_Name da tabela Production.Product no banco de dados AdventureWorks2025.

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 AdventureWorks2025.

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

D. Exibir o conjunto de resultados completo para cada índice em cada tabela em um banco de dados

O exemplo a seguir retorna um conjunto de resultados de tabela completa para cada índice em cada tabela no banco de dados AdventureWorks2025.

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

E. Use 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 fragmentado 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

Ver também

Comentários

Esta página foi útil?

Last updated on 2025-03-05

Partilhar via

DBCC SHOWCONTIG (Transact-SQL)

Sintaxe

Argumentos

table_name | table_id | view_name | view_id

index_name | index_id

COM

RÁPIDO

ALL_INDEXES

TABELARESULTADOS

ALL_LEVELS

NO_INFOMSGS

Conjuntos de resultados

Comentários

Restrições

Fragmentação da tabela

Permissões

Exemplos

Um. Exibir informações de fragmentação para uma tabela

B. Use OBJECT_ID para obter o ID da tabela e sys.indexes para obter o ID do índice

C. Exibir um conjunto de resultados abreviado para uma tabela

D. Exibir o conjunto de resultados completo para cada índice em cada tabela em um banco de dados

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

Ver também

Comentários

Recursos adicionais