Moving Average Sql Query

Conteúdo williamrobertson Configurando o PLSQL Developer, parte 2 Este artigo foi escrito para o PLSQL Developer 8.0.4 usando Oracle 11.2 e Windows XP, em uma máquina virtual Parallels no meu Mac, razão pela qual as screenshots mostram uma mistura de XP Silver e Aqua windows. O PLSQL Developer é um dos vários ambientes de desenvolvimento integrados (IDEs) que estão disponíveis para a Oracle. Uma das coisas que eu gosto sobre ele é como é configurável - você pode mudar quase qualquer coisa, e com plug-ins para download, como Browser Extender você pode adicionar sua própria funcionalidade. Depois de mover os PCs várias vezes e ter que reinstalar o PLSQL Developer cada vez, descobri que existem algumas personalizações que eu não poderia viver sem, e eu pensei que o documento de identidade. Parte Um cobriu as preferências, como fontes e layout da tela, e Parte 2 abrange o navegador de sessão. Eu estava indo para incluir o direito personalizado mouseclick ações que eu adicionei usando Browser Extender. Mas há muito o que você pode fazer com o navegador de sessão que eu vou ter que deixar isso para a Parte 3. Estender o Navegador de Sessão 1. Faça o Navegador de Sessão mais fácil de encontrar No layout padrão, seu caminho enterrado na lista sob o Ferramentas menu, mas como seu algo youll usar todo o tempo é muito melhor ter um botão para ele. Caso você tenha perdido isso na parte 1. você pode personalizar a barra de ferramentas adicionando um ícone para o Navegador de Sessão. Heres o tipo de coisa que você pode fazer: Barra de ferramentas padrão Barra de ferramentas personalizada Observe o ícone de chaves cruzadas segundo a partir da esquerda na barra de ferramentas personalizada. 2. Um olhar para as configurações padrão Agora abra o Navegador de Sessão e veja a configuração padrão. (Na verdade, não bastante padrão - Ive mudou a fonte para Corbel 8pt, que se encaixa mais informações na tela, bem como ser mais atraente do que o padrão na minha opinião Tahoma também funciona bem. Você estará olhando para esta tela muito, Depois de tudo.) A tela é um relatório de detalhe mestre, com as consultas mestre e de detalhe configuráveis ​​usando o ícone de chave inglesa. A consulta mestre na parte superior da janela é definida em Filtros e são fornecidas algumas variações em select from vsession. Em Detalhes, há quatro consultas muito básicas para cursores abertos, instrução SQL atual, estatísticas de sessão e bloqueios: Observe a variável de ligação: sid na consulta Cursores. A coisa legal sobre as consultas de detalhe do navegador de sessão é que você pode consultar o valor atual de qualquer coluna da seção superior como uma variável de ligação em consultas detalhadas. Assim, contanto que a consulta principal contenha uma coluna chamada sid, podemos usar expressões como onde sessionid: sid em qualquer consulta detalhada. (Isso significa, no entanto, que você pode precisar incluir algumas colunas na consulta mestre puramente para usar como chaves em consultas detalhadas.) Mais um ponto a observar sobre a caixa de consulta de detalhe é que adicionando o concatenar texto após a consulta faz PLSQL Developer unir todas as linhas de saída em um bloco grande. Enquanto um recurso puro, isso também impede a rolagem assim que eu encontrá-lo uma bênção misto. 3. Escreva suas próprias consultas do VSESSION Active Sessions As consultas padrão são todas selecionadas de vsession onde. . Que obviamente é uma configuração padrão sensata que funcionará em todas as versões do Oracle. Novos e úteis atributos são adicionados à vsession em cada versão e, é claro, codificá-los explicitamente em associações e lookups significa que a consulta pode não funcionar em uma versão anterior.1 Se você estiver trabalhando com várias versões do Oracle, talvez seja necessário salvar Mais de uma consulta na seção Filtros e selecione a apropriada conforme necessário (infelizmente, o desenvolvedor do PLSQL não pode verificar a versão e escolhê-la para você). Heres uma consulta de sessões Active melhor para Oracle 10.2.0.2 em diante (note as colunas plsqlentryobjectid e plsqlentrysubprogramid. Entre outros, foram adicionados nesta versão para que ele não vai funcionar no Oracle 10g XE). Todas as sessões que estão atualmente ativas (exceto os processos em segundo plano Oracle, como o Log Writer), ou que estão bloqueando outras sessões ou pertencentes a mim. Seu pai, se parte de uma consulta paralela O objeto que está sendo aguardado atualmente (geralmente uma tabela ou índice) - olhou para cima de dbaobjects usando rowwaitobj. A entrada PLSQL e procedimentos atuais - olhou para cima de dbaprocedures usando as colunas plsql adicionadas em Oracle 10.2.0.2. Algumas estatísticas sobre CPU, leituras, uso de memória e análise de consultas, de vsessmetric. Quando os resultados são exibidos, você pode clicar nessas colunas para classificar sessões por uso da CPU Qualquer sessão que esteja bloqueando outra, independentemente do seu status, além das sessões de primeiro plano atualmente ativas. A instância RAC, para clusters de vários nós. Se você tiver apenas uma única instância será 1 (você pode querer movê-lo para o final da listagem para dar espaço para outras colunas). GV views for RAC As v views (na verdade sinônimos para sys. v views) têm versões g-prefixadas - por exemplo, gvsession - que incluem o número da instância, para uso em sistemas RAC. Para sistemas de instância única isso sempre será 1. A documentação lista apenas a versão v, portanto, se você quiser saber sobre gvsession. Por exemplo, basta procurar vsession e assumir que haverá uma coluna extra chamado instid. Eu usei o v regular e RAC-pronta gv nomes de forma intercambiável. Copie a consulta abaixo na caixa Query (depois de testá-la em uma janela SQL para certificar-se de que ela funciona com a versão do Oracle e as permissões - para acessar as visualizações V que você precisa SELECTCATALOGROLE). Observe que não há semicolon no final. Você também pode querer revê-lo contra vsession no caso de haver colunas úteis que são úteis para você. Minhas Sessões Em um sistema ocupado, às vezes você só quer ver suas próprias sessões e excluir tudo o resto. Para isso, eu uso uma consulta de Minhas sessões, que é igual à anterior, exceto para a cláusula WHERE, que é: Todas as Sessões Seu também às vezes útil para ter uma versão que mostra todas as sessões, incluindo o gravador de log Oracle, etc monitor de processo Faça outra cópia da consulta acima e deixe de lado a cláusula WHERE. 4. Agora adicione suas próprias guias de detalhes Isso usa vsqlstats para exibir estatísticas de execução detalhadas sobre as sessões SQL instrução atual (identificado por sqlid). Observe que ele se refere a todas as instâncias do cursor, e não apenas esta chamada de sessões atual. (Além disso, como as visões v refletem apenas o que está na memória agora, pode ser diferente do que você vê nas visões dbahist se você tiver o Pacote de Diagnóstico). A idéia das porcentagens é mostrar como o tempo total decorrido se decompõe Em CPU, IO, simultaneidade espera etc. Seu apenas aproximado e eles não sempre adicionar até 100, porque pode haver outros fatores não registrados como, por exemplo, os tempos de transferência de rede e processamento de aplicativos, mas eles dão-lhe uma idéia de como a declaração está sendo processado. Agora você deve obter um SQL Stats guia como a imagem abaixo para qualquer sessão thats execução SQL. (Binds, Prev SQL etc são outras guias que eu definirei em um momento.) História de perfuração deste cursor Se uma instrução SQL está demorando muito tempo, você pode querer verificar seu histórico de desempenho (de dbahistsqlstats) para ver se isso é normal para o Cursor ou se alguma coisa mudou. A primeira consulta abaixo fornece os planos de execução distintos e suas estatísticas de tempo de execução correspondentes, agregados para todo o histórico do cursor, para que você possa ver o tempo de execução médio e se existem vários planos. (Observe a junção ao gvsqlplan - o g indicando a versão RAC habilitada - que seeems ser a maneira mais confiável para encontrar o plano de execução que está sendo usado atualmente, pois inclui o número filho. Como vsqlstats relatórios apenas uma linha por sqlid distintos A segunda versão - que eu rotulo como Perf history this cursor by date - divide as mesmas informações por dia, para que você possa ver se ele funcionou rapidamente na última terça-feira, ou se o Plano alterado esta manhã: A consulta a seguir listará todas as variáveis ​​de bind mantidas em vsqlbindcapture para a instrução SQL atual. Ive filtrado os resultados para excluir duplicatas. Observe que o Oracle não capta todos os valores de ligação simples e apenas mantém o último valor capturado no intervalo cursorbindcaptureinterval e dependendo da quantidade de espaço disponível até cursorbindcaptureareasize. Uma alternativa é obter os dados de ligação usados ​​no tempo de análise de vsqlplan. Embora isso leve alguma decodificação como a sua realizada em formato RAW dentro de uma coluna XML - ver Jonathan Lewis postagem no blog Bind Capture. Que se vincula à criação de scripts de teste com variáveis ​​de vinculação de Kerry Osborne e acompanhamento do valor de ligação de Dion Cho. Isso me levou à seguinte consulta usando uma idéia de Kyle Hailey nos comentários sobre Jonathan Lewis post: Em meus testes usando Oracle 11.2.0.2 isso omite os nomes de bind. De qualquer forma, a captura de valores de vinculação é um grande assunto, por isso vou deixá-lo com as consultas acima para experimentar e seguir em frente. SQL anterior, estatísticas SQL anteriores Ocasionalmente, é útil para ver o que foi a instrução anterior. Vsession contém várias colunas prev, então apenas duplicar as guias de detalhes para SQL Text e SQL Stats, mas substituir prevsqlid e prevchildnumber. Estatísticas de objeto Ao investigar um problema de desempenho, você geralmente deseja verificar o estado atual das estatísticas nas tabelas envolvidas na consulta. A consulta abaixo associa vsqlplanstatisticsall com dbatabstatistics para listar essas informações - não é perfeito se tabelas particionadas estão envolvidos, como o problema pode estar com estatísticas para uma partição individual ou subpartition, mas é um começo. Substitua a consulta de Cursores padrão (selecione de vopencursor onde sid: sid) com o seguinte para adicionar algumas estatísticas de atividade. (Observe que a estatística de execuções se refere a todas as sessões, e não apenas à sessão atual). Plano atual Os PLSQL Developers incorporados na ferramenta Explain Plan (F5) estão bem e bem, mas só podem ser tão bons quanto explicar o plano. Ou seja, a ferramenta usa o plano de explicação para prever o plano de execução e exibe os resultados em forma gráfica. Às vezes, isso não é o mesmo que o plano de execução real. Ao visualizar sessões atualmente em execução, eu gostaria de usar dbmsxplan. displaycursor () para ver o que o banco de dados está realmente fazendo. Defina uma guia de plano atual usando o seguinte: O comentário concatenar fará PLSQL Developer envolver todas as linhas de saída da consulta em um bloco grande. Isso torna mais fácil de ler, embora ele também impedir a rolagem para Im não tenho certeza que todos os que útil aqui. (Infelizmente, você não pode especificar uma fonte monospace para um item de menu individual, então a exibição padrão não é tão grande.) A melhor maneira de lê-lo é copiar e colar em uma nova janela SQL. Isso é mais fácil se você definiu uma tecla de atalho como Alt-S para o arquivo gt Nova janela gt SQL como sugeri na parte 1. (Eu também tenho uma extensão do navegador Extender para fazer isso em um clique com o botão direito, que eu vou vir mais tarde. ) Eu também uso outra variação desta consulta, que Ive rotulada GPS plano atual (Gather Plan Statistics - embora talvez plano estendido seria um nome melhor agora eu penso sobre isso). Isso usa ALLSTATS LAST no argumento de formato para dbmsxplan. displaycursor para obter as contagens de linhas estimadas e reais (cardinalidade) se a consulta usou a dica gatherplanstatistics ou se o parâmetro statisticslevel foi definido como ALL para a sessão. A parte ligeiramente complicada com isto é que você não pode usá-lo até que a consulta termine (porque a linha real conta arent ainda conhecido), mas quando ele completar não é mais a consulta atualmente em execução e, portanto, desaparece de vsession, e assim quando você Atualizar seu navegador de sessão seu ido. Em vez disso, você precisará atualizar a tela enquanto a consulta está sendo executada, mas aguarde até que ela seja concluída antes de ir para a guia GPS do plano atual. Aguardar atual Embora o estado de espera das sessões atuais já esteja mostrado na consulta mestre, acima, eu gostaria de ter as informações em sua própria guia também. Ive rotulado o objeto de espera olhou para cima de rowwaitobj como possivelmente não relacionados como um lembrete de que embora este é o objeto mais recente que a sessão esperou, o processamento pode agora ter movido para outra coisa (classificação de saída, por exemplo, ou à espera de um aplicativo para Processar a saída de consulta) ea sessão não está realmente acessando esse objeto no momento. Últimas 10 esperas O seguinte dá um rápido olhar para as sessões de atividade recente usando vsessionwaithistory (tempo de espera está em centésimos de segundo): vsessionlongops exibe o status de várias operações que são executados por mais de 6 segundos. Essas operações atualmente incluem muitas funções de backup e recuperação, coleta de estatísticas e execução de consultas, e mais operações são adicionadas para cada versão da Oracle. Se uma consulta usa operações de hash ou classificar, varreduras de tabelas, operações de partição etc que levam mais de 6 segundos, essas operações aparecerão em vsessionlongops e você poderá acompanhar o seu progresso. (Note que suas únicas operações individuais são rastreadas, e não consultas inteiras.) Muitos processos Oracle de longa execução também estão instrumentados, como o manual menciona. Outros não listados acima incluem Database Replay e SQL Performance Analyzer executa (11g), e tarefas de importaport de dataapump - e, claro, qualquer um de seus próprios processos onde você incluiu dbmsapplicationinfo. setsessionlongops chamadas para registrar o total de trabalho e quantidade processsed até agora. Eu também defino um longo ops essa guia de consulta, que é uma cópia da acima, mas com um filtro adicional para limitá-lo para a operação em execução: resumo ASH - sessão vactivesessionhistory é um instantâneo de vsession tomada uma vez cada segundo, realizada por um Período limitado (normalmente 30 a 60 minutos) e, em seguida, armazenado em dbahistactivesesshistory. (Para usar isso, você precisa do Diagnostics Pack, portanto certifique-se de que está licenciado, mesmo que funcione - você não quer que seu chefe receba uma conta inesperada após uma auditoria da Oracle). Existem várias formas criativas de explorar essas informações e eu uso três consultas Para rastrear o que uma sessão atualmente em execução está fazendo. Desde ASH amostras a cada segundo, pode ser útil para resumi-lo por instrução SQL e lista os resultados por tempo tomado. Se você estiver assistindo a um procedimento ou lote que chama várias instruções, isso dá uma visão geral de onde a sessão está gastando seu tempo (um pouco como rastrear a sessão). A consulta a seguir fornece uma linha por sqlid. Em ordem decrescente do tempo total, com um total na parte inferior. ASH sumário - execuções Eu também tenho uma versão mais detalhada possível em 11g pela coluna sqlexecstart em vacativesessionhistory. O que me permite ver execuções individuais de uma instrução SQL ao invés de uma única linha agregada. Sumário ASH - objeto de cursor de tempo Relatório mostrando objetos esperados por todas as instruções SQL para a sessão especificada. Isso se entende como uma maneira rápida de ver o que uma sessão passou seu tempo, em termos de objetos, em vez de consultas. ASH resumo desta consulta com chamador Next Eu tenho uma consulta GROUP-BY para o sqlid atual. Em ordem decrescente da contagem de amostras. A idéia é ver onde o tempo está sendo gasto na instrução atualmente em execução (ao invés de quais instruções tomaram tempo na sessão atual). Como o Active Session History usa um intervalo de sondagem de 1 segundo, algo que ocorre em 10 amostras provavelmente levou cerca de 10 segundos. Observe que ele só filtra em sqlid. Assim múltiplas execuções da mesma consulta pela sessão serão todas agregadas juntas. (Em 11g você pode usar a nova coluna sqlexecid para distinguir entre execuções.) Lembre-se também que a ASH pode samplear atividade como On CPU juntamente com um objeto de banco de dados - isso significa apenas o último objeto acessado no momento em que a amostra foi tomada, não Que a CPU estava necessariamente relacionada a esse objeto. Eu tenho dois sabores deste, com e sem detalhes do chamado PLSQL procedimento. ASH resume esta consulta apenas SQL Esta é a mesma que a consulta anterior, mas sem os detalhes de PLSQL de chamada para dar uma visão mais clara do acesso ao banco de dados. ASH detalhe esta sessão Finalmente, eu tenho uma lista reta de vacativesessionhistory para a sessão atual para que você possa ter uma idéia do que está fazendo atualmente: A configuração padrão vem com um guia Locks. Vamos dizer que uma sessão executa as seguintes ações: A guia Bloquear padrão exibe isso: Alterá-lo para o seguinte fornece alguns detalhes mais: Otimizador não padrão Eu acho isso útil para verificar quais configurações otimizador estão em uso por uma determinada sessão (que pode não ser a As mesmas configurações da sua sessão ou os padrões da instância). Isto associa o vsysoptimizerenv (parâmetros do sistema relacionados ao otimizador) com vsesoptimizerenv (parâmetros de sessão relacionados ao otimizador, inicialmente herdados das configurações no nível do sistema, mas refletindo quaisquer alterações feitas pelos comandos alter session) e relata as diferenças. Temp espaço Quanto espaço temporário é esta sessão usando para hash junta-se, classifica etc cópia William Robertson 2011 Subscrever aos artigos Subscrever ao código e scriptsSQL Correlated Sumário Subconsulta. Neste tutorial, você aprenderá sobre a subconsulta correlacionada SQL. Que é uma subconsulta que depende da consulta externa. Este tutorial requer um bom conhecimento de subconsulta. Se você não sabe nada sobre a subconsulta. Confira o tutorial de subconsulta antes de avançar com este tutorial. Introdução à SQL subconsulta correlacionada Uma subconsulta correlacionada é uma subconsulta que depende da consulta externa. Isso significa que a cláusula WHERE da subconsulta correlacionada usa os dados da consulta externa. A principal diferença entre uma subconsulta correlacionada e uma subconsulta não correlacionada é que você não pode executar uma subconsulta correlacionada como uma subconsulta não correlacionada. Além disso, uma subconsulta correlacionada executa uma vez para cada linha selecionada da consulta externa. Uma subconsulta correlacionada também é conhecida como subconsulta de repetição ou subconsulta sincronizada. Exemplos de subconsulta correlacionados SQL Vamos dar uma olhada em alguns exemplos para entender a idéia da subconsulta correlacionada. SQL subconsulta correlacionada no exemplo de cláusula SELECT A consulta a seguir seleciona os cinco principais clientes por vendas: Como analisar o desempenho do SQL Server 24 de fevereiro de 2014 Portanto, você tem esse banco de dados SQL Server que seu aplicativo usa e de alguma forma parece ser lento. Como você resolver este problema Onde você olha O que você mede Espero que este artigo irá responder perguntas suficientes para você começar a fim de que você possa identificar os gargalos você mesmo, ou saber o que procurar para ampliar ainda mais o seu arsenal e conhecimento. Como funciona o SQL Server Para solucionar problemas de desempenho, você precisa ter uma compreensão de como o SQL Server funciona. A explicação grosseiramente simplificada é que o SQL Server executa suas consultas da seguinte maneira: O aplicativo envia uma solicitação para o servidor, contendo um nome de procedimento armazenado ou algumas instruções T-SQL. A solicitação é colocada em uma fila dentro da memória do SQL Server. Um thread livre do próprio pool de threads do SQL Server8217s pega o pedido, compila-o e executa-o. O pedido é executado instrução por declaração, sequencialmente. Uma instrução em uma solicitação deve ser concluída antes das próximas partidas, sempre. Procedimentos armazenados executa da mesma forma, instrução por instrução. As declarações podem ler ou modificar dados. Todos os dados são lidos do cache na memória do banco de dados (o conjunto de buffers). Se os dados não estiverem nesse cache, ele deve ser lido do disco para o cache. Todas as atualizações são gravadas no log do banco de dados e no cache em memória (no pool de buffer), de acordo com o protocolo Write-Ahead Logging. O bloqueio de dados garante a correção sob simultaneidade. Quando todas as instruções na solicitação tiverem sido executadas, o thread está livre para pegar outro pedido e executá-lo. Uma solicitação está em execução ou está aguardando Isso pode parecer trivial, mas entender que a solicitação está executando ou está aguardando algo (está suspenso) é a chave para solucionar o desempenho do SQL Server. Se solicitações enviadas para o SQL Server levar um longo tempo para retornar resultados, em seguida, eles levam muito tempo executando ou demoram muito tempo esperando. Saber se é um caso ou outro é crucial para descobrir os gargalos de desempenho. Além disso, se os pedidos levam muito tempo esperando, podemos investigar mais e descobrir o que eles estão esperando e por quanto tempo. Noções básicas sobre solicitação Waits Os pedidos de espera têm dados de informações de espera Sempre que um pedido for suspenso, por algum motivo, o SQL Server coletará informações sobre o motivo pelo qual ele foi suspenso e por quanto tempo. No código interno do SQL Server, simplesmente não há maneira de chamar a função que suspende uma tarefa sem fornecer os dados de informações de espera necessários. E esses dados são coletados e disponibilizados para você de várias maneiras. Esses dados de informação de espera são cruciais para determinar problemas de desempenho: um pedido em execução no momento que está suspenso agora você pode ver o que está esperando e quanto tempo está esperando. A solicitações atualmente em execução que está executando agora você pode ver qual é a última coisa que esperou. Você pode entender quando os pedidos estão aguardando em outros pedidos. Você pode obter agregados para quais são os recursos mais esperados (ocupados) em todo o servidor. Você pode entender quais recursos físicos (de hardware) estão saturados e estão causando problemas de desempenho. Informações de espera para solicitações atualmente em execução Para cada solicitação em execução no SQL Server há uma linha no sys. dmexecrequests. Consultar este DMV, a qualquer momento, dá-lhe uma visão rápida de tudo que está em execução naquele momento. O waittype. Waittime e lastwaittype colunas dar-lhe-ão uma sensação imediata do que é 8216runnig8217 vs o que é 8216waiting8217 eo que está a ser esperado: Pedido na sessão 53 é um SELECT e está actualmente à espera. Está esperando em uma fechadura. A sessão 54 é um INSERT e está aguardando. Ele está esperando em uma trava de página. A sessão 55 é um INSERT e está atualmente em execução. A espera anterior era uma trava de página. A sessão 56 é um INSERT e está aguardando. Ele está aguardando o log do banco de dados para liberar (é cometer uma transação). A sessão 57 é um INSERT e está aguardando. Ele está aguardando o log do banco de dados. Observe que o pedido para sessão 54 tem um status de execução, mas ele está realmente esperando. Isso ocorre porque esperas de trava são esperadas para ser curto. O blockingsessionid também nos diz, para algumas das tarefas em espera, qual outra solicitação está mantendo o recurso sendo aguardado. A sessão 53 está aguardando na sessão 56 para um recurso KEY lock, o que significa que o SELECT está tentando ler uma linha bloqueada pelo INSERT. O SELECT não será retomado até que o INSERT cometer. A sessão 54 está aguardando na sessão 55 para uma trava de página, o que significa que a sessão 55 INSERT está modificando essa página de dados agora e os dados na página são instáveis ​​e não podem ser lidos. As sessões 56 e 57 estão aguardando, mas não há nenhuma outra sessão bloqueando-as. Eles estão aguardando o log para 8216flush8217, o que significa que eles devem garantir que o registro de log de confirmação da transação tenha sido gravado de forma durável no disco. Eles não serão retomados até que o controlador de disco reconheça que o log foi escrito. Aqui está outro exemplo: Pedido na sessão 53 é um COMMIT e está aguardando. Ele está aguardando o log do banco de dados. A sessão 54 é um SELECT e está aguardando. Está esperando na fechadura. A Sessão 55 é um SELECT e está aguardando. Está esperando na fechadura. A sessão 56 é um SELECT e está aguardando. Está esperando na fechadura. A sessão 57 é um SELECT e está aguardando. Está esperando na fechadura. Observe que neste exemplo cada solicitação é realmente suspenso. Neste momento o servidor é basicamente não fazer nada. Temos 4 sessões (54, 55, 56 e 57) esperando no mesmo bloqueio (a linha com o recurso de bloqueio de chaves KEY: 5: 72057594039369728 (ac11a2bc89a9)). A sessão 54 está aguardando na sessão 55 e a sessão 55 está aguardando na sessão 53, o que significa que a sessão 54 está aguardando na sessão 53. Portanto, todo mundo espera na sessão 53, que aguarda que o controlador de disco grave um registro no disco. Também podemos ver, a partir da coluna waittime, quanto tempo cada sessão aguardou: cerca de 160 milissegundos. Observe como no exemplo anterior nós tínhamos apenas um pedido na verdade ficando sem 5. I8217m executando essas consultas em uma estação de trabalho com 8 núcleos, muita memória RAM e um disco decente, então há bastante hardware para levar essas solicitações, mas em vez disso São a maioria das vezes espera em vez de executar. Para tornar suas solicitações mais rápidas, você precisa que elas sejam executadas ao invés de esperar. Está tendo a maioria (ou mesmo todas) as solicitações esperando, ao invés de executar, algo incomum. Não é assim, é a norma. Uma carga moderada, você verá que a maioria dos pedidos estão aguardando e apenas alguns estão em execução. O que você precisa para ver para uma longa espera ou curta mas repetido espera que adicionar. Longas esperas indicam algum recurso que é mantido por longos períodos, e normalmente isso ocorre com bloqueios. Esperas curtas repetidas indicam um recurso que está sendo saturado, possivelmente um ponto quente para o desempenho. Antes de eu ir mais longe, eu só quero mostrar sys. dmoswaitingtasks. Que é um DMV do SQL Server projetado especificamente para mostrar as tarefas atualmente em espera: Sessão 53 está esperando o log para flush Sessão 57 está aguardando 40 ms para um bloqueio em uma linha e é bloqueado pela sessão 53 (que, portanto, deve ser possuir o bloqueio) A sessão 54 está esperando 37 ms para um bloqueio em uma linha e é bloqueada pela sessão 57 (mas a sessão 57 é por sua vez bloqueada pela sessão 53) A situação que temos aqui é muito semelhante à anterior, onde tivemos 4 sessões SELECT Bloqueado por um INSERT. Ele pode ver aqui duas solicitações bloqueadas por tentar ler uma linha (de modo que eles provavelmente são SELECT) e bloqueando sessão está aguardando sua transação para cometer de forma durável. A informação neste DMV é muito semelhante àquele em sys. dmexecrequests e mais tarde tem mais informações, mas há um caso importante onde a informação em sys. dmexecrequests é enganosa: consultas paralelas. Para entender os tipos de espera do CXPACKET, você precisa examinar as tarefas paralelas da criança Quando uma instrução pode se beneficiar da execução paralela, o mecanismo criará várias tarefas para a solicitação, cada uma processando um subconjunto de dados. Cada uma dessas tarefas pode executar em um CPUcore separado. A solicitação se comunica com essas tarefas usando basicamente uma fila produtor-consumidor. O operador de consulta que implementa essa fila é chamado de operador do Exchange (I8217m realmente simplificando aqui, leia O Operador de Paralelismo (também conhecido como Exchange) para uma descrição mais precisa). Se esta fila produtor-consumidor estiver vazia (o que significa que os produtores não introduziram quaisquer dados nela) o consumidor deve suspender e esperar eo tipo de espera correspondente é o tipo de espera CXPACKET. Solicitações que mostram esse tipo de espera estão realmente mostrando que as tarefas que deveriam ter produzido dados para consumir não estão produzindo nenhum (ou o suficiente) de dados. Essas tarefas de produtor, por sua vez, podem ser suspensas, esperando em algum outro tipo de espera e isso é o que está bloqueando seu pedido, não o operador de câmbio. Aqui está um exemplo: Aqui podemos ver que o pedido na sessão 54 tem 3 tarefas à espera, e enquanto o waittype da solicitação mostra CXPACKET. Uma das tarefas filho paralela está realmente esperando em um bloqueio de página. Estatísticas de espera agregadas: sys. dmoswaitstats O SQL Server agrega estatísticas sobre todos os tipos de espera e os expõe em um sys. dmoswaitstats. Se olhando para os pedidos em execução e as tarefas em espera nos mostrou o que estava sendo esperado a qualquer momento, as estatísticas agregadas dão um status cumulativo desde o início do servidor. Consultar este DMV é direto, mas interpretar os resultados é um pouco mais complicado: Primeiro, o que é o acordo com todos os tipos de espera que levam a parte lion8217s no topo do resultado DIRTYPAGEPOOL. REQUESTFORDEADLOCKSEARCH. LAZYWRITERSLEEP. Nós não vimos qualquer pedido sempre esperando por estes Aqui está o porquê: adicionando o sessão WHERE 50 filtraram para fora tarefas de fundo, threads internas para o SQL Server que são encarregados com vários trabalhos de manutenção. Muitas dessas tarefas em segundo plano seguem um padrão como 8220wait para um evento, quando o evento é sinalizado fazer algum trabalho, em seguida, esperar again8221, enquanto outros são padronizados em um padrão de ciclo de sono (8220sleep por 5 segundos, acordar e fazer algo, voltar Para dormir 5 segundos8221). Como nenhuma tarefa pode suspender-se sem fornecer um tipo de espera, há tipos de espera para capturar quando essas tarefas de plano de fundo se suspendem. Como esses padrões de execução resultam na tarefa sendo realmente suspenso quase todo o tempo, o tempo de espera agregado para esses tipos de espera muitas vezes supera todos os outros tipos de espera. A comunidade do SQL Server veio nomear esses tipos de espera 8216benign wait types8217 ea maioria dos especialistas tem uma lista de tipos de espera que simplesmente filtram, por exemplo, veja Filtrando as esperas benignas: Agora temos uma imagem mais coerente das esperas. A imagem informa-nos quais tipos de espera são mais prevalentes, de forma agregada, nessa instância do SQL Server. Isso pode ser um passo importante para identificar uma causa de estrangulamento. Além disso, interpretar os dados é dados é subjetiva. É o valor WRITELOG agregado de 4636805 milissegundos bom ou ruim Eu não sei é um agregado, acumulado desde que este processo está em execução, por isso, obviamente, aumentará contiguamente. Talvez ele tivesse acumulado valores de períodos em que o servidor estava funcionando sem problemas e de períodos em que estava funcionando mal. No entanto, agora eu sei que, de todos os tipos de espera 8216non bening8217, WRITELOG é aquele com o maior tempo total de espera. Eu também sei que maxwaittime para esses tipos de espera, então eu sei que houve pelo menos uma vez quando uma tarefa tinha que esperar 1,4 segundos para o log para flush. O waittaskcount diz-me quanto tempo uma tarefa esperou em um tipo de espera particular, e dividindo waittimemswaittaskcount me dirá o tempo médio que um tipo de espera particular foi esperado. E eu vejo vários tipos de espera relacionadas ao bloqueio no topo: LCKMS. LCKMIS e LCKMIX. Eu já posso dizer algumas coisas sobre o comportamento geral do servidor, a partir de um ponto de vista de desempenho: limpar o log é o recurso mais esperado no servidor, e contenção de bloqueio parece ser a próxima grande questão. Tipos de espera CXPACKET estão lá em cima, mas sabemos que a maioria das vezes os tipos de espera CXPACKET são apenas substituto para outros tipos de espera, como discutido acima. Como sobre aqueles outros com tipos na lista, como SOSSCHEDULERYIELD. PAGELATCHEXSH ou PAGEIOLATCHEXSHUP Tipos de espera comuns Para investigar os tipos de espera e os tempos de espera, agregados ou para uma consulta individual, é preciso entender o que significam os nomes dos tipos de espera. Muitos dos nomes são auto-descrevendo, alguns são crípticos em seu nome e alguns são para baixo direito enganosamente nomeado. Acho que o melhor recurso para ler uma descrição detalhada dos nomes é o Waits and Queues white paper. Ele tem uma lista alfabética de tipos de espera, descrição e que recurso físico é correlacionado com. O white paper é um pouco antigo (que abrange o SQL Server 2005), mas ainda altamente relevante. The wait type names for important resources has not changed since SQL server 2005, what you8217ll be missing will be newer wait type names related to post SQL Server 2005 features. The wait types are briefly documented also in the sys. dmoswaittypes topic on MSDN. Disk and IO related wait types PAGEIOLATCH This is the quintessential IO, data read from disk and write to disk wait type. A task blocked on these wait type is waiting for data to be transferred between the disk and the in-memory data cache (the buffer pool). A system that has high aggregate PAGEIOLATCH wait types is very likely memory starved and is spending much time reading data from the disk into the buffer pool. Be careful not to confuse this wait type with the PAGELATCH wait type (not the missing IO in the name). WRITELOG This wait type occurs when a task is issuing a COMMIT and it waits for the log to write the transaction commit log record to disk. High average wait times on this wait type indicate that the disk is writing the log slowly and this slows down every transaction. Very frequent waits on this wait type are indicative of doing many small transaction and having to block frequently to wait for COMMIT (remember that all data writes require a transaction and one is implicitly created for each statement if no BEGIN TRANSACTION is explicitly used). IOCOMPLETION This wait type occurs for tasks that are waiting for something else that ordinary data IO. Eg. loading an assembly DLL, reading and writing TEMPDB sort files. some special DBCC data reading operations. ASYNCIOCOMPLETION This wait type is mostly associated with backup, restore, database and database file operations. If your wait time analysis finds that IO and disk are important wait types then your should focus on analyzing disk activity . Memory related wait types RESOURCESEMAPHORE This wait type indicate queries that are waiting for a memory grant. See Understanding SQL Server memory grant. OLTP type workload queries should not require memory grants. if you see this wait type on an OLTP system then you need to revisit your application design. OLAP workloads often are in need of (sometimes large) memory grants and large wait times on this wait type usually point toward RAM increase upgrades. SOSVIRTUALMEMORYLOW Are you still on 32 bit systems Move on Network related wait types ASYNCNETWORKIO This wait type indicate that the SQL Server has result sets to send to the application but the application odes not process them. This may indicate a slow network connection. But more often the problem is with the application code, it is either blocking while processing the result set or is requesting a huge result set. CPU, Contention and Concurrency related wait types LCK Locks. All wait types that start with LCK indicate a task suspended waiting for a lock. LCKMS wait types indicate a task that is waiting to read data (shared locks) and is blocked by another task that had modified the data (had acquired an exclusive LCKMX lock). LCKMSCH wait types indicate object schema modification locks and indicate that access to an object (eg. a table) is blocked by another task that did a DDL modfication to that object ( ALTER ). PAGELATCH Do not confuse this wait types with the PAGEIOLATCH wait types. High wait times on these wait types indicate a hot spot in the database, a region of the data that is very frequently updated (eg. it could be a single record in a table that is constantly modified). To further analyze, I recommend the Diagnosing and Resolving Latch Contention on SQL Server white paper. LATCH These wait types indicate contention on internal SQL Server resources, not on data (unlike PAGELATCH they do not indicate a hot spot in the data). To investigate these waits you need to dig further using the sys. dmoslatchstats DMV which will detail the wait times by latch type. Again, the best resource is Diagnosing and Resolving Latch Contention on SQL Server white paper. CMEMTHREAD This wait type occurs when tasks block on waiting to access a shared memory allocator. I placed thi here, in concurrency section, and not in 8216memory8217 section because the problem is related to internal SQL Server concurrency. If you see high CMEMTHREAD wait types you should make sure you are at the latest SQL Server available Service Pack and Cumulative Update for your version, because some of these kind of problems denote internal SQL Server issues and are often addressed in newer releases. SOSSCHEDULERYIELD This wait type can indicate spinlock contention. Spinlocks are extremely light wait locking primitives in SQL Server used for protecting access to resources that can be modified within few CPU instructions. SQL Server tasks acquire spinlocks by doing interlocked CPU operations in a tight loop, so contention on spinlocks burns a lot of CPU time (CPU usage counters show 90-100 usage, but progress is slow). Further analysis need to be done using sys. dmosspinlockstats : RESOURCESEMAPHOREQUERYCOMPILE This wait type indicate that a task is waiting to compile its request. High wait times of this type indicate that query compilation is a bottleneck. For more details I recommend reading Troubleshooting Plan Cache Issues. SQLCLRQUANTUMPUNISHMENT This wait type occurs if you run CLR code inside the SQL Server engine and your CLR code does not yield the CPU in time. This results in an throttling of the CLR code (8220punishment8221). If you have CLR code that can potentially hijack the CPU for a long time you must call Thread. BeginThreadAffinity() . For more details I recommend watching Data, Faster: Microsoft SQL Server Performance Techniques with SQLCLR. Special wait types TRACEWRITE This wait type indicate that tasks are being blocked by SQL Profiler. This wait type occurs only if you have SQL Profiler attached to the server. This wait type occur often during investigation if you had set up a SQL Profiler trace that is too aggressive (gets too many events). PREEMPTIVEOSWRITEFILEGATHER This wait type occurs, among other things, when auto-growth of files is triggered. Auto-growth occurs when a insufficiently sized file is grown by SQL Server and is a hugely expensive event. During growth event all activity on the database is frozen. Data file growth can be made fast by enabling instant file growth, see Database File Initialization. But log growth cannot benefit from instant file initialization so log growth is always slow, sometimes very slow. Log Auto-Growth events can be diagnosed by simply looking at the Log Growths performance counter in the SQL Server, Databases Object. if is anything but 0 it means log auto-growth had occurred at least once. Real-time monitoring can be done by watching for Data File Auto Grow Event Class and Log File Auto Grow Event Class events in SQL Profiler. I did not cover many wait types here (eg. CLR . SQLCLR . SOSHOST . HADR . DTC and many more). If you encounter a wait type you do not understand, usually just searching online for its name will reveal information about what that wait type is what potential bottleneck it indicates, if any. Analyze disk activity: IO stats SQL Server needs to read and write to the disk. All writes (inserts, updates, deletes) must be written to disk. Queries always return data from the in-memory cache (the buffer pool) but the cache may not contain the desired data and has to be read from disk. Understanding if the IO is a bottleneck for your performance is a necessary step in any performance investigation. SQL Server collects, aggregates and exposes information about every data and log IO request. First thing I like to look at is sys. dmiovirtualfilestats . This DMV exposes the number of writes and reads on each file for every database in the system, along with the aggregated read and write IO 8216stall8217 times. Stall times are the total time tasks had to block waiting for transfer of data to and from disk. The per file IO stats are aggregated since server start up but be aware that they reset for each file if it ever goes offline. The total number of bytes transferred (reads and writes) is a good indicator of how busy is a database from IO point of view. The stalls indicate which IO subsytem (which disk) is busy and may even be saturated. Analyzing individual query execution When analyzing an individual statement or query for performance allows us to understand what the query is doing, how much is executing vs. what is waiting on. The most simple analysis is to use the execution statistics. These work by first running the appropriate SET STATISTICS. ON in your session and then executing the statement or query you want to analyze. In addition to the usual result, the query will also report execution statistics, as follows: SET STATISTICS TIME ON The query parse time, compile time and execution time are reported. If you execute a batch or a stored procedure with multiple statements then you get back the time for each statement, which helps determining which statement in a procedure is the expensive one. But we8217ll see that analyzing the execution plan reveals the same information, and more. SET STATISTICS IO ON The IO statistics show, for each statement, the number of IO operations. The result shows several metrics for each table touched by the statement : scan count Number of times the scans or seeks were started on the table. Ideally each table should be scanned at most once. logical reads Number of data pages from which rows were read from the in-memory cache (the buffer pool). physical reads Number of data pages from which data was had to be transferred from disk in-memory cache (the buffer pool) and the task had to block and wait for the transfer to finish. read-ahead reads Number of data pages that were asynchronously transferred from disk into the buffer pool opportunistically and the task did not had to wait for the transfer. LOB logicalphysicalread-ahead reads Same as their non-lob counterparts, but referring to reading of large columns (LOBs). There is also the SET STATISTICS PROFILE ON . which provides a detailed execution information for a statement, but much of the information overlaps with the execution plan info and I think analyzing the execution plan is easier and yields better information. Logical Reads are the best measure of a statement cost. A large logical reads value for a statement indicates an expensive statement. For OLTP environments the number of logical reads should be in single digits. Large logical reads count translate to long execution times and high CPU consumption even under ideally conditions. Under less ideal conditions some or all of these logical reads turn into physical reads which mean long wait times waiting for disk transfer of data into the buffer pool. Large logical reads are indicative of large data scans, usually entire table end-to-end scan. They are most times caused by missing indexes. In OLAP, decision and DW environments, scans and large logical reads may be unavoidable as indexing for an OLAP workload is tricky, and indexing for an ad-hoc analytic workload is basically impossible. For these later cases the answer usually relies in using DW specific technologies like columnstores . Information about query execution statistics can also be obtained using SQL Profiler. The SQL:StmtCompleted . SQL:BatchCompleted , SP:StmtCompleted and RPC:Completed capture the number of page reads, the number of page writes and the execution time of an individual invocation of a statement, a stored procedure or a SQL batch. The number of reads is logical reads. Note that monitoring with SQL Profiler for individual statement completed events will have effect on performance and on a big busy server it can cause significant performance degradation. You can also see the number of logical reads, reads and writes as it happens by looking at the sys. dmexecrequests but this information is visible only if while a request is running. Furthermore the number aggregate all statements in a request so you cannot determine the cost of an individual statement using this DMV. Analyzing individual query execution wait times If you need to drill down more detail that execution time and IO statistics for a query or for a stored procedure, the best information is the wait types and wait times that occurred during execution. Obtaining this information is no longer straightforward though, it involves using Extended Events monitoring. The method I8217m presenting here is based on the Debugging slow response times in SQL Server 2008 article. It involves creating an extended event session that captures the sqlos. waitinfo info and filter the extended events session for a specific execution session (SPID): With the Extended Events session created and started, you can now run the query or procedure you want to analyze. After that, stop the Extended Event session and look at the captured data: As you can see the Extended Events session captured each individual wait, each incident when the session was suspended. If we click on an individual captured event we see the event data XML: This is a 4 millisecond wait for an IX mode lock. Another 2 milliseconds had spent until the task resumed, after the lock was granted. You can shred out the XML into columns for a better view: Finally, we can aggregate all the data in the captured Extended Events session: This is an exceptional wealth of information about what happened during the execution of my request. 742 times it waited on the log commit to be written to disk for a total of 14 seconds wait time, 12 times it waited for a lock, 2 times for a page latch etc. If your curious, my workload I captured was a single row INSERT in a loop. Analyzing Execution Plans SQL Server can expose the actual execution plan for queries. The actual query tree gets serialized as XML and there are several ways to obtain it: SET SHOWPLANXML ON in the session you execute your query. Use the SQL Profiler, see Displaying Execution Plans by Using SQL Server Profiler Event Classes . Enable the Include Actual Execution Plan in SSMS, see Displaying Graphical Execution Plans . Use sys. dmexecqueryplan function to obtain the plan XML from a plan handle. The plan handle can be obtained from DMVs like sys. dmexecrequests (currently executing queries) or sys. dmexecquerystats (past executed queries). If you dont know anything about execution plans then focus on thick arrows I will not enter into much detail into how to analyze a query plan because the subject is way too vast for this article. Execution plans capture what the query did . namely what operators it executed, how many times was each operator invoked, how much data did each operator process and how much data it produced in turn, and how much actual time it took for each operation. Unlike the wait times analysis, analyzing the execution focuses on runtime and where was the actual CPU time consumed on. The graphical display of SSMS simplifies the information in an execution plan XML to make it more easily consumable and the graphical representation makes easy to stop the bottlenecks. Thick arrows represent large data transfers between operators, meaning that operators had to look at a lot of rows to produce their output. Needless to say, the more data an operator needs to process, the more time it takes. Hovering over operators display a popup info note with details about the operator. A large actual row count shows large amount of data being processed. The popup include also the estimated row count which is how much data SQL Server had predicted the operator will process, based on the table statistics. A wild discrepancy between actual and estimated often is responsible for bad performance and can be tracked to outdated statistics on the table. Another are of interest in the execution plan are the operators themselves. Certain operators are inherently expensive, for example sort operators. An expensive operator coupled with a large data input is usually the cause of a huge execution time. Even if you do no know which operators are expensive and which are not, the execution plan contains the cost associated with each operator and you can use this information to get an idea for where problems may be. Identifying problem queries SQL Server exposes the runtime execution statistics for most queries it had run in sys. dmexecquerystats . This DMV shows the number of times a query had run, totalmaxminlast active CPU time (worker time), totalmaxminlast elapsed wall-clock time, totalmaxminlast logical reads and logical writes, totalmaxminlast number of rows returned. This kind of information is a real treasure cove to identify problems in an application: Large execution count Queries that are run frequently are the most sensitive for performance. Even if they perform acceptable, if they are run often then small improvements in each individual execution can yield overall significant speed. But even better is to look, with a critical eye, into why is the query executed often. Application changes can reduce the number of execution count. Large logical reads A query that has to scan large amounts of data is going to be a slow query, there is no question about this. The focus should be in reducing the amount of data scanned, typically the problem is a missing index. A bad query plan can also be a problem. A high number of logical reads is accompanied by a high worker time but the issue is not high CPU usage, but the focus should be on large logical reads. Large physical reads This is basically the same problem as large logical reads, but it also indicates that your server does not have sufficient RAM. Luckily this is, by far, the easiest problem to solve: just buy more RAM, max out the server RAM slots. Youre still going to have a large logical reads, and you should address that problem too, but addressing an application design issue is seldom as easy as ordering 1TB of RAM. High worker time with low logical reads This case is not very common and indicates an operation that is very CPU intensive even with little data to process. String processing and XML processing are typical culprits and you should look at moving the CPU burn toward the periphery, in other words perform the CPU intensive operations on the client, not on the server. High elapsed time with log worker time A query that takes a long wall-clock time but utilizes few CPU cycles is indicative of blocking. This is a query that was suspended most of the time, waiting on something. Wait analysis should indicate the contention cause, the resource being waited for. High total rows count Large result sets requested by the application can be justified in an extremely low number of cases. This problem must be addressed in the application design. Finding a problem query using sys. dmexecquerystats becomes a simply exercise of ordering by the desired criteria (by execution count to find frequently run queries, by logical reads, by elapsed time etc). Tho get the text of the query in question use sys. dmexecsqltext . and to get the query execution plan use sys. dmexecqueryplan : If you do not know what kind of problem query to look for to start with, my advice is to focus in this order: High execution count. Identifying what queries are run most often is in my opinion more important that identifying which queries are particularly slow. More often than not the queries found to be run most often are a surprise and simply limiting the execution count yield significant performance improvements. High logical reads. Large data scans are the usual culprit for most performance problems. They may be caused by missing indices, by a poorly designed data model, by bad plans caused by outdated statistics or parameter sniffing and other causes. High elapsed time with low worker time. Blocking queries do not tax the server much, but your application users do not care if the time they waited in front of the screen for a refresh was spent active or blocked. Missing Indexes Repeatedly I said that performance problems are caused by missing indexes, and SQL Server has a feature aptly named Missing Indexes : The missing indexes feature uses dynamic management objects and Showplan to provide information about missing indexes that could enhance SQL Server query performance. Personally I seldom focus my analysis on the built in missing index feature. To start with the feature has known limitations. but more importantly is that the feature is starting with the solution, wo first searching for a cause. I always start with identifying problem queries first, searching for ones which would yield the highest bang for the buck. Once a problem query is identified as causing large scans, the missing index DMVs can be consulted to identify a candidate. But you should always take the recommendations with a grain of salt. The missing index feature does not know what the query is doing from an application business logic point of view. Reviewing the query can potentially find a better solution than what the automated missing index feature may recommend. Experienced administrators have in place automation that periodically inspects the missing index DMVs and find potential hot spots and problems. This is a very good proactive approach. But in most cases the performance problems are hot and immediate, and finding them using wait analysis or query execution stats is a more direct process. For a more in depth discussion of missing indexes DMVs I recommend spBlitzIndex . TEMPDB Performance tempdb is the special SQL Server database that is used to store any transient data, including things like temporary tables, static cursors, table variables, sort runs, internal worktables, the version store and piles more. Even though the problems that can occur with tempdb are ultimately CPU, IO or contention problems, tempdb deserves a special topic because some of these problems can be understood at a higher level as tempdb problems, as opposed to, say, low level latch contention problems. tempdb also tends to manifest a set of problems entirely of its own, specially due to the very high rate of creation and destruction of objects in tempdb. MSDN has an Optimizing tempdb Performance topic which contains some practical advice, and Cindy Gross has compiled a collection of SQL Server TempDB IO Best Practices . Make each tempdb data file the same size In general I tried to emphasize the importance of measuring and understanding SQL Server performance characteristics instead of blindly following advice. But here is one piece of advice that I always give, wo any secondary consideration: make sure tempdb is stored on files of identical size, with identical growth characteristics. Ive spent just too many hours chasing difficult to diagnose issues to discover that theyre ultimately caused by unaligned tempdb files. I would also recommend as mandatory reading Martin Smiths answer to this dba. stackexchange question:Whats the difference between a temp table and table variable in SQL Server. The answer goes in great detail not only from a tempdb usage point of view, but also impact on locking, compilation, parallelism and indexability, all critical components of performance. SQL Server Performance Counters Performance counters offer a different perspective into SQL Server performance than the wait analysis and execution DMVs do. The biggest advantage of performance counters is that they are extremely cheap to collect and store so they lend themselves as the natural choice for long term monitoring. Looking at current performance counter numbers can reveal potential problems, but interpreting the values is subject to experience and there are a lot of lore and myths on certain magical values. For a analyzing performance problems on a server that you have access and is misbehaving right now . a more direct approach using wait analysis and execution stats is my recommended approach. Where performance counters shine is comparing current behavior with past behavior. for which, obviously, you need to have performance counters values collected and saved from said past. These should be part of an established baseline, which has captured the server behavior under workload when the server was behaving correctly and was not experiencing problems. If you do no have a baseline, consider establishing one. The performance counters infrastructure workhorse is the logman. exe utility. For a quick intro, read Two Minute Drill: LOGMAN. EXE. I recommend you familiarize yourself with this tool. I favor it over the graphic interface ( perfmon. exe ) because, as a command line interface (CLI) tool, it allows for easy scripting. Telling someone run the attached script is always easier that telling click the button, select A from the list, then choose menu B, click twice in the middle of the red square etc etc. The graphic perfmon. exe tool is the tool of choice for displaying performance counters. Performance counters, if collected at sufficient resolution, are also an excellent tool for determining correlation. The graphic display makes easy to spot counters that tend to spike together, or in an anti-correlated pattern (one spikes the other dips). Other patterns also become evident in the graphic visualization, like lag (eg. a counter spike is always preceded by another one increasing). Short of having a data scientist doing factorial analysis on your counters, your own eyes are the best analysis tool. SQL Server usage performance counters Batch Requestssec If you want one counter to best represent the overall performance throughput of your server, this is it. It captures the workload pressure (how many requests the application is making), and how is the server coping with the workload. Trends in this counter need to be further analysed: an increase in the value could mean the server is performing better, because it can accept and handle more requests, but it can also mean the application is sending more requests (bigger, heavier workload), for example more your application is serving more client requests. Transactions The number of active transactions (any type). Be aware that there are at least two counters named Transactions . one in the SQL Server:General Statistics object and another one in the SQL Server:Transactions object. Im referring to the later one. Knowing the number of active transactions is important to set a background on your understanding of the workload. Processes blocked This is specially useful when troubleshooting spikes or jagged performance. For example if you can correlate moments where performance drops with moments when the blocking increase you have found an area to focus your investigation on: blocking. Errorssec Understanding whether errors occur in your workload is a key point in performance troubleshooting. Eliminating errors is an easy path toward performance increase. Number of Deadlockssec Related to the number of Errorssec, monitoring the rate of deadlocks can quickly point toward a performance problem. Is hard to believe that a you are not aware that deadlocks are occurring, given the impact they have on the application. But Ive seen cases where the deadlocks were quietly swallowed in the application or simply there was no communication between the organization monitoring the application and the DBA organization monitoring the server. Log Growths I can hardly think of any event in SQL Server execution that has a more dramatic impact on performance than log growth. During log growth every write request effectively freezes as it waits for available log to generate the write-ahead log records necessary to record any update. Log growth cannot use instant file initialization so it must wait until the log file is grown and the newly allocated disk space is filled with 0s. Your application users might as well watch paint dry. Often cases this is aggravated by a log file gone out of control with 10 increments which is now growing in hundreds of megabytes or even gigabytes at a time. The impact on performance is usually devastating, and is also difficult to track down after the fact. It shows as a sudden performance dip (actually often a complete freeze) that lasts few seconds or even minutes, and then everything is again fast until the next log growth event. The article How to shrink the SQL Server log offers an explanation why log growth can occur. Data and log growth can also be tracked by non-zero values for the PREEMPTIVEOSWRITEFILEGATHER wait info statistics. Data File(s) Size (KB) Monitoring the data size is interesting on its own, but there is another reason why this counter should be collected: it can show when database growth events occur. Unlike Log Growths, data growths do not have counter of their own (shame), but the events can be deduced from sudden jumps in the data file size. Note that data growth can leverage instant file initialization so the impact may not be as dramatic. You should always ensure instant file initialization is enabled for SQL Server, if is not the impact may be serious, specially if you encounter tempdb growth events (which in my experience occurs on most deployments). IO related performance counters Page readssec The rate at which data pages are read from disk. This will be the main driver factor for disk read IO. Normally your workload should read only a very small amount of data from disk, the data should be cached in memory. Constant reading is a sign of data scans (missing indexes) andor insufficient memory (RAM). Log Flushessec Together with the Log Flush Waitssec . Log Flush Wait Time and Log Flush Write Time (ms) you can quickly get a feel of how much blocking occurs due to transactions waiting to commit (log flush events). For well written OLTP intensive applications the rate at which transactions can be committed is often the main performance bottleneck. Page writessec I intentionally put data write after log writes, because most times is the log that is the problem. Only few workloads are data-write heavy. However data-write can interfere with disk IO, specially with log writes, and cause delays in log flush waits that are very visible as application performance drops. The typical pattern is some longer time of good performance with periodic short times of performance dip. Long and short are relative, sometime you see 1-2 minutes long and 10-15 seconds as short, but other times is 10-15 minutes long and 1-2 minutes short. The jagged pattern is the key, with a strong correlation between performance dips and data-writes spikes. The pattern is caused by checkpoints and the timing is driven by the recovery interval option. Looking at Checkpoint pagessec will quickly corroborate this hypothesis. Checkpoint pagessec This counter is the usual culprit to blame Page writessec counter spikes. Log write waits This performance counter exposes the wait info object for log writes. The advantage of using performance counters vs. the DMVs is to leverage the performance counters infrastructure, namely collecting the values in a continuous fashion using the operating system collectors. Page IO latch waits Same as above, this is a wait info exposed through performance counters. Strictly speaking this does not measure IO, it measures only when blocking caused by IO occurs. You cannot distinguish between read IO and write IO with this counter. BackupRestore Throughputsec Often the IO requirements of the maintenance backup operations are forgotten when doing performance analysis. Understanding the additional IO pressure a backup adds and tracking it through this counter is important on its own, but is even more important to understand correlation between spikes in this counter and general performance degradation. This is a scenario Ive see all too often: the maintenance plan kicks in and the overall throughput dips significantly, but nobody understands why. PhysicalDisk Object This category is the operating system counter for monitoring the IO operations. The typical focus is not on throughput, but on queue length, indicating IO operations that take a long time. For a more thorough explanation, read Windows Performance Monitor Disk Counters Explained. Monitoring Usage and Monitoring Queue Length. Operating system exposes IO counters for each individual drive separately, always corroborate findings with the SQL Servers own IO stats as reported by the virtual IO stats DMVs. Another good resource to read is this SQL Server Technet wiki page: Monitoring Disk Usage. ProcessIO Data Operationssec The Process counter category has a different instance for each OS executing process and is very important to analyze when the overall IO on the system does not match the IO as reported by SQL Server. Often times youll discover that another process is causing IO making the disks busy and increasing the response time in SQL Server. Unfortunately monitoring this counter is difficult long term because instances come and go as OS executable processes start and terminate. Pages sec Despite being in the Memory counter category, understanding if and when paging occurs is critical in understand IO performance as paging can introduce a large spike in IO requests. See Monitoring Paging. Memory related performance counters Page life expectancy PLE is measuring the average time a page stays cached in memory before being evicted because of the need to read more data from disk. The DBA lore give this counter a magical value of 300 (5 minutes). This originates with the The 5 Minute Rule for Trading Memory for Disc Accesses paper published back in 1985, then revised as The Five-Minute Rule Ten Years Later and again revised as The Five-Minute Rule 20 Years Later. This 2011 SQL Magazine QA with Paul Randal goes on to say: The value of 300 seconds is a Microsoft recommendation in a white paper that was written five years ago. Its not a relevant guideline nowadays, and even back in 2006 I would have argued that aiming for a set value isnt a valid recommendation. Analyzing PLE is useful as it gives a single value to get a feel for the server memory health, but do not panic if the value is 297 and relax is is 302. More important is to understand trends and the dynamic of this value. A jagged pattern (periodic dips) indicates something that comes and creates a great disturbance in the server memory, perhaps a periodic query that causes a scan, evicting much of the cached data. An overall trend in reducing PLE indicates your workload increases but the server hardware is the same and is a great indicator of potential future problems, better to alleviate now. On machines with NUMA nodes, which in this day and age means pretty much every server, you should pay attention to the PLE per node using the SQL Server:Buffer NodePage life expectancy counter. Read Page Life Expectancy isnt what you think. SQL Server:Memory Manager This category tracks the how is memory used inside the SQL Server process. the absolute values themselves are somehow esoteric, but having a baseline to compare against and looking at long term trends is a great way to determine what changed and what components start putting SQL Server under memory pressure. Memory Grants Pending Im calling out this counter in the Memory Manager category because is particularly important in understanding blocking caused by memory grant waits. Memory grants are only needed for certain query operators and queries requesting large memory grants tend to wreak havoc with your SQL Server performance. Read Understanding SQL server memory grant for more details. Memory grant queue waits Related to the above this is the memory grant wait info exposed as a performance counter. ProcessPrivate Bytes The Process category OS performance counter has an instance for each process in the system and allows you to easily track other processes that may consume memory, causing external memory pressure for SQL Server and ultimately performance degradation. CPU performance counters Processor Object I really hope this one doesnt need an explanation. The OS reported CPU usage counter. Page lookupssec Im mentioning this counter here, in the CPU category, because it is a critical counter to analyze when considering CPU usage in SQL Server. When this counter correlates strongly with CPU usage is a clear indicator of why the CPU usage increased: more pages are scanned, the queries simply look at more data and therefore consume more CPU. The usual culprit is, again, table scans (ie. missing index, poor data modeling or a bad query plan). Process Processor Time The Process category OS performance counter has an instance for each process in the system and allows you to easily track other processes that may consume CPU, causing SQL Server performance degradation. SQL Server blocking related performance counters SQL Server:Locks Monitoring and analyzing the locking performance counters can reveal if lock related blocking is a significant cause of performance problems. Look specially at the Lock Wait Time (ms) value, it will give you an insight on how much percent of the time is spent by queries being blocked waiting for locks. You wont be able to determine exactly what query is blocking what, but knowing that lock waits are significant allows you to focus on analyzing the block chains. spwhoIsActive is a great tool for that. SQL Server:Wait Statistics Most important wait info types are reported also as performance counters. Analyzing these is similar to analyzing the wait statistics using the DMVs, but the performance counters have the advantage of being available in the whole performance counters toolset ecosystem, making easy to collect, store and analyze. Not all wait info types are exposed as performance counters. SQL Server:Latches If you see high Average Latch Wait Time (ms) values you should perform an investigation to find the problem latches. I recommend you read Diagnosing and Resolving Latch Contention on SQL Server. Network performance counters ProTip: Network is not your bottleneck. Network performance comes into picture usually only for deployments using Database Mirroring or Availability Groups. These features are indeed network hungry and require a fast, high bandwidth-low latency connection between the nodes. Furthermore, both DBM and AG tend to push their problems up the stack, resulting in performance degradation when the network traffic cannot keep up with the workload. Network IO waits This counter shows the wait info stats related to the server being blocked because the client is not consuming the result sets. Most often this does no indicate a network problem, but an application design problem: the client is requesting large result sets. Send IO bytessec and Receive IO bytessec These counters show the network traffic generated by Service Broker and Database Mirroring. Availability Groups have a more specialized counter category in SQL Server:Availability Replica . Network Interface This is the performance counter where all the network activity is exposed, from all processes. IP Object. TCP Object These categories are important for monitoring the health of the traffic, not the amount. Specifically they can indicate network errors, dropped packets and retries. Defining your own counters You can actually report custom performance counters from T-SQL. The SQL Server:User Settable is specifically designed for this and allows you to report up the 10 user defined values to be monitored and collected by the system performance counters tools. You publish counter values by using spusercounter1 through spusercounter10 : For application developers is also possible to add performance counters to the application itself. Publishing performance counters from managed () application code is trivially easy, and you can automate the somehow boring process of declaring them, see Using XSLT to generate Performance Counters code . Advanced Performance Analysis Developers are familiar with performance analysis using sample profiling, with tools like VTune. F1 or WPA. The sample profiling technique consist of periodically taking a stack sample of each thread in the profiled process, up to several hundred times per second. The process is non-invasive, it does not require a specially instrumented process, any process can be profiled. It is also fairly lightweight, taking several hundred samples per second has no measurable impact on the performance of the process being sampled. The trick is interpreting the observations. Using the Microsoft public symbols server you can resolve the collected samples to actual function names in the source code (note that in my experience F1 and WPA do a much better job at handling the LTCG code of SQL Server than VTune does). With some common sense, one can make an educated guess what exactly was the SQL Server doing during the period being sampled. This technique makes sense only if you are seeing significant CPU usage. If the performance is bad and you see slow queries but low CPU usage it means the queries are waiting and you should focus instead on wait info analysis. Sample profiling of a blocked process will reveal nothing useful. WPA is one of my favorite performance tools and it can do more than just sample profiling. It can visualize XPerf ETW traces and this makes it extremely powerful. Existing documentation and whitepapers for WPA tends to focus on multimedia and game development, but if you are and adventurous spirit and want to learn about how your favorite database works, using WPA to analyse SQL Server works can reveal a great deal of information and help you spot performance improvements that can be leveraged in your application. One of my favorite use cases for WPA is to collect IO activity (this will collect every single IO request ) and then visualize the Disk Offset as a graph. This is an incredibly simple, yet powerful, way to visualize random IO vs. sequential IO patterns. If you have a feeling WPA and XPerfETW are somehow related to SQL Server Extended Events, that is because they are, very much so. Finally, some readings to get you started on using WPA: The USE method USE stands for Utilization . Saturation and Errors . and the USE Method is a formalized approach to investigating performance troubleshooting. Its focus is on identifying resources in a system and then for each one measure the utilization (percent in use compared to maximum bandwith), the saturation of the resource (extra work queued because the resource cannot service due to 100 utilization) and errors. When used in SQL Server context, the tricky part is to identify the resources and how to measure the utilizationsaturationerrors. Some resources are obvious: disk, CPU, memory, network. Sometimes even simply starting with the USE method and measuring these hardware parameters is a good way to start a performance investigation, at the very least youll know what hardware component is saturated, if any. But knowing how SQL Server works can reveal more detailed, internal resources that can be analyzed. In Understanding how SQL Server executes a query I have shown this image: Many of the components in the image above can be investigated as Resources in the USE method. Applying this methodology requires some thinking outside the box since you will not find much references online about how to apply the USE method to SQL Server, what resources to look for and how to measure the utilization, saturation and errors for each. I myself do not have a clear picture yet for each resource involved and Im still collecting facts and know-how on the subject. But I like the USE approach because it gives a method, a strategy to follow in troubleshooting performance. Comments are closed.