| /* ===============================================================================================================================
1 - Chave não-monotônica: causa fragmentação
Neste script criamos uma tabela de testes (Pessoas) que tem uma PK na coluna EMail (varchar).
Incluimos 8.000 linhas com valores de e-mails "aleatórios" (não ordenados), e vemos a fragmentação causada.
Reconstruímos o índice base da PK e vemos a fragmentação ser eliminada pela reordenação dos registros e das páginas da tabela.
Inserimos mais 8.000 linhas e novamente a tabela se fragmenta.
A PK não é monotônica.
=============================================================================================================================== */
-- Criação de tabela com PK "natural" (E-mail)
DROP TABLE Pessoas
CREATE TABLE Pessoas
(
Codigo int not null identity,
Email varchar(200) not null,
Nome varchar(50) not null,
Sobrenome varchar(50) not null,
CPF char(11) not null,
UF char(2)
CONSTRAINT PK_Pessoas PRIMARY KEY (Email),
)
GO
-- Pega nome e sobrenome de AdventureWorks2012.Person.Person (download em http://msftdbprodsamples.codeplex.com/releases/view/93587) pra gerar dados de teste
-- O uso de um cursor na inserção é para simular uma aplicação transacional inserindo linhas na tabela
-- (Se usarmos um INSERT INTO ... SELECT ... o Query Optimizer ordena as linhas antes de fazer a inserção, e a fragmentação não ocorre)
-- Pega os 1os 8.000 registros do SELECT que gera os dados de teste e os insere na tabela
DECLARE @nome varchar(100), @sobrenome varchar(100)
DECLARE c CURSOR FOR
SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName
FROM AdventureWorks2012.Person.Person
ORDER BY LastName ASC
OPEN c
FETCH NEXT FROM c INTO @nome, @sobrenome
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Pessoas VALUES (
@nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email
@nome, -- Nome
@sobrenome, -- Sobrenome
LEFT(NEWID(), 11), -- CPF
LEFT(@nome,1) + LEFT(@sobrenome,1) -- UF
)
FETCH NEXT FROM c INTO @nome, @sobrenome
END
CLOSE c
DEALLOCATE c
GO
SELECT COUNT(*) FROM Pessoas
-- PK EMail não é monotônica: fragmentação altíssima
DBCC SHOWCONTIG('Pessoas')
GO
-- Se reconstruirmos o índice a fragmentação é eliminada pela reordenação das páginas, mas isto é só temporário - novas
-- operações de inclusão e alteração irão fragmentar novamente a tabela (o que não ocorre numa PK IDENTITY)
ALTER INDEX PK_Pessoas ON Pessoas REBUILD
DBCC SHOWCONTIG('Pessoas')
GO
-- Inserção de mais 8.000 linhas fragmenta a tabela de novo
DECLARE @nome varchar(100), @sobrenome varchar(100)
DECLARE c CURSOR FOR
SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName
FROM AdventureWorks2012.Person.Person
order by LastName DESC
OPEN c
FETCH NEXT FROM c INTO @nome, @sobrenome
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Pessoas VALUES (
@nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email
@nome, -- Nome
@sobrenome, -- Sobrenome
LEFT(NEWID(), 11), -- CPF
LEFT(@nome,1) + LEFT(@sobrenome,1) -- UF
)
FETCH NEXT FROM c INTO @nome, @sobrenome
END
CLOSE c
DEALLOCATE c
GO
DBCC SHOWCONTIG('Pessoas')
/* ===============================================================================================================================
2 - Chave monotônica: não gera fragmentação.
Neste script repetimos a sequencia do script anterior, com a diferença de que a PK agora é monotônica
(uma coluna IDENTITY).
Incluimos 8.000 linhas com valores de e-mails "aleatórios" (não ordenados), e não ocorre fragmentação, pois as chaves dos registros é
crescente.
Inserimos mais 8.000 linhas e nenhuma fragmentação ocorre.
A PK é monotônica.
=============================================================================================================================== */
-- Criação de tabela com PK artificial (Codigo - identity)
DROP TABLE Pessoas
CREATE TABLE Pessoas
(
Codigo int not null identity,
Email varchar(200) not null,
Nome varchar(50) not null,
Sobrenome varchar(50) not null,
CPF char(11) not null,
UF char(2)
CONSTRAINT PK_Pessoas PRIMARY KEY (Codigo),
)
GO
-- Pega os 1os 8.000 registros do SELECT que gera os dados de teste e os insere na tabela
DECLARE @nome varchar(100), @sobrenome varchar(100)
DECLARE c CURSOR FOR
SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName
FROM AdventureWorks2012.Person.Person
ORDER BY LastName ASC
OPEN c
FETCH NEXT FROM c INTO @nome, @sobrenome
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Pessoas (Email, Nome, Sobrenome, CPF, UF)
VALUES (
@nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email
@nome, -- Nome
@sobrenome, -- Sobrenome
LEFT(NEWID(), 11), -- CPF
LEFT(@nome,1) + LEFT(@sobrenome,1) -- UF
)
FETCH NEXT FROM c INTO @nome, @sobrenome
END
CLOSE c
DEALLOCATE c
GO
SELECT COUNT(*) FROM Pessoas
-- PK Codigo é monotônica: nenhuma fragmentação ocorre - inserção é sempre no "fim" da tabela
DBCC SHOWCONTIG('Pessoas')
GO
-- Inserção de mais 8.000 linhas: tabela se mantém compacta
DECLARE @nome varchar(100), @sobrenome varchar(100)
DECLARE c CURSOR FOR
SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName
FROM AdventureWorks2012.Person.Person
order by LastName DESC
OPEN c
FETCH NEXT FROM c INTO @nome, @sobrenome
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Pessoas (Email, Nome, Sobrenome, CPF, UF)
VALUES (
@nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email
@nome, -- Nome
@sobrenome, -- Sobrenome
LEFT(NEWID(), 11), -- CPF
LEFT(@nome,1) + LEFT(@sobrenome,1) -- UF
)
FETCH NEXT FROM c INTO @nome, @sobrenome
END
CLOSE c
DEALLOCATE c
GO
DBCC SHOWCONTIG('Pessoas')
|
