Trabalhando com sys.dm_db_index_physical_stats no SQL Server 2008 – Final

Fragmentação da extensão

É a porcentagem de extensões com problema nas páginas de folha de um heap. Uma extensão com problema é aquela para a qual a extensão que contém a página atual de um heap não é fisicamente a próxima extensão depois da extensão que contém a página anterior.

O valor de avg_fragmentation_in_percent deve ser o mais próximo possível de zero para um máximo desempenho. Porém, valores de 0% a 10% podem ser aceitáveis. Podem ser usados todos os métodos de redução de fragmentação, como reconstruir, reorganizar ou recriar, para reduzir esses valores.

Reduzindo a fragmentação em um índice

Quando um índice estiver fragmentado de forma que a fragmentação afete o desempenho da consulta, há três opções para reduzir a fragmentação:

• Descartar e recriar o índice clusterizado.

  Recriar um índice clusterizado redistribui os dados e resulta em páginas de dados completas. 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 permanece offline durante o ciclo de descarte e recriação e que a operação é atômica. Se a criação de índice for suspensa, o índice não será recriado.

• Usar ALTER INDEX REORGANIZE, a substituição de DBCC INDEXDEFRAG, para reordenar as páginas de nível folha do índice em uma ordem lógica. Como essa operação é online, o índice permanecerá disponível enquanto a instrução estiver sendo executada. A operação também pode ser interrompida sem perda do trabalho já concluído. A desvantagem desse método é que ele não reorganiza muito bem os dados como uma operação de reconstrução de índice e não atualiza as estatísticas.
• Usar ALTER INDEX REBUILD, a substituição de DBCC DBREINDEX, para reconstruir o índice online ou offline.

A fragmentação sozinha não é uma razão suficiente para reorganizar ou reconstruir um índice. O efeito principal da fragmentação é que ela reduz a velocidade da taxa de transferência read-ahead da página durante os exames de índice. O resultado é tempos de resposta mais lentos. Se a carga de trabalho da consulta em uma tabela ou índice fragmentado não envolver exames porque a carga de trabalho é composta por pesquisas singleton, a remoção da fragmentação poderá não ter efeito algum.

Observação:
A execução de DBCC SHRINKFILE ou DBCC SHRINKDATABASE poderá apresentar fragmentação se um índice for movido parcial ou completamente durante a operação de redução. Por isso, se uma operação de remoção precisar ser executada, você não deverá fazer isso após a remoção da fragmentação.

Reduzindo fragmentação em um heap

Para reduzir a extensão da fragmentação de um heap, crie um índice clusterizado na tabela e descarte o índice. Isso redistribui os dados enquanto o índice clusterizado é criado. E também otimiza o máximo possível esse processo, enquanto considera a distribuição de espaço livre disponível no banco de dados. Quando o índice clusterizado é descartado para a recriação de um heap, os dados não são movidos e permanecem no mesmo lugar. Compactando dados de objetos grandes

Por padrão, a instrução ALTER INDEX REORGANIZE compacta páginas que contêm dados de objetos (LOB) grandes. Como as páginas LOB não são desalocadas quando vazias, a compactação desses dados poderá melhorar o espaço em disco se vários dados LOB tiverem sido excluídos ou se uma coluna LOB foi descartada.

Reorganizar um índice clusterizado especificado compacta todas as colunas LOB contidas no índice clusterizado. Reorganizar um índice não clusterizado compacta todas as colunas LOB não-chave (incluídas) no índice. Quando ALL é especificado na instrução, todos os índices associados à tabela ou exibição especificada são reorganizados. Além disso, todas as colunas LOB associadas ao índice clusterizado, tabela subjacente ou índice não clusterizado com colunas incluídas são compactadas.

Avaliando o uso do espaço em disco

A coluna avg_page_space_used_in_percent indica que a página está cheia. Para se obter um ótimo uso do espaço em disco, esse valor deverá estar perto de 100% para um índice que não terá muitas inserções aleatórias. Entretanto, 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. Por isso, para reduzir as divisões de página, o valor deve ser menor que 100%. A recriação de um índice com a opção FILLFACTOR especificada permite que o preenchimento da página seja alterado para atender ao padrão de consulta do índice. Avaliando fragmentos de índice

Um fragmento é composto de páginas de folha fisicamente consecutivas no mesmo arquivo de uma unidade de alocação. Um índice tem pelo menos um fragmento. O máximo de fragmentos que um índice pode ter é igual ao número de páginas no nível de folha do índice. Fragmentos maiores indicam que menos E/S de disco é necessária para ler o mesmo número de páginas. Por isso, quanto maior o valor avg_fragment_size_in_pages, melhor o desempenho de exame de intervalo. Os valores avg_fragment_size_in_pages e avg_fragmentation_in_percent são inversamente proporcionais entre si. Por isso, a reconstrução ou a reorganização de um índice deve reduzir a quantidade de fragmentação e aumentar o tamanho do fragmento.

Permissões

Requer as seguintes permissões:

• Permissão CONTROL no objeto especificado no banco de dados.
• Permissão VIEW DATABASE STATE para retornar informações sobre todos os objetos do banco de dados especificado, usando o curinga de objeto @object_id= NULL.
• Permissão VIEW SERVER STATE para retornar informações sobre todos os banco de dados usando o curinga de banco de dados @database_id = NULL.

Conceder VIEW DATABASE STATE permite que todos os objetos no banco de dados sejam retornados, independentemente de qualquer permissão CONTROL negada a objetos específicos.

Negar VIEW DATABASE STATE impede que todos os objetos do banco de dados sejam retornados, independentemente de qualquer permissão CONTROL concedida a objetos específicos. Além disso, quando o curinga do banco de dados @database_id= NULL é especificado, o banco de dados é omitido.

Para obter mais informações, consulte Exibições e funções de gerenciamento dinâmico (Transact-SQL).

A. Retornando informações sobre uma tabela especificada

O exemplo a seguir retorna as estatísticas de tamanho e fragmentação de todos os índices e partições da tabela Person.Address do banco de dados AdventureWorks. O modo de exame é definido como 'LIMITED' para um melhor desempenho e para limitar as estatísticas retornadas. A execução desta consulta requer, no mínimo, a permissão CONTROL na tabela Person.Address.

DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks'); SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address'); IF @db_id IS NULL BEGIN; PRINT N'Invalid database'; END; ELSE IF @object_id IS NULL BEGIN; PRINT N'Invalid object'; END; ELSE BEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED'); END; GO

B. Retornando informações sobre um heap

O exemplo a seguir retorna todas as estatísticas do heap dbo.DatabaseLog no banco de dados AdventureWorks. Como a tabela contém dados LOB, uma linha é retornada para a unidade de alocação LOB_DATA, além da linha retornada para IN_ROW_ALLOCATION_UNIT que está armazenando as páginas de dados do heap. A execução desta consulta requer, no mínimo, a permissão CONTROL na tabela dbo.DatabaseLog.

DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks'); SET @object_id = OBJECT_ID(N'AdventureWorks.dbo.DatabaseLog'); IF @object_id IS NULL BEGIN; PRINT N'Invalid object'; END; ELSE BEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED'); END; GO

C. Retornando informações de todos os bancos de dados

O exemplo a seguir retorna todas as estatísticas de todas as tabelas e índices na instância do SQL Server por meio da especificação do curinga NULL para todos os parâmetros. A execução desta consulta requer a permissão VIEW SERVER STATE.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL); GO

D. Usando sys.dm_db_index_physical_stats em um script para reconstruir ou reorganizar índices

O exemplo a seguir reorganiza ou reconstrói automaticamente em um banco de dados todas as partições que têm uma fragmentação média de 10%. A execução desta consulta requer a permissão VIEW DATABASE STATE. Este exemplo especifica DB_ID como o primeiro parâmetro sem especificar um nome de banco de dados. Um erro será gerado se o banco de dados atual tiver um nível de compatibilidade de 80 ou menos. Para resolver o erro, substitua DB_ID() por um nome de banco de dados válido. Para obter mais informações sobre os níveis de compatibilidade do banco de dados, consulte sp_dbcmptlevel (Transact-SQL).

-- Ensure a USE <databasename> statement has been executed first. SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; -- Open the cursor. OPEN partitions; -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; -- Drop the temporary table. DROP TABLE #work_to_do; GO