Introdução e fragmentação no SQL Server 2000
Estreia do meu blog na Microsoft, vamos ver no que vai dar...
Primeiro minha apresentação: meu nome é Luciano Caixeta Moreira, tenho 26 anos e acabei de entrar na Microsoft ocupando o cargo de Premier Field Engineer, focando nas áreas de banco de dados e desenvolvimento. Aqui atendemos clientes premier localizados do México para baixo e tentamos ser o mais especialista possível nas tecnologias que trabalhamos. Atualmente possuo as certificações: MCP, MCDBA, MCAD, MCSD e MCT.
Vamos para a parte técnica do post...
Como não estou com tempo para ficar escrever artigos longos, vou tentar utilizar uma nova abordagem por aqui.
Diariamente eu crio vários script testando algumas funcionalidades do SQL Server e vou documentando passo a passo o que estou fazendo, afinal de contas é minha intenção sempre ter por perto o meu material de estudo. Então estou aproveitando o momento e documentando de forma mais detalhada, para que o script possa ser útil a quem interessar. Caso o feedback seja positivo, um dia eu paro para escrever mais sobre o assunto ou transformá-lo em um artigo.
Vale sempre lembrar que os resultados dos comandos DBCC podem variar, então eu sempre coloco o resultado que obtive para que a explicação possa ser entendida. Sugiro que executem o script a medida em que vão lendo e verificando o resultado.
Coloquei o script em anexo e, para facilitar, copiei o texto logo abaixo.
O feedback de vocês é importante.
Caso queiram entrar em contato, meu e-mail é: luticm79@hotmail.com
[]s
Luti
============================= SCRIPT ==========================
/******************************************************************************
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.
*/