Ferramentas do ofício: SQL Server Profiler and Query Analyzer

Por Rob Howard

No último artigo, discutimos o Microsoft® Application Center Test e como poderíamos usá-lo para medir o desempenho de nossa aplicação Web (ver Extreme ASP.NET: Tools of the Trade: Application Center Test). O Application Center Test é o tipo de ferramenta que não sabemos que iremos necessitar, até que alguém nos mostre quão útil realmente é. Verificamos que o uso do Application Center Test para medição de desempenho é bastante crítico, pois podemos usar essas medições para realizar otimizações, estipular metas e, o mais importante, planejar capacidade e escala.

Neste artigo, discutiremos duas outras ferramentas do ofício: SQL Server™ Profiler e SQL Server Query Analyzer. Sabemos que a maioria dos desenvolvedores ASP.NET não estão familiarizados com estas ferramentas SQL-oriented, porém queremos fortemente encorajá-los no sentido de acrescentá-las aos seus "cintos de ferramentas". Particularmente, se pudéssemos ter trabalhado em qualquer outro time de produtos além do ASP.NET durante nossa passagem pela Microsoft, teria sido com o time do SQL Server. Internamente, o SQL Server é um software incrivelmente complexo, mas programá-lo e usá-lo é bastante fácil. Entender um pouco desta complexidade é importante porque, como veremos, o banco de dados é freqüentemente o gargalo de desempenho da nossa aplicação e, quanto mais soubermos sobre os internals do SQL Server, quanto mais poderemos fazer para evitar esses problemas.

Acesso de Dados otimizado

Quanto tempo gastamos analisando o modo como nossa aplicação usa os recursos do banco de dados? Para os desenvolvedores, a resposta mais comum é "não muito". Tipicamente a maioria dos problemas de desempenho, são resolvidos de uma das duas maneiras seguintes: adicionando novo hardware ou fazendo o ajuste fino do código que roda a aplicação. A maioria dos desenvolvedores prefere fazer o ajuste fino do código, mas às vezes, pode ser realmente mais efetivo simplesmente adicionar novo hardware. No entanto, deveríamos levar em conta que às vezes, estes problemas de desempenho podem ser piorados pela adição de hardware novo, sem termos antes analisado os gargalos de desempenho existentes. Se um gargalo de desempenho estiver sendo provocado pelo software, ter hardware executando rapidamente irá enfileirar mais pedidos, o que acabará piorando a situação. Como sempre, a melhor abordagem consiste em analisar os gargalos de desempenho (de hardware ou software), antes de tomar qualquer providência.

Sendo desenvolvedores, preferimos verificar antes o código e depois passar para a abordagem de hardware. Uma vez que a decisão foi tomada, normalmente é uma questão de traçar algum perfil básico da aplicação, usando o Application Center Test e identificando em que ponto o desempenho da mesma é fraco. Uma vez que as áreas de problema são identificadas, o próximo passo será usar o SQL Server Profiler para inspecionar como a aplicação usa os recursos do banco de dados.

Quando escrevemos aplicações Web de alto-desempenho, nos deparamos com o seguinte truísmo: se nossa aplicação executa qualquer comunicação cross-process (banco de dados, Web services, invocação remota de objeto), nosso tempo será mais bem utilizado para otimizar essas comunicações ou remove-las completamente antes de tentar otimizar qualquer outro código dentro da aplicação. Para otimização do banco de dados, nossa "regra de ouro" geral é: se a aplicação acessar mais de uma vez o banco de dados ou duas vezes em uma determinada solicitação (ou fizer uma única chamada Web service), tem que existir uma razão muito boa para fazê-lo, porque essas idas e vindas são freqüentemente a causa das aplicações executarem com fraco desempenho.

Nossa equipe da Telligent, acabou de passar as últimas três semanas fazendo o ajuste fino do nosso Community Server para o forums.asp.net. Fizemos quase 40 modificações relacionadas ao desempenho, das quais 75 por cento no banco de dados, enquanto que os outros 25 por cento estavam diretamente relacionados à codificação de mudanças ou redução / otimização do tempo gasto no banco de dados. As funcionalidades de caching do ASP.NET constituem uma das melhores ferramentas para reduzir as idas e vindas ao banco de dados.

Se estivermos usando o SQL Server, teremos de imediato um grande conjunto de ferramentas a nossa disposição; o Profiler and Query Analyzer; para verificar como a aplicação está usando o banco de dados. Não abordaremos neste artigo todos os funcionamentos internos do Profiler and Query Analyzer, mas aprenderemos mais a respeito de como o SQL Server trabalha nos bastidores, recomendamos o Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook da Ken England (Digital Press, 2001). Este livro é um das nossas referências favoritas (nossa própria cópia é cheia de marcações e manchada de café). Esperamos que vossa cópia terá uma existência útil semelhante.

SQL Server Profiler

Verificar os logs ou arquivos trace gerados pelas aplicações, é fundamental para entender como estão sendo usadas. Por exemplo, para desenvolvedores Web é comum a revisão dos logs IIS para entender o uso da aplicação. Dentro do Community Server, há um manipulador de exceção embutido para localizar todas as exceções geradas. Depois, podemos revisar estes logs e rapidamente obter uma boa idéia das áreas problemáticas.

O SQL Server Profiler pode ser usado para visualizar sessões ao vivo de atividades no SQL Server do banco de dados atual. Podemos colher informações importantes como, por exemplo, quantas consultas o banco de dados está executando, quanto tempo estão demandando estas consultas, qual banco de dados está executando qual consulta e assim por diante.

A Figura 1 mostra uma sessão do profiler com o banco de dados usado no cluster do asp.net. Podemos ver várias capturas de trace SQL, que mostram stored procedures para weblogs.asp.net (itens com prefixo blog_), beta.asp.net (itens com prefixo aspnet_), Community Server forums (itens com prefixo cs_) e contadores de download para www.asp.net (IncrementDownloads). Ao selecionarmos um item podemos ver também o texto completo, como foi feito para a stored procedure cs_forums_Post. Há colunas adicionais para CPU, Reads, Writes e Duration.

Cc580638.SQLProfilerQueryAnalyzer01(pt-br,MSDN.10).png
Figura 1 - Sessão SQL Server do Profiler

Stored procedures

Há muito debate apaixonado sobre o uso de stored procedures. Um bom argumento para justificar seu uso pode ser achado em "TheServerSide Debates: Stored Procedures v Parameterized Queries", por Peter DeBetta.

Um dos benefícios das stored procedures em aplicações empresariais, são o encapsulamento e a abstração. Se uma aplicação está usando SQL dinâmico (texto de SQL que está sendo gerado na hora), o caso é tipicamente de ajuste fino. Fazer o ajuste fino do SQL dinâmico implica em recompilar a aplicação. Stored procedures tem a vantagem de encapsular suas funcionalidades, podendo ser modificadas sem afetar a assinatura pública. Da mesma maneira que métodos em APIs são usados para encapsular funcionalidades, stored procedures tem de várias maneiras a mesma capacidade, mas aplicadas a banco de dados.

Claro que também há alguns casos em que é recomendável usar o SQL dinâmico. Um exemplo seria o de mapeadores object/relational que podem criar SQL dinamicamente, baseados nos objetos de requisitos de negócio. Se o SQL dinâmico fosse usado em quaisquer dos clusters de asp.net, apareceria no SQL Profiler como SQL standard, tal como em SELECT column1, column2 from TableA. Porém, nenhum existe.

Iniciando o Profiler

Podemos achar o Profiler em qualquer instalação padrão do SQL Server, na pasta SQL Server dentro do All Programs listing. Recomendamos também instalar as ferramentas SQL Server como parte de qualquer ambiente de desenvolvimento padrão.

Uma vez aberto o Profiler, devemos conectar-nos a um banco de dados, antes de podermos começar uma sessão. Para tal, nos conectamos ao banco de dados, e selecionamos File | New | Trace. Isto exibe o diálogo Connect to SQL Server. Entramos com as credenciais Windows ou SQL Server. Temos que ser membros do grupo SQL Server sysadmin para rodar o profiler.

Assim que as credenciais são autenticadas, precisaremos configurar algumas propriedades do trace no diálogo Trace Properties. Por padrão, o Profiler capturará eventos definidos no modelo Standard, que é o subconjunto de eventos mais importantes. Capturar todos os eventos poderia ser útil, porém a maioria dos servidores suporta mais de um banco de dados, e um trace que capture todo o tráfego de banco de dados, não será necessariamente útil. Clicando na aba Filters identificaremos alguns filtros para restringir o que será capturado. Por exemplo, podemos restringir os traces para mostrar só conexões de certo login ou consultas que têm uma duração específica ou consultas que contêm algum texto conhecido (podemos usar símbolos wildcard como % para fazer o casamento wildcard de SQL).

Por exemplo, digamos que desejamos rodar um trace de todo o tráfego do banco de dados para o Community Server. Além disto, queremos visualizar as comunicações que estão consumindo mais de 100 ms para executar (normalmente itens que duram mais que 200 ms, serão interessantes para o usuário final). Para fazermos isto, configuramos um filtro com um nome de login específico, tal como csforums, e uma duração maior ou igual a 100. A Figura 2 mostra o resultado deste trace.

Cc580638.SQLProfilerQueryAnalyzer02(pt-br,MSDN.10).png
Figura 2 - Resultados do Trace

Há vários stored procedures que estão rodando mais freqüentemente, tais como cs_forums_Search e cs_Sections_Get. Também o cs_Threads_GetThreadSet, que teve uma duração de 703 é interessante! A stored procedure cs_Sections_Get parece ligeiramente anormal com um total de mais de 11,000 leituras, junto com um valor de duração alto. Estes números não são tão terríveis, mas tampouco são perfeitos.

Usando a informação que o Profiler provê, podemos ao menos começar a entender o que o banco de dados está fazendo e como está gastando o tempo. Nesta sessão de trace, filtramos os procedimentos que estavam tomando muito tempo para rodar e focalizamos no banco de dados de forums. Poderia haver também casos para os quais acontecem muitas consultas rodando e que estão reduzindo a velocidade do sistema, mas nestes casos é melhor aliviarmos alguma carga do banco de dados, adicionando um servidor adicional ou obtendo uma cópia local do banco de dados para testar a remoção dos falsos positivos.

Em um sistema pesadamente carregado, em lugar de usarmos o Profiler UI para coletar os SQL traces, deveríamos considerar o uso do T-SQL para enviar os resultados para um arquivo server-side (podemos ainda usá-lo para criar a definição de trace e para escrever o script e executá-lo). Ao enviarmos os resultados para arquivos de trace server-side, garantimos que nenhum evento será cancelado, enquanto ao enviarmos eventos de trace para um rowset (que é o que SQL Profiler usa) não temos esta garantia.

O SQL Server Query Analyzer

Agora que adquirimos a habilidade básica para examinar e extrair profiles de banco de dados, estaremos provavelmente desejando saber o que acontece a seguir. Bem, o próximo passo é examinar as consultas com o Query Analyzer, outra ferramenta maravilhosa que é útil para qualquer desenvolvedor que trabalha com o SQL Server. Assim como o Profiler é útil para obter as 50,000 visões básicas do sistema, o Query Analyzer é o microscópio para analisar os detalhes. Com o Query Analyzer, podemos executar consultas e stored procedures e obter uma exibição visual do plano de execução do SQL Server.

Da mesma forma que o Profiler, o Query Analyzer é achado na pasta SQL Server no menu All Program. Também, será necessário completar o Connect para o diálogo SQL Server. Uma vez conectado, podemos passar para o nosso banco de dados, digitando o seguinte:

use [databasename]

A seguir, clicamos no botão play na barra de menu (ou realçamos o texto e pressionamos F5). Qualquer opção executará o SQL realçado.

Em Profiler identificamos que a stored procedure cs_Sections_Get está lendo um volume extraordinariamente grande de dados - acima de 11,000 leituras! Podemos copiar o SQL trace do Profiler para o Query Analyzer e veremos então exatamente o que aquela consulta em particular está fazendo.

Analisando uma Consulta

Para rodar a consulta, primeiro colamos o conteúdo em Query Analyzer e a seguir selecionamos Query | Show Execution Plan na barra de ferramentas. Em seguida, realçamos o SQL e pressionamos F5 para executá-lo. A Figura 3 mostra os resultados.

Cc580638.SQLProfilerQueryAnalyzer03(pt-br,MSDN.10).png
Figura 3 - Resultados do Query Analyzer

Logo abaixo da consulta SQL que foi executada encontramos a grade de resultados que mostra o que foi retornado pela consulta. Neste caso foram retornados dois resultsets. O primeiro é uma lista de Sections (forums) e o segundo é um conjunto de permissões. O Community Server usa um sistema de permissões role-based para controlar o que os usuários podem ou não fazer dentro do sistema. Sempre que uma lista de Sections é recuperada, o sistema também refresca a lista de permissões para as mesmas.

No canto inferior direito do Query Analyzer vemos o texto: "5430 rows". Isto indica o número total de linhas retornadas. Neste procedimento, havia uma falha na lógica de permissões que faria o sistema retornar uma lista equivalente à soma do total dos roles multiplicada pelo total de sections. No caso de forums.asp.net, há 29 roles únicos e 181 sections, o que equivale a 5,249 linhas. Somando as 181 linhas retornadas pelo primeiro resultset teremos um total geral de 5,430 linhas retornadas.

Obviamente são muitas linhas. Na realidade, usando o Profiler e o Query Analyzer, esta Query particular foi otimizada para retornar menos de 250 registros - reduzindo a duração total para menos de 100 ms.

Além de rodar consultas, o Query Analyzer nos permite analisá-las. Verificamos que a exibição do plano de execução foi habilitada antes de executar o SQL. O plano provê um diagrama visual de como SQL Server está executando a consulta, que índices utiliza e outros dados que podem nos ajudar a otimizar índices ou outros dados requeridos pelo SQL Server para aumentar o desempenho das consultas.

A Figura 4 mostra cada consulta executada, o plano de execução, os índices usados, os joins e o volume de dados retornado em cada operação. Até mesmo as setas entre cada item têm significado: a espessura da seta é uma indicação visual de quantos dados foram retornados pela operação; isto é, uma consulta mal ajustada, teria muitas setas grossas, indicando operações que estão retornando provavelmente muitos dados.

Cc580638.SQLProfilerQueryAnalyzer04(pt-br,MSDN.10).png
Figura 4 - Execution Plans

O Application Center Test, o SQL Server Profiler e o SQL Server Query Analyzer são algumas das ferramentas mais poderosas para fazer o ajuste fino do desempenho de aplicações ASP.NET ou SQL Server. Estas ferramentas não só deveriam fazer parte do "cinto de ferramenta" dos desenvolvedores, como também deveriam ser usadas habitualmente para inspecionar o desempenho global das aplicações. Freqüentemente os desenvolvedores estão preocupados em otimizar rotinas dentro do código, no entanto estariam utilizando melhor seu tempo, dedicando-se a eliminar ou otimizar comunicações cross-process, tais como chamadas a banco de dados.

Espero termos despertado o interesse do leitor mediante um melhor conhecimento sobre o funcionamento interno do SQL Server. É uma tecnologia realmente fascinante. Se formos bastante afortunados para contarmos com um DBA na nossa equipe, recomendamos solicitar uma amostrar de como nossas aplicações interagem com o banco de dados. Aprenderemos muito!

Rob Howard (xtrmasp@microsoft.com /rhoward@telligentsystems.com) é fundador da Telligent Systems, especializada em alto desempenho de aplicações Web, administração de conhecimento e sistemas de colaboração. Previamente, Rob foi empregado da Microsoft onde ajudou a projetar as características de infra-estrutura do ASP.NET 1.0, 1.1, e 2.0.

Mostrar: