Performance começa na modelagem II

Boa noite galera! Como falei no meu post anterior, “apareci” novamente aqui para falar sobre modelagem. Vou mostrar alguns descuidos que podem causar futuro problemas de performance. Ou até mesmo causado por falta de conhecimento. E é aqui que nós profissionais de SQL Server entramos, pra ajudar os amigos de profissão e tirar dúvidas sempre quando possível.

Agora apresentarei as definições das formas normais:

1º Forma normal – Requer que todos os valores de colunas em uma tabela, sejam atômicos (ex., um número é um átomo, enquanto uma lista ou um conjunto não o são). Por exemplo, a normalização elimina grupos repetidos pondo-os cada um em uma tabela separada, conectando-os com uma chave primária ou estrangeira.

2º Forma normal –  Requer que não haja dependência funcional não-trivial de um atributo que não seja a chave, em parte da chave candidata.

3º Forma normal – Requer não haver dependências funcionais não-triviais de atributos que não sejam chave, em qualquer coisa exceto um superconjunto de uma chave candidata.

Referência: http://pt.wikipedia.org/wiki/Normaliza%C3%A7%C3%A3o_de_dados

Agora imagine que o programador ou o analista de sistemas recebeu a seguinte especificação:

Criar uma sistema de call center que recebe um lote de informações de clientes que devem receber ligações para venda de cartões. Porém, as informações são recebidas em um arquivo de texto, sendo que o nome do cliente, CPF do cliente, telefone residencial do cliente e telefone celular do cliente fazem parte de um único registro. Como em terra de cego que tem um olho é rei. Nosso ilustre amigo criou uma única tabela para receber os dados e assim facilitando o seu trabalho. A tabela ficou da seguinte forma:

USE MCITP
GO
IF OBJECT_ID(‘dbo.TB_CLIENTE’) IS NOT NULL
BEGIN
DROP TABLE dbo.TB_CLIENTE
END
GO
CREATE TABLE dbo.TB_CLIENTE
(
COD_CLI INT IDENTITY NOT NULL PRIMARY KEY,
CPF_CLI CHAR(11) NOT NULL ,
DD_TEL_RES CHAR(2) NOT NULL,
TEL_RES_CLI CHAR(8) NOT NULL,
DD_CEL_CLI CHAR(2) NOT NULL,
TEL_CEL_CLI CHAR(8) NOT NULL
)

Até aqui tudo parece perfeito, porém, a equipe gerencial deseja que uma tela de consulta seja criada e que seja possível realizar consulta por cpf, telefone residencial e telefone celular. Na tela todas os valores serão opcionais exceto cpf, teoricamente, deveria existir um índice para cada coluna, isso iria garantir que o SQL Server usasse o índice requisitado para a coluna escolhida como parâmetro.

O objetivo deste post não é falar sobre indexação ou estratégia de indexação. Esse assunto será abordado em um outro post com maiores detalhes e maiores abordagens. O que está sendo apresentando aqui acontece por ai no mercado, pois, esse é uma situação a qual eu já presenciei.

Voltando ao nosso assunto como todo mundo sabe que índice é “bom” para consultas, nosso amigo criou um índice para a coluna da cpf, telefone residencial e telefone celular.

USE MCITP
GO
CREATE NONCLUSTERED INDEX IX_CPF_CLI ON dbo.TB_CLIENTE (CPF_CLI)
GO
CREATE NONCLUSTERED INDEX IX_TEL_CLI ON dbo.TB_CLIENTE (DD_TEL_RES,TEL_RES_CLI)
GO
CREATE NONCLUSTERED INDEX IX_CEL_CLI ON dbo.TB_CLIENTE (DD_CEL_CLI,TEL_CEL_CLI)

Vamos aos fatos, um índice é uma faca de dois gumes. Em um ambiente OLTP temos muita leitura e criar índices ajuda a devolver os dados de forma mais eficaz e faz com que o SQL Server crie planos de execução mais eficientes. Por outro lado, quando você cria muito índice é possível que tenha baixa performance em instruções INSERT, UPDATE e DELETE. É correto afirmar que: O SQL Server organiza os índices em uma estrutura B-Tree (Esse assunto não será abordado neste post). E quando um índice clustered é atualizado ele deve manter os índices non-clustereds atualizados também. Já conseguiram sacar onde está o nosso possível overhead nas instruções que modificam os dados? Não! Então vamos lá!

Podemos ver que na consulta abaixo é possível verificar quais os índices que foram criados. Temos quatro índices. Um índice clustered para a coluna COD_CLI que é a nossa primary key  e os demais índices NONCLUSTEREDs que críamos anteriormente.

select i.name,i.type_desc from sys.indexes i
where i.object_id = OBJECT_ID(‘dbo.TB_CLIENTE’)

Como não realizamos nenhuma instrução sobre a nossa tabela TB_CLIENTE, podemos perceber que o nível folha e o nível intermediário da B-Tree não sofreu nenhuma modificação:

SELECT i.name,a.leaf_insert_count,a.nonleaf_insert_count
,a.leaf_delete_count,a.nonleaf_delete_count
,a.leaf_update_count,a.nonleaf_update_count
,a.leaf_allocation_count,a.page_lock_count
FROM sys.dm_db_index_operational_stats(DB_ID(‘mcitp’),OBJECT_ID(‘dbo.TB_CLIENTE’),NULL,NULL) as a
join sys.indexes i
on i.index_id = a.index_id
and i.object_id = a.object_id

Agora iremos realizar alguns inserts em nossa tabela e veremos o que acontece.

INSERT INTO dbo.TB_CLIENTE(CPF_CLI,DD_TEL_RES,TEL_RES_CLI,DD_CEL_CLI,TEL_CEL_CLI)
VALUES(‘7893652′,’11’,’24985236′,’11’,’78964521′)
,(‘7896325236′,’11’,’78965230′,’11’,’69365212′)
,(‘33209376325′,’11’,’78785225′,’11’,’80756312′)
,(‘33369863201′,’11’,’96785421′,’11’,’56365425′)

Executaremos novamente a query que traz as modificações feitas no nível folha e no nível intermediário para ver como a nossa estrutura está após os inserts:

Thiago! Continuo sem entender! Então vamos lá? O que aconteceu!? Como temos quatro índices na nossa tabela e realizamos quatro inserções, que dizer que: O SQL Server também atualizou todos os nossos índices nonclustereds no nível folha da nossa estrutura B-Tree como eu havia explicado anteriormente. Podemos ver que, muito índice em uma tabela que recebe alto nível de inserções pode prejudicar a performance. Imagine que eu precisasse importar 2000 linhas. O SQL Server escreveria 2000 linhas para o índice clustered e 2000 para cada índice nonclustered.

Mais e a atualização de registros, como ficaria ? Vejamos irei atualizar a coluna CPF_CLI para o cliente com o código 1.

UPDATE dbo.TB_CLIENTE SET CPF_CLI = ‘123654789’ WHERE COD_CLI = 1

Pronto! Agora com a atualização na coluna CPF_CLI podemos ver um aumento de valor na coluna leaf_insert_count para 5 no indice da coluna CPF. O registro também foi modificado no índice clustered, isso pode ser visto na coluna leaf_update_count que tem o valor de 1.  Mas porque no índice clustered? O índice clustered são os dados propriamente ditos, quando fizemos o update na coluna de cpf o SQL Server

Espero ter ajudado. Até o próximo post.