Continuando a análise do DBCC SHOWCONTIG, pretendo mostrar como a organização das páginas fisicamente pode gerar uma baixa densidade no item scan density. Para uma tabela pequena em que todas as páginas estão no cache de dados, isso não é problema, mas quando a questão escala para proporções maiores, o impacto pode ser ruim para as consultas.
O script utiliza o comando DBCC PAGE que deve ser habilitado através da instrução DBCC TRACEON(3604) e nos permite ver a estrutura e os dados de cada página do banco. Para mais informações sobre o DBCC Page, visite o site: http://support.microsoft.com/kb/83065/en-us
Está em anexo o script com os testes que eu fiz.
Para mais informações, leiam o Inside SQL Server 2000 ;-) ou me mandem um e-mail.
[]s
Luti
luticm79@hotmail.com
=============== SCRIPT ===================================
/******************************************************************************
Autor: Luciano Caixeta Moreira Data: 11/02/2006 Descrição: Continua a análise de fragmentação, colocando um cenário onde devem existir muitos extent switches.
Histórico:
-
******************************************************************************/
USE MASTERGO
IF EXISTS (SELECT * FROM SYSDATABASES WHERE [Name] = 'Inside')BEGIN DROP DATABASE InsideENDGO
CREATE DATABASE InsideON(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 InsideGO
/* Cria a nossa tabela de trabalho*/IF EXISTS(SELECT * FROM sysObjects WHERE [Name] = 'Fragmentation' AND XType = 'U')BEGIN DROP TABLE FragmentationEND
CREATE TABLE Fragmentation(Codigo INT NOT NULL CONSTRAINT PK_Codigo PRIMARY KEY,Name VARCHAR(8000))GO
/* Insere os valores ímpares*/Declare @Contador INTSET @Contador = 1
WHILE @Contador < 1000BEGIN INSERT INTO Fragmentation VALUES (@Contador, Replicate('L', 8000)) SET @Contador = @Contador + 2ENDGO
/* Insere os valores pares*/Declare @Contador INTSET @Contador = 2
/* Como o regsitro ocupa mais de 8000 bytes para armazenar a informação, somente cabeuma linha por página, portanto nesse momento, a estrutura esperada das páginas é (aproximadamente) a seguinte:
Extent 1 Pag 1 - Codigo 1 Pag 2 - Codigo 3 Pag 3 - Codigo 5 Pag 4 - Codigo 7 Pag 5 - Codigo 9 Pag 6 - Codigo 11 Pag 7 - Codigo 13 Pag 8 - Codigo 15
.....
Extent 63 Pag 1 - Codigo 2 Pag 2 - Codigo 4 Pag 3 - Codigo 6 Pag 4 - Codigo 8 Pag 5 - Codigo 10 Pag 6 - Codigo 12 Pag 7 - Codigo 14 Pag 8 - Codigo 16
Vamos verificar isso logo abaixo...*/
select * from master..sysdatabases where [name] = 'Inside'select * from sysindexes where [id] = Object_id('Fragmentation')GO
/* Resultado...
first: 0x1C0000000100 root: 0x0F0000000100 firstIAM: 0x190000000100*/
DBCC TRACEON(3604)GO
/* No meu caso, o database id é 7. Consulte a tabela sysdatabases para verificar qual o dbid do banco Inside no SQL Server que você está trabalhando.*/DBCC PAGE(7, 1, 15, 3) -- Root do índice 0x0FDBCC PAGE(7, 1, 710, 3) -- Nível não folha do índiceDBCC PAGE(7, 1, 711, 3) -- Nível não folha do índiceGO
/* Analisando a saída do DBCC PAGE(7, 1, 710, 3) podemos ver claramente como estão as páginasno disco. As ímpares são as primeira páginas (a partir da 28) enquanto as pares estão apósas ímpares, a partir da 589.
FileId PageId Row Level ChildFileId ChildPageId Codigo ------ ----------- ------ ------ ----------- ----------- ----------- 1 710 0 0 1 28 NULL1 710 1 0 1 589 21 710 2 0 1 29 31 710 3 0 1 590 41 710 4 0 1 30 51 710 5 0 1 591 61 710 6 0 1 31 71 710 7 0 1 592 81 710 8 0 1 33 9 */
DBCC PAGE (7, 1, 28, 3)GO
/* Analisando a página com o registro de código 1, podemos verificar noseu cabeçalho a seguinte informação:
Page @0x45562000----------------m_pageId = (1:28) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0m_objId = 1977058079 m_indexId = 0 m_prevPage = (0:0)m_nextPage = (1:589) pminlen = 8 m_slotCnt = 1m_freeCnt = 79 m_freeData = 8111 m_reservedCnt = 0m_lsn = (8:1413:5) m_xactReserved = 0 m_xdesId = (0:0)m_ghostRecCnt = 0 m_tornBits = 2
A próxima página física em disco é a página 29, que contém o registro 3. Enquanto a próxima página lógica é a 589 (item m_nextPage) que contém ocódigo 2, garantindo a ordenação do índice clusterizado.*/
DBCC PAGE (7, 1, 589, 3)GO
/* Analisando a página com o registro de código 2, podemos verificar noseu cabeçalho a seguinte informação:
Page @0x4516E000----------------m_pageId = (1:589) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000m_objId = 1977058079 m_indexId = 0 m_prevPage = (1:28)m_nextPage = (1:29) pminlen = 8 m_slotCnt = 1m_freeCnt = 79 m_freeData = 8111 m_reservedCnt = 0m_lsn = (8:1413:10) m_xactReserved = 0 m_xdesId = (0:0)m_ghostRecCnt = 0 m_tornBits = 1
Notem que a próxima página lógica é a página 29, que contém o registrode código 3, enquanto a anterior é a página 28 (m_prevPage) que contém oregistro de código 2.
-> O nível folha dos índices clusterizados são encadeados atravésdos registros m_nextPage e m_prevPage encontrados no cabeçalho de cada página.*/
DBCC SHOWCONTIG ('Fragmentation', 1)GO
/*Resultado:===============================================================================
DBCC SHOWCONTIG scanning 'Fragmentation' table...Table: 'Fragmentation' (1977058079); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 999- Extents Scanned..............................: 127- Extent Switches..............................: 998- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 12.51% [125:999]- Logical Scan Fragmentation ..................: 49.95%- Extent Scan Fragmentation ...................: 0.79%- Avg. Bytes Free per Page.....................: 79.0- Avg. Page Density (full).....................: 99.02%DBCC execution completed. If DBCC printed error messages, contact your system administrator.
O output do DBCC SHOWCONTIG mostra claramente a quantidade de extent switches quesão feitos. Enquanto o ideal seria somente mudar 125 vezes (999 / 8), foram feitas 999mudanças (igual ao número de páginas :-p).*/
DBCC INDEXDEFRAG (7, 'Fragmentation', 1)GO
/* O INDEXDEFRAG somente manteve no mesmo local algumas páginas, fazendo a trocade local entre 990 páginas.
Pages scanned: 992 Pages moved: 990 Pages deleted: 0*/
DBCC SHOWCONTIG scanning 'Fragmentation' table...Table: 'Fragmentation' (1977058079); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 999- Extents Scanned..............................: 127- Extent Switches..............................: 137- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 90.58% [125:138]- Logical Scan Fragmentation ..................: 0.60%- Extent Scan Fragmentation ...................: 0.79%- Avg. Bytes Free per Page.....................: 79.0- Avg. Page Density (full).....................: 99.02%DBCC execution completed. If DBCC printed error messages, contact your system administrator.
O INDEXDEFRAG fez um ótimo trabalho de organização das páginas, mas ainda poderia ter sidomelhor, chegando mais perto do Scan Density de 99%.*/
/* Refazer todo o processo e ao invés de utilizar o INDEXDEFRAG, utilizar o DBREINDEX.*/DBCC DBREINDEX('Fragmentation', 'PK_Codigo', 0) GO
DBCC SHOWCONTIG scanning 'Fragmentation' table...Table: 'Fragmentation' (1977058079); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 999- Extents Scanned..............................: 126- Extent Switches..............................: 125- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 99.21% [125:126]- Logical Scan Fragmentation ..................: 0.00%- Extent Scan Fragmentation ...................: 0.00%- Avg. Bytes Free per Page.....................: 79.0- Avg. Page Density (full).....................: 99.02%DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/