ESTUDODECASO

ESTUDO DE CASO

Objetivo

Objetivo



Encontrar as consultas de um grupo de

trabalho através do SQL Profiler.



Identificar as consultas e/ou

procedimentos que tenham o maior

impacto.



Analisar o plano de execução e as

Objetivo



Identificar consultas que necessitam de

ajustes



Identificar índices que devem ser criados.

Verificar os efeitos obtidos.



Repetir os processos anteriores para

O Artigo

Artigo

 Publicado em 9 de março de 2009.  Autor, Gail Shaw

 Título Original - Finding the Causes of Poor Performance in SQL Server

 Motivação – Dificuldade em solucionar um problema de desempenho no SQL Server. Propósito de responder

perguntas do tipo.

◦Por onde começar ?

◦O que fazer ?

Cenário

Cenário



Hipotético banco de dados que tem um

mal desempenho.



Procura por procedimentos e consultas

problemáticas.



Utilização da ferramenta SQL Profiler.

Análise do plano de execução, das

estatísticas das consultas e proposição de

ajustes.

O Problema

O Problema

 Manter um fórum de internet ativo.

 Inicialmente o desempenho era satisfatório.

 Com o volume de dados crescendo e o aumento

de carga no servidor as consultas começaram a ficar lentas.

 Deadlocks começaram a ser frequentes.

 Análise revelou que no banco de dados não foram

O Plano

O Plano



Muito provavelmente a criação de índices

úteis resolveria o problema.



Identificar dentre as consultas que são

executadas as tabelas e colunas mais

usadas, para então criar os índices.



O seguinte plano foi montado para

identificar os índices a serem criados e

outras alterações a serem realizadas:

O Plano



1. Capturar os traços (trace) de um

intervalo enquanto o servidor está ativo.

◦Identificará as consultas que constituem a carga

de trabalho do servidor.



2. Importar os resultados dos traços para

uma tabela do banco de dados.

◦Analisá-los para identificar as consultas ou

procedimentos que estão tendo o maior impacto sobre o servidor.

O Plano



3. Executar os procedimentos armazenados no

Management Studio em um banco de dados

de teste.

◦Analisar o plano de execução e as estatísticas das consultas.



4. Usar o plano de execução e as estatísticas

para identificar consultas que necessitam de

ajustes e índices que necessitam ser criados.

5.Implementar as mudanças e rever os efeitos.

A Execução do Plano

A Execução do Plano

◦Identificar os procedimentos com:

 _{A maior duração e/ou}

 _{O maior valor de CPU e/ou}

A Execução do Plano

◦A interface gráfica do Profiler degrada a performance de servidores.

◦Com utilização do script o processo pode ser automatizado.

◦Foi utilizado o conjunto de procedimentos da sp_trace para capturar o arquivo de trace.

A Execução do Plano

◦A definição da Trace descreve:

 _{Os eventos (events) necessário para análise.}

 _{As colunas de dados (data columns) necessárias}

para análise de cada evento.

 _{Qualquer filtro (filters) necessário para omitir}

A Execução do Plano

◦Importantes eventos capturados

 _{RPC:Completed e}

 _{TSQL:BatchCompleted}

◦Importantes colunas de dados a serem exibidas

 _TextData,  _CPU,

 _Reads,  _{Writes e}  _Duration.

A Execução do Plano

◦O endereço do Script foi armazenado em um diretório diferente do que armazena os

arquivos de dados e logs.

◦O tempo de trace foi agendado para executar durante 30 minutos após o seu início.

◦Foi configurado o tamanho dos arquivos a fim de gerá-los em pequena quantidade.

A Execução do Plano

A Execução do Plano

◦Os dados do Trace foram carregados para uma tabela, dedicada, para uma melhor análise.

◦Foi utilizado a função proprietária do SQL Server para carregar os dados do Trace na tabela criada fn_trace_gettable.

◦A duração foi armazenada em milisegundos e não em microsegundos conforme padrão do SQL Server.

A Execução do Plano

◦Ordenando em forma decrescente da duração, leitura ou CPU foi focado somente as consultas que apareceram no topo.

◦A primeira linha trata-se de um processo executado uma única vez no dia e por este motivo não foi avaliado.

A Execução do Plano

◦Uma vez que o aplicativo utiliza somente stored procedures criou-se uma consulta específica para demonstrar o nome das

A Execução do Plano

◦Inicialmente foram tratadas somente os dois primeiros procedimentos por acreditar que

seriam suficiente para melhorar o desempenho da aplicação.

◦Caso não seja suficiente uma nova análise seria realizada.

A Execução dos Procedimentos

A Execução dos Procedimentos

A Execução dos Procedimentos

 _{Atualiza a visualização das estatísticas da Thread.}  _{Atualiza o status do usuário.}

 _{Lista os comentários da discussão juntamente}

A Execução dos Procedimentos

A Execução dos Procedimentos

 _{Com a estatística ativada analisamos os planos de}

A Execução dos Procedimentos

 _{Resultado da primeira consulta.}

A Execução dos Procedimentos

 _{Resultado da terceira consulta.}

A Execução dos Procedimentos

A Execução dos Procedimentos

 _{Atualiza o status do usuário.}

 _{Lista os detalhes da discussão de uma página}

requerida, no máximo 20 detalhes por página, de um fórum selecionado.

A Execução dos Procedimentos

A Execução dos Procedimentos

 _{Com a estatística ativada analisamos os planos de}

A Execução dos Procedimentos

A Execução dos Procedimentos

Aplicação das Mudanças

Aplicação das Mudanças

Aplicação das Mudanças

◦Solução para Terceira Consulta

 _{Primeiro ponto analisou-se junto ao desenvolvedor}

a real necessidade comando SELECT *.

 _{Alterou-se o comando para recuperar somente os}

cinco atributos necessários, porém as estatística de execução não modificaram.

 _{Foi identificado pelo plano de execução que o}

Criação do Índice

O predicado sinalizado foi o atributo ThreadID. Criou-se um índice por este campo.

Aplicação das Mudanças

◦Resultado da Solução Terceira Consulta

Anterior

Aplicação das Mudanças

◦Resultado da Solução Terceira Consulta

Aplicação das Mudanças

Aplicação das Mudanças

◦Solução para Segunda Consulta

 _{Não foi identificado necessidade de melhoria nos}

códigos.

 _{Foi identificado pelo plano de execução que o}

Criação do Índice

O predicado sinalizado foi o atributo ForumID. Criou-se um índice por este campo.

Aplicação das Mudanças

◦Solução para Segunda Consulta

 _{Não foi obtido sucesso, pois o plano não se}

modificou.

 _{O índice não foi utilizado.}

 _{Foi analisado os demais itens do plano.}

Criação do Índice

Existe uma junção com a tabela Users, pelo atributo

CreatedBy.

E uma outra junção novamente com a tabela Users, pelo atributo LastPoster. Um novo índice foi criado com os atributos ForumID, CreatedBy e

Aplicação das Mudanças

◦Solução para Segunda Consulta

 _{Não foi obtido sucesso, pois o plano não se}

modificou.

 _{O índice não foi utilizado.}

 _{Uma nova análise do plano foi realizada.}

Criação do Índice

Um novo índice foi criado com os

atributos ForumID e LastModified.

◦Resultado da Solução Segunda Consulta

Aplicação das Mudanças

Anterior

Aplicação das Mudanças

◦Resultado da Solução Segunda Consulta

Aplicação das Mudanças

◦Aperfeiçoamento da Solução

 _{A quantidade de leituras na tabela Threads foi}

diminuído.

 _{A quantidade de leituras na tabela Users foi}

aumentada.

 _{Foi alterado o índice para incluir as colunas,} Title, CreatedOn, TotalReplies, TotalViews, Locked, CreatedBy, LastPoster, na propriedade INCLUDE no índice.

◦Resultado da Solução Segunda Consulta

Aplicação das Mudanças

Anterior

Aplicação das Mudanças

◦Resultado da Solução Segunda Consulta

O Resultado

O Resultado

◦Uma nova captura do Trace foi realizado no novo ambiente com os índices criados.

◦Processos repetidos:

 _{Carga do resultado do Trace para a tabela.}  _{Verificação dos Top 3.}

Conclusão

◦Foi encontrada as consultas que constituiam a

carga de trabalho do servidor.

◦Foi agregado as estatísticas dos procedimentos

para identificar os procedimentos com maiores impactos.

◦Foi utilizado o plano de execução e as estatísticas

das consultas para apontar os problemas.

◦Foi identificado os índices que melhor resolveriam

Conclusão

◦A otimização requer experiência, tentativas, conhecimento.

◦E paciência para analisar cada item, criar as soluções e verificar a eficácia1 e eficiência2 da solução.

1 _{Eficácia, o que fazer: fazendo as coisas certas.} 2 _{Eficiência, como fazer: fazendo certo as coisas.}

Dúvidas ?

 Faculdade INED de Tecnologia  Curso de Banco de Dados  Disciplina de Otimização de Banco de Dados  Professor Rogério Morais Rocha  Autor Rogério Morais Rocha