Getting your Trinity Audio player ready...

Existe uma grande diferença entre escrever uma instrução SQL que funciona e uma que funciona bem e é performática.

Algumas vezes os desenvolvedores estão muito focados em escrever suas queries que apenas resolvam uma tarefa específica sem levar em consideração performance ou o impacto na instancia do SQL server, como por exemplo quantidade de CPU, IO e memória que eles estão consumindo.  Assim, eles comem outros processos do SQL Server durante a execução derrubando toda a instância. Este artigo tentará prover ao desenvolvedor alguns pequenos detalhes que podem ajudar a otimizar as instruções.

Um grande número de livros e “white papers” foram escritos falando sobre performance no SQL server e este artigo não ira de forma alguma substituir o conhecimento que pode ser adiquirido com esses livros e “white papers”. A intenção é prover uma lista rápida para ajudar o desenvolvedor a identificar possíveis gargalos que podem existir no código SQL.

Antes de tentar resolver qualquer problema de performance, uma ferramenta correta de diagnostico é necessária. Alem de usar o SSMS e o SQL Profiler, o SQL 2008 vem com muitas DMV’s que trazem muita informação. Neste post irei usar o SSMS e farei referência a algumas DMV´s para ajudar a identificar os gargalos no SQL.

Então por onde começar?

Meu primeiro passo é capturar o plano de execução. Ele pode ser capturado pelo SSMS ou pelo SQL Profiler. Por simplicidade, usarei o SSMS.

1. Veja se você não está esquecendo nenhum table join. Isso acontece facilmente e o resultado pode ser um join cartesiano. Ex. um cross join entre duas tabelas de 1000 linhas cada pode gerar um resultado de 1.000.000 de linhas, trazer esse resultado envolve ler todos os dados do storage, aumento de I/O, carregar em memória e o buffer do SQL Server.
2. Veja se você não está esquecendo nenhuma clausula no WHERE. Esquecer um WHERE pode trazer mais informação que o necessário e pode causar o mesmo impácto do passo 1.
3. Verifique se as estatísticas estão sendo criadas e atualizadas automaticamente. Você pode ver isso nas propriedades da base. Por padrão quando uma base é criada a opção de “Auto Create Statistics” e “Auto Update Statistics” estão ON. Estatísticas são usadas para determinar o melhor plano de execução para uma query. Este artigo explica muito bem a importância da estatística.
4. Verifique se as estatísticas estão atualizadas. Além de criar estatísticas é importante que elas sejam atualizadas para refletir a troca dos dados. Em tabelas grandes, algumas vezes o “Auto Update Statistics” não refletem a distribuição atual dos dados. Basicamente, por padrão, estatísticas em tabelas grandes são atualizadas usando exemplos de dados randômicos. É interessante, em tabelas muito atualizadas, que a manutenção utilize Full Scan e seja agendado para horários menos impactantes.O DBCC SHOW_Statistics pode ser usado para ver a última data de atualização das estatísticas

   Se você identificar que as estatísticas estão muito desatualizadas, o sp_updatestats pode ser usado para atualizar as estatísticas baseadas em amostragem.

   Exec sp_updatestats

   Ou, usando a opção de FULLSCAN, neste caso todas as estatísticas serão recomputadas

   UPDATE STATISTICS Tabela WITH FULLSCAN

5. Procure por qualquer table ou index scan. Ele é fácil de identificar usando o execution plan. Na maioria dos casos (assumindo que as estatísticas estão atualizadas) ele indicará que existem índices faltando. Estas 3 DMV´s podem ajudar a encontrar os índices faltantes:
   • sys.dm_db_missing_index_details
   • sys.dm_db_missing_index_group_stats
   • sys.dm_db_missing_index_groups

   O código a seguir usa essas DMV´s e pode ser usado para identificar os índices que estão faltando e ordenados por performance.

   SELECT avg_total_user_cost, avg_user_impact,  user_seeks, user_scans,  ID.equality_columns, ID.inequality_columns, ID.included_columns, ID.statement FROM sys.dm_db_missing_index_group_stats GS LEFT OUTER JOIN sys.dm_db_missing_index_groups IG On (IG.index_group_handle = GS.group_handle) LEFT OUTER JOIN sys.dm_db_missing_index_details ID On (ID.index_handle = IG.index_handle) ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

   Como alternativa, podemos usar o Database Engine Tuning Advidor para recomendar a criação de índices, estatísticas e outras formar para melhorar a performance.

6. Procure por RID Lookups. Novamente usando o execution plan, esse é fácil de identificar. Não é sempre que é possível eliminar esse cara, mas usando covering indexes, RID Lookups podem ser reduzidos.
7. Procure por qualquer operador sort. Em um execution plan, operadores sort são identificados carregando uma grande porcentagem total do custo da query. Considere estas três opções:
   • Crie uma view Indexada na tabela de classificação e alinhe a view criando um Clustered Index.
   • Modifique a tabelas de classificação criando um Clustered Index usando as colunas do sort. Sempre existe uma discussão sobre essa solução…
   • Crie um Non Clustered Index nas colunas específicas e inclua todas as outras.
8. Procure por muita fragmentação de índices. Fragmentação de índices podem ser localizadas usando sys.dm_db_index_physical_stats. Se a fragmentação for superior a 30%, um rebuild de índice é recomendado, enquanto a fragmentação de índice for inferior a 30% podemos usar apenas um reorganize. A fragmentação de índices aumenta a requisição de I/O para ler as páginas de índices.O código a seguir mostra uma lista de tabelas e índices ordenados por fragmentação:
   

   Declare @db     SysName; Set @db = ‘<DB NAME>’; SELECT CAST(OBJECT_NAME(S.Object_ID, DB_ID(@db)) AS VARCHAR(20)) AS ‘Table Name’, CAST(index_type_desc AS VARCHAR(20)) AS ‘Index Type’, I.Name As ‘Index Name’, avg_fragmentation_in_percent As ‘Avg % Fragmentation’, record_count As ‘RecordCount’, page_count As ‘Pages Allocated’, avg_page_space_used_in_percent As ‘Avg % Page Space Used’ FROM sys.dm_db_index_physical_stats (DB_ID(@db),NULL,NULL,NULL,‘DETAILED’ ) S LEFT OUTER JOIN sys.indexes I On (I.Object_ID = S.Object_ID and I.Index_ID = S.Index_ID) AND S.INDEX_ID > 0 ORDER BY avg_fragmentation_in_percent DESC

   O comando a seguir pode ser usado para executar um rebuild de todos os índices de uma tabela específica:

   ALTER INDEX ALL ON Tabela REBUILD;

   Enquanto o comando abaixo pode ser usado para rebuild em um índice específico:

   ALTER INDEX Índice ON Tabela REBUILD;

   Os mesmos comandos podem ser usados para executar um REORGANIZE dos índices, apenas substitua o REBUILD por REORGANIZE.

   Após reorganize/rebuild dos índices, execute novamente o script para ver a fragmentação deles, provavelmente eles não irão aparecer.

   NOTA: Não vejo grandes vantagens em criar índices em uma tabela com menos de 1000 registros, provavelmente a performance com e sem índice será a mesma e é bem possível que esse índice sempre apareça no script como muito fragmentado já que a tabela possui muito poucos registros.

9. Procure por Locks. Se as tabelas estiverem em lock, causado por algum processo, a query pode gastar muito tempo aguardando que o lock morra para concluir a tarefa. Existem algumas coisas que podem ser feitas para tentar ajudar nesses casos:

• Mantenha as transações o mais curtas e rápidas o possível.
• Veja o transaction isolation level, e considere minimizar o locking contention aumentando a concorrência alterando para “Read Committed using row versioning” ou “Snapshot”.
• Especifique READUNCOMMITTED ou READPAST nos selects. Embora ambas aumentem a concorrência, ambas possuem desvantagens como “leitudas sujas” no caso do READUNCOMMITTED ou retorno de informação incompleta quando usado o READPAST (que na maioria dos casos pode não ser aceitável)

Caso precisem de mais ajuda, não deixe de colocar um comentário,,,

UPDATE: 12-07-2011

Continuando com a ideia de melhoria contínua… T-SQL Tuesday #20 – Melhores Práticas

---

Filed under: SQL,Virtual PASS BR - @ 2010-08-23 12:54 pm

Tags: 2000, 2005, 2008, código, função, microsoft, otimizar, programação, server, sql