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.
O SQL Server cria automaticamente um índice para uma coluna declarada como chave primária (constraint PRIMARY KEY). Por default, este índice é do tipo 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]). Caso a coluna usada como chave do índice clustered seja "grande", isto irá aumentar o espaço de armazenamento da tabela, pois os outros índices (non-clustered) irão incorporar o valor da chave do índice clustered para referenciar cada linha da tabela. Se a coluna usada como chave do índice clustered for uma coluna passível de alteração, pode haver um aumento de IO nas operações que modifiquem o valor desta coluna, pois esta modificação deverá ser propagada para todos os índices non-clustered.
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!
/* =============================================================================================================================== - Chave pequena: diminui espaço usado - Chave imutável: menos IO Neste script criamos duas tabelas de teste, uma com chave pequena e imutável (int identity) e outra com chave "grande" e passível de modificação (Email varchar(200)). Após incluir a mesma massa de testes nas duas, vemos que a tabela com chave identity ocupa menos espaço que a tabela com chave natural. Ao realizar um UPDATE na coluna Email, vemos que o comando gera mais IO quando esta coluna é parte da chave primária. Ambas as situações se devem ao fato de que os valores do índice clustered são usados pelos índices non-clustered (como especificado em http://msdn.microsoft.com/en-us/library/ms177484(v=sql.105).aspx) =============================================================================================================================== */ -- Tabela Pessoas_PKEMAIL: PK é Email - "grande" e passível de modificação IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Pessoas_PKEMAIL') DROP TABLE Pessoas_PKEMAIL CREATE TABLE Pessoas_PKEMAIL ( Codigo int not null identity UNIQUE, -- Índice (non-clustered) é criado para reforçar constraint UNIQUE Email varchar(200) not null PRIMARY KEY, -- Índice (clustered) é criado para reforçar constraint PK Nome varchar(50) not null, Sobrenome varchar(50) not null, CPF char(11) not null UNIQUE, -- Índice (non-clustered) é criado para reforçar constraint UNIQUE UF char(2) ) CREATE INDEX IX_Pessoas_PKEMAIL_UF ON Pessoas_PKEMAIL(UF) -- Alguém ainda colocou um índice em UF (não foi culpa minha) GO -- Tabela Pessoas_PKIDENTITY: PK é Codigo - "pequena" e imutável IF EXISTS(SELECT * FROM sys.tables WHERE name = 'Pessoas_PKIDENTITY') DROP TABLE Pessoas_PKIDENTITY CREATE TABLE Pessoas_PKIDENTITY ( Codigo int not null identity PRIMARY KEY, -- Índice (clustered) é criado para reforçar constraint PK Email varchar(200) not null UNIQUE, -- Índice (non-clustered) é criado para reforçar constraint UNIQUE Nome varchar(50) not null, Sobrenome varchar(50) not null, CPF char(11) not null UNIQUE, -- Índice (non-clustered) é criado para reforçar constraint UNIQUE UF char(2) ) CREATE INDEX IX_Pessoas_PKIDENTITY_UF ON Pessoas_PKIDENTITY(UF) -- Alguém ainda colocou um índice em UF (não foi culpa minha) GO -- Inclui os mesmos dados de teste em cada tabela INSERT INTO Pessoas_PKEMAIL SELECT Email = p1.FirstName + '.' + p1.LastName + '@srnimbus.com.br', Nome = p1.FirstName, Sobrenome = p1.LastName, CPF = LEFT(NEWID(), 11), UF = LEFT(p1.FirstName,1) + LEFT(p1.LastName,1) FROM (SELECT DISTINCT FirstName, LastName = isnull(MiddleName,'') + LastName FROM AdventureWorks2012.Person.Person) P1 INSERT INTO Pessoas_PKIDENTITY SELECT Email = p1.FirstName + '.' + p1.LastName + '@srnimbus.com.br', Nome = p1.FirstName, Sobrenome = p1.LastName, CPF = LEFT(NEWID(), 11), UF = LEFT(p1.FirstName,1) + LEFT(p1.LastName,1) FROM (SELECT DISTINCT FirstName, LastName = isnull(MiddleName,'') + LastName FROM AdventureWorks2012.Person.Person) P1 GO -- Quantidade de péginas por tabela, incluíndo os índices: coluna sys.dm_db_index_physical_stats().page_count -- Veja que a tabela com chave IDENTITY ocupa menos espaço que a tabela com chave Email, pois os valores -- chaves de cada registro são copiados para as entradas dos índices non-clustered. SELECT name, SUM(page_count) FROM sys.tables t INNER JOIN sys.dm_db_index_physical_stats(DB_ID('Testes'), NULL, DEFAULT, DEFAULT, 'DETAILED') ips ON t.object_id = ips.object_id WHERE t.name IN ('Pessoas_PKIDENTITY', 'Pessoas_PKEMAIL') GROUP BY name -- Quantidade de IO: logical reads - páginas acessadas do cache de dados. -- Veja que o UPDATE na tabela com PK Email consistentemente acessa mais páginas que o UPDATE na tabela com PK IDENTITY, -- pois a atualização de uma coluna que é usada em um índice clustered deve ser propagada para todos os índices -- non-clustered. -- (Obviamente que um UPDATE na coluna Codigo geraria mais IO, mas como esta coluna é "artificial", não há necessidade -- de a aplicação atualizá-la, ao contrário da coluna Email.) SET STATISTICS IO ON UPDATE Pessoas_PKEMAIL SET Email = CAST(NEWID() as varchar(36)) + '@srnimbus.com.br' WHERE Codigo = 1 UPDATE Pessoas_PKIDENTITY SET Email = CAST(NEWID() as varchar(36)) + '@srnimbus.com.br' WHERE Codigo = 1 SET STATISTICS IO OFF |
