/****************************************************************************** Autor: Luciano Caixeta Moreira Data: 08/02/2006 Descrição: Analisa as questões de fragmentação. ******************************************************************************/ USE MASTER GO IF EXISTS (SELECT * FROM SYSDATABASES WHERE [Name] = 'Inside') BEGIN DROP DATABASE Inside END GO CREATE DATABASE Inside ON ( Name = 'Inside_data', FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL$INST2000_01\Data\Inside_data.mdf', Size = 10MB, FileGrowth = 5MB, MaxSize = UNLIMITED ) LOG ON ( Name = 'Inside_log', FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL$INST2000_01\Data\Inside_log.mdf', Size = 5MB, FileGrowth = 3MB, MaxSize = UNLIMITED ) GO USE Inside GO /* Cria a tabela que será analisada. Para que as análises sejam consistentes a tabela somente deve estar em 1 arquivo. */ IF EXISTS(SELECT * FROM sysObjects WHERE [Name] = 'Fragmentation' AND XType = 'U') BEGIN DROP TABLE Fragmentation END CREATE TABLE Fragmentation ( PKIdentity INT NOT NULL IDENTITY CONSTRAINT PK_PKIdentity PRIMARY KEY, Name VARCHAR(100) ) GO /* Gera uma massa de dados para a tabela */ Declare @Contador INT SET @Contador = 0 WHILE @Contador < 10000 BEGIN INSERT INTO Fragmentation VALUES (Replicate('L', 100)) SET @Contador = @Contador + 1 END GO SELECT * FROM Fragmentation GO /* first: 0x1C0000000100 root: 0x0F0000000100 firstIAM: 0x190000000100 */ select * from sysindexes where [id] = Object_id('Fragmentation') GO /* Verifica a fragmentação da tabela. Neste momento a fragmentação deve ser muito baixa */ DBCC SHOWCONTIG (Fragmentation, 1) /* Resultado: =============================================================================== DBCC SHOWCONTIG scanning 'Fragmentation' table... Table: 'Fragmentation' (1093578934); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 145 - Extents Scanned..............................: 20 - Extent Switches..............................: 19 - Avg. Pages per Extent........................: 7.3 - Scan Density [Best Count:Actual Count].......: 95.00% [19:20] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 5.00% - Avg. Bytes Free per Page.....................: 27.0 - Avg. Page Density (full).....................: 99.67% DBCC execution completed. If DBCC printed error messages, contact your system administrator. O ideal extent count pode ser calculado da seguinte forma: 145 páginas / 8 = 18,125 -> Ceiling = 19 extents */ /* Aqui podemos verificar quais são os extents utilizados pela tabela */ DBCC EXTENTINFO('Inside', 'Fragmentation', 1) /* Resultado: =============================================================================== File_id | Page_id | pg_alloc | ext_size | Obj_id 1 15 1 1 1977058079 1 28 1 1 1977058079 1 29 1 1 1977058079 1 30 1 1 1977058079 1 31 1 1 1977058079 1 33 1 1 1977058079 1 34 1 1 1977058079 1 35 1 1 1977058079 1 96 8 8 1977058079 1 104 8 8 1977058079 1 112 8 8 1977058079 1 120 8 8 1977058079 1 128 8 8 1977058079 1 136 8 8 1977058079 1 144 8 8 1977058079 1 152 8 8 1977058079 1 160 8 8 1977058079 1 168 8 8 1977058079 1 176 8 8 1977058079 1 184 8 8 1977058079 1 192 8 8 1977058079 1 200 8 8 1977058079 1 208 8 8 1977058079 1 216 8 8 1977058079 1 224 8 8 1977058079 1 232 2 8 1977058079 Analisando os extents utilizados 1) 8 ~ 15 (1 página) 2) 24 ~ 31 (4 páginas) 3) 32 ~ 40 (3 páginas) 4) 96 ~ 103 (8 páginas) 5) 104 ~ 111 (8 páginas) . . Seguindo a lógica... (1 extent = 8 páginas) . 20) 224 ~ 231 (8 páginas) 21) 232 ~ 239 (2 páginas) Total de páginas: 1 + 4 + 3 + (8 * 17) + 2 = 146 páginas ========================= Aqui você deve estar perguntando: porque 146 páginas ao invés de 145 como mostra o resultado do DBCC SHOWCONTIG? R: Isto acontece porque quando analisamos os extents nós também visualizamos a página raiz do índice, nesse caso a página 15. Já o DBCC SHOWCONTIG somente levou em conta as páginas folhas do índice clusterizado. Para comprovarmos isso, basta vermos o endereço da página root retirada da Sysindexes: 0x0F0000000100 Ordenando o little endian e tirando os bytes que indicam qual é o arquivo, temos: 0x0000000F -> SELECT CAST (0x0000000F AS INT) = 15! Nem precisava do cast para ver isso, mas fica a dica de conversão de hexa. Continuando... Com base nas informações acima, também é intuitivo contar o número de extents scanned e o número de extent switches, o que está de acordo com o resultado do DBCC. Como a ordenação física das páginas (apontadas pela IAM) está de acordo com a lista encadeada entre os headers das mesmas, a fragmentação lógica é de 0%. A fragmentação de extent pode ser calculada de acordo com o número de gaps entre os extents do objeto, no nosso caso existem 20 páginas e somente 1 gap, entre o extent 32 e o extent 96, calculando: 1/20 = 0,05 = 5%. */ /* Cria uma fragmentação excluindo 1/3 das páginas */ DELETE FROM Fragmentation WHERE (PKIdentity % 3) = 0 DBCC SHOWCONTIG (Fragmentation, 1) GO /* Resultado: =============================================================================== DBCC SHOWCONTIG scanning 'Fragmentation' table... Table: 'Fragmentation' (1977058079); index ID: 1, database ID: 7 TABLE level scan performed. - Pages Scanned................................: 145 - Extents Scanned..............................: 20 - Extent Switches..............................: 19 - Avg. Pages per Extent........................: 7.3 - Scan Density [Best Count:Actual Count].......: 95.00% [19:20] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 5.00% - Avg. Bytes Free per Page.....................: 2100.0 - Avg. Page Density (full).....................: 74.06% DBCC execution completed. If DBCC printed error messages, contact your system administrator. A fragmentação introduzida pelo delete acima foi uma INTERNAL FRAGMENTATION. Como o número de páginas utilizadas continua o mesmo e a ordem também não mudou, somente se alterou a densidade da página e o número de bytes livres por página. */ /* O que será realmente útil do indexdefrag é o recurso de compactação das páginas, responsável por diminuir a fragmentação interna. As páginas não mais utilizadas seráo liberadas pelo SQL Server. */ DBCC INDEXDEFRAG (7, 'Fragmentation', 1) GO /* Resultado: Pages scanned: 140 Pages moved: 90 Pages removed: 48 Se pergarmos o total inicial de páginas 145 e dividirmos por 3, teremos como resultado 48 páginas. Então a defragmentação fez seu trabalho direitinho, pois 1/3 da tabela havia sido excluído. */ DBCC SHOWCONTIG (Fragmentation, 1) GO /* Resultado: =============================================================================== DBCC SHOWCONTIG scanning 'Fragmentation' table... Table: 'Fragmentation' (1977058079); index ID: 1, database ID: 7 TABLE level scan performed. - Pages Scanned................................: 97 - Extents Scanned..............................: 14 - Extent Switches..............................: 13 - Avg. Pages per Extent........................: 6.9 - Scan Density [Best Count:Actual Count].......: 92.86% [13:14] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 42.86% - Avg. Bytes Free per Page.....................: 51.9 - Avg. Page Density (full).....................: 99.36% DBCC execution completed. If DBCC printed error messages, contact your system administrator. Vale notar um alto valor no item Extent Scan Fragmentation, pois o SQL Server não muda o local físico das páginas sobreviventes, deixando os extents fisicamente separados. Essa organização atrapalha os range scans. Exercício: Execute o comando "DBCC EXTENTINFO('Inside', 'Fragmentation', 1)" e veja se consegue calcular o valor correto do Extent Scan Fragmentation. */ /* 1 - Repetir todo o procedimento de fragmentação da tabela (CREATE, INSERT e DELETE). 2 - Ao invés de executar o DBCC INDEXDEFRAG, executar um CREATE com DROP_EXISTING (abaixo) */ CREATE UNIQUE CLUSTERED INDEX PK_PKIdentity ON Fragmentation (PKIdentity) WITH DROP_EXISTING GO DBCC SHOWCONTIG (Fragmentation, 1) GO /* Resultado: =============================================================================== DBCC SHOWCONTIG scanning 'Fragmentation' table... Table: 'Fragmentation' (1977058079); index ID: 1, database ID: 7 TABLE level scan performed. - Pages Scanned................................: 97 - Extents Scanned..............................: 13 - Extent Switches..............................: 12 - Avg. Pages per Extent........................: 7.5 - Scan Density [Best Count:Actual Count].......: 100.00% [13:13] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 54.4 - Avg. Page Density (full).....................: 99.33% DBCC execution completed. If DBCC printed error messages, contact your system administrator. A diferença entre a recriação e o INDEXDEFRAG fica por conta do Extent Scan Fragmentation, pois quando o índice foi recriado o SQL Server colocou todos os extents contíguos em disco. */