Como configuro corretamente o SQL Server Profiler em um ambiente de produção?

Question

Em nosso ambiente de produção (SQL Server 2008 Enterprise), temos tido problemas com o uso da CPU e a utilização da rede. Com base nas especificações das (múltiplas) máquinas que temos e na funcionalidade geral do nosso produto de software, a carga não deve ser um problema.

Como desenvolvedor, imaginei que o problema deve estar em outro lugar, provavelmente nas consultas que estão sendo executadas. Eu pensei que seria uma boa idéia anexar o SQL Profiler a uma instância de produção para descobrir quais consultas (ou mesmo código) são os pontos de acesso, e trabalhar na otimização dessas áreas primeiro. (Nenhum de nossos caras de hardware / rede já fez isso antes.)

Anexei o profiler a uma de nossas instâncias de desenvolvimento por algumas horas e determinei que temos algumas áreas problemáticas:

1. Consultas sendo executadas dentro de loops - era comum ver as mesmas poucas consultas sendo executadas dezenas de milhares de vezes.
2. Consultas que não são armazenadas em cache - vi algumas SELECT * consultas aparecerem; havia também alguns que não são parametrizados. Este é um grande subconjunto de # 1, em que uma consulta variaria apenas por um único parâmetro WHERE .
3. Consultas de longa duração - aquelas que realmente estão sobrecarregando a CPU; estes podem ser escritos de forma ineficiente, não utilizando índices, etc. A contagem de ocorrências nestes foi muito menor.

Com base nas estatísticas de resumo geradas, as maiores consultas de problemas são do tipo # 1 e # 2. No entanto, não posso obter esses resultados imediatamente e começar a trabalhar porque as consultas de instância de desenvolvimento são essencialmente o que os outros desenvolvedores estão trabalhando no momento, não o que será mais executado na produção.

Eu tenho lido que anexar o SQL Server Profiler a uma instância é problemático devido à sobrecarga. Por razões óbvias, anexar em um ambiente de produção deve ser o mais leve possível.

O que eu preciso saber é isso - considerando os três tipos diferentes de consultas que preciso localizar, como configuro o criador de perfil para ter um impacto mínimo no desempenho? Existe outra ferramenta que eu poderia usar para conseguir isso?

Notas:

• Nosso ambiente de produção tem horários definidos de pico e fora de pico, por isso, é possível obter uma amostra decente, capturando apenas 30 a 60 minutos de dados durante um horário de pico.
• Gostaria de fazer login em um banco de dados - depois de brincar com o criador de perfil, esse formato de log é o mais fácil de consumir mais tarde.

Answer 1

Em vez de anexar o SQL PRofiler, use os procedimentos sp_trace_xxx . A razão pela qual o SQL Profiler não é recomendado na produção é que ele pode fazer com que o servidor seja bloqueado ao aguardar a comunicação da rede com o Profiler para liberar ('network' pode significar memória compartilhada local, a localização não é relevante). A idéia é que um único processo gerenciado por GUI encadeado não será capaz de acompanhar um fluxo de eventos de velocidade máxima a partir de um servidor altamente otimizado nativo multiencadeado. Usando os procedimentos Trace, você está tirando a GUI / Profiler da equação e permite que o servidor libere os eventos tão rápido quanto eles podem ser gravados no disco no servidor, o que é sempre muito mais rápido do que como o Profiler seria capaz para processá-los. Se você adicionar um filtro decente nos eventos (por exemplo, apenas rastrear eventos RPC: Concluídos e Lote: Concluídos com duração superior a > 1 segundo ou algo semelhante), você não terá nenhum impacto sério no servidor.

O Profiler da GUI é capaz de salvar um rastreio como uma série de chamadas sp_trace para você, assim você não precisa criar manualmente o script.

    

Answer 2

O ideal é que você também tenha um ambiente de teste ou teste, em que você coloca o código que deseja testar, não desenvolve e, em seguida, insere uma carga no nível de produção. Nesse caso, você primeiro carregaria uma cópia do código de produção e dos bancos de dados, iniciaria o criador de perfil e permitiria que os usuários ou testadores o usassem como o sistema de produção.

Depois de fazer isso e otimizar algumas coisas, você carrega esse código de volta em seu ambiente de teste e o testa novamente, para ver se encontrou outro gargalo.

Agora, se você não puder fazer isso, você deve ter uma idéia de executar o profiler em seu ambiente de desenvolvimento se ele for leve o suficiente (ou não) para tentar rodar diretamente na produção.

    

Answer 3

Execute o profiler a partir de um servidor / estação de trabalho diferente.