Blog – Fabiano Neves Amorim

Depois de ler um post no fórum do MSDN, resolvi escrever um pouco sobre índice, especialmente sobre o Índice Cluster que na maioria das vezes acaba sendo nossa primary key.

Vou começar com os 3 pontos que sempre são vistos em ppts de WebCasts e apresentações de boas práticas em criação de índices.

Índices cluster devem ser:

·         Únicos

o   Quando uma tabela possui um índice cluster ele servirá como referência(lookup) para todos os índices non-cluster, isso significa que quando você faz uma consulta que utiliza seu índice non-cluster e ele necessita de alguma informação que não está no próprio índice(colunas informadas na criação do índice) ele irá para o índice cluster ler esta informação, por isso ele grava o valor do seu índice cluster no índice non-cluster para conseguir fazer este lookup. É importante dizer que o SQL Server não obriga que seu índice cluster seja único, porém caso ele não seja único o SQL irá “unificar” sua chave incluindo uma informação integer de 4 bytes afim de que ele se torne único. Portanto se o SQL tiver que incluir esta informação de 4 bytes alem de consumir recurso para gerar esta informação(para cada insert ou update ele terá que verificar se esta informação já existe para saber se ele tem que incluir os 4 bytes ou não) complementar ao seu índice, ele irá ocupar mais espaço por conta dos 4 bytes para cada linha duplicada. Seus updates e inserts irão sofrer as conseqüências de uma má escolha do índice cluster.

·         Pequenos

o   Sabendo que a chave do índice cluster é salvo em todos índices non-cluster significa que quanto menor ele for menos espaço você irá utilizar para guardar esta informação no índice non-clustered. Por exemplo imagine que você possui uma tabela com uma chave primária com as colunas ID, Ano, Mes, Dia e possúi vários índices non-cluster em outras colunas, o SQL irá gravar os dados de ID, Ano, Mês, Dia em cada índice non-clustered de sua tabela. Então se ele for muito grande você terá uma grande perda de espaço e custo para seus selects(pois ele terá que ler mais páginas de dados para retornar sua informação), inserts e updates…

o   Quanto maior for seu índice cluster mais espaço seus índices non-clusters irão ocupar.

·         Estáticos

o   Deve ser estático porque se você alterar seu valor ele terá que alem de alterar o valor na tabela alterar todos os índices non-clustered lembra que ele também fica gravado nos índices non-clustered?. Outra coisa importante é que como o índice está ordenado pela chave caso o valor mude ele irá causar fragmentação na sua tabela.

Bom com base nestas informações eu tento sempre utilizar o seguinte padrão, colunas Identity para minha chave primária, elas definitivamente são Unicas, Pequenas(Integer = 4 bytes), e estáticas. Algumas vezes o máximo que tenho que fazer é mudar meu índice cluster para outra coluna que também será um identity por exemplo uma foreign key, e então definir minha chave primária como non-clustered.

Algumas pessoas podem dizer que colunas identity como primary key podem causar hot-spot, um hot-spot acontece quando existe um GRANDE número de inserts no final de uma tabela, isso pode causar Page Level Lock porque existem vários usuários tentando acessar a mesma página(final da tabela) para inserir seus dados. Sinceramente eu nunca vi isso acontecer e imagino que você vai ter que efetuar MUITOS inserts ao mesmo tempo para ver isso acontecer, portanto esse contra muitas vezes pode ser ignorado visto os benefícios do identity.

Como sempre, gosto de ver na prática(código) o que escrevo portanto vamos a parte legal da coisa, scripts.

 http://cid-52eff7477e74caa6.skydrive.live.com/embedrow.aspx/Publica/Boaspr%c3%a1ticasparaIndicesCluster.sql

Já está disponível para download o Vídeo da WebCast.

Basta clicar no link e seguir os passos de inscrição.

https://msevents.microsoft.com/cui/r.aspx?t=5&c=pt-br&r=1297386063

Hoje fiz a WebCast de Caminhos para SQL Serve 2008, apesar de ter ficado nervoso no inicio foi um prazer e espero que seja a primeira de muitas.

Obrigado ao Luciano Moreira pelo apoio, Daniel pelo Convite e Leandro pelo suporte.

Segue o link para download do PPT que usei na WebCast de hoje. Assim que o vídeo estiver disponível coloco o link aqui no Blog.

 http://cid-52eff7477e74caa6.skydrive.live.com/embedrowdetail.aspx/Publica/MSDNWebcast-CaminhosdeUpgradeparaSQLServer.pptx

God bless!

Hahaha fantástico esses vídeos falando sobre SQL Server.

 http://images.video.msn.com/flash/soapbox1_1.swf
Video: SQL Server vs. Oracle 11g

Pessoal é com prazer com convido a todos para participar da WebCast que a convite da Microsoft estarei apresentando na quarta-feira as 12:00 dia 04/06/2008.

Segue o resumo do que será discutido na WebCast e o link para inscrição do evento.

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032379953&EventCategory=4&culture=pt-BR&CountryCode=BR

Conto com a presença de todos.

Muitas vezes temos a necessidade de criar 2 índices em variáveis do tipo table, mas o comando CREATE INDEX @TMP… não roda pois não são permitidos comandos DDL em variáveis do tipo table, porém é possível definir 2 índices no comando de create da tabela, segue um exemplo bem prático.

SET STATISTICS PROFILE ON

DECLARE @Tmp TABLE(ID   Int IDENTITY(1,1) PRIMARY KEY,

                   Nome VarChar(200))

SELECT * FROM @Tmp

WHERE ID = 10

/*

SELECT * FROM @Tmp  WHERE ID = 10

  |–Clustered Index Seek(OBJECT:(@Tmp), SEEK:([ID]=(10)) ORDERED FORWARD)

*/

SELECT * FROM @Tmp

WHERE Nome = ‘Teste’

/*

SELECT * FROM @Tmp  WHERE Nome = ‘Teste’

  |–Clustered Index Scan(OBJECT:(@Tmp), WHERE:([Nome]=’Teste’))

*/

GO

— Exemplo de UNIQUE

DECLARE @Tmp TABLE(ID   Int IDENTITY(1,1) PRIMARY KEY,

                   Nome VarChar(200) UNIQUE)

SELECT * FROM @Tmp

WHERE ID = 10

/*

SELECT * FROM @Tmp  WHERE ID = 10

  |–Clustered Index Seek(OBJECT:(@Tmp), SEEK:([ID]=(10)) ORDERED FORWARD)

*/

SELECT * FROM @Tmp

WHERE Nome = ‘Teste’

/*

SELECT * FROM @Tmp  WHERE Nome = ‘Teste’

  |–Index Seek(OBJECT:(@Tmp), SEEK:([Nome]=’Teste’) ORDERED FORWARD)

*/