Blog do GB: IDENTITY na PK

Este é um dos posts sobre os exemplos apresentados na palestra sobre modelagem no 24 Hours of PASS - PT. Você pode ver a série de posts em Impactos da Modelagem na Performance do SQL Server.

Por default, no SQL Server uma coluna declarada como chave primária (constraint PRIMARY KEY) cria automaticamente um índice clustered (se você não está familiarizado com os tipos de índice do SQL Server, veja este vídeo sobre o assunto [ainda não disponível]). No índice clustered, como a ordem lógica dos registros em disco é mesma ordem da chave do índice, se os valores da chave nas linhas sendo inseridas são monotônicos (sempre crescente ou sempre decrescente) as inclusões não causam fragmentação na tabela. Se a chave não for monotônica a tabela sofrerá fragmentação.

O script a seguir demonstra isto. Copie ele para uma nova query no Management Studio (não esqueça de mudar para um banco de testes), vá executando os pedaços de código entre os comentários e veja os resultados. Qualquer dúvida posta uma pergunta!

/* ===============================================================================================================================
   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')

quinta-feira, 29 de novembro de 2012

IDENTITY na PK - Fragmentação