Artigos Banco de Dados T-SQL: análises e otimizações de CPU
Por que eu devo ler este artigo:O artigo demonstra algumas técnicas de otimização de código SQL para reduzir o consumo de CPU e, consequentemente, otimizar a performance do ambiente. Para isso, diversos conceitos relacionados ao consumo de CPU serão explorados, capacitando o leitor a analisar e elaborar seus próprios métodos de otimização. Saber otimizar código é importante em todo o ciclo de vida de uma aplicação. A performance da mesma é diretamente afetada pelo consumo de recursos, como a CPU, e por isso, entender como se pode analisar e otimizar queries contribui para o desempenho do banco de dados e do sistema como um tudo.

Central Processor Unit (ou CPU) é um dos dispositivos mais importantes que existem em um computador. É lá que todos os outros dispositivos são coordenados e onde uma série de operações são realizadas, como soma, subtração, leitura e armazenamento de dados, etc. Essas operações são realizadas através das instruções, que são apenas números que ativam certos circuitos do processador.

As linguagens de programação são interpretadas ou compiladas, resultando em instruções que a CPU entende. Um simples código escrito em C++ ou T-SQL pode resultar na execução de milhões de instruções. Na grande maioria dos casos, quanto mais instruções, mais demorado aquele código irá rodar. Otimizar o uso de CPU de um código significa reduzir o número de instruções que são executadas, ou escolher um conjunto de instruções que resultem em menos tempo de processamento.

O SQL Server possui componentes que fazem a interpretação do código T-SQL e, baseado nas cláusulas e comandos utilizados, executam as instruções adequadas. Assim como qualquer outro processo no Windows, o SQL Server possui threads e está sujeito aos limites e configurações do ambiente, e, portanto, compreender certos mecanismos e conceitos do sistema operacional é importante para efetuar otimizações em scripts.

Otimizar o consumo de CPU é um desafio, principalmente em código T-SQL. Antes de qualquer ação, é preciso compreender como o sistema operacional e as ferramentas de análise disponibilizam e mensuram o gasto de CPU para que se possa direcionar as técnicas de otimização. O Windows e o SQL Server fornecem várias ferramentas para auxiliar no processo de análise. Este artigo irá explorar os conceitos básicos, técnicas e ferramentas que o desenvolvedor ou o DBA podem utilizar para tornar a utilização de CPU mais eficiente em suas aplicações. Todos os exemplos funcionam a partir da versão 2005 e os casos específicos de uma versão serão informados.

Processador, cores, CPU e hyper thread

É importante definir os termos quando se fala em CPU, pois diferentes fontes os usam como se fossem a mesma coisa. Os próximos parágrafos irão definir os termos a serem usados no artigo.

Processador se refere ao dispositivo acoplado diretamente no computador, especificamente na placa mãe. Podem existir um ou mais processadores em um computador. Cada processador possui um ou mais cores (núcleos). O core é a unidade de processamento. Ele é capaz de realizar as operações independentemente dos outros Cores. O core é capaz de executar um grupo de instruções por vez, mas há arquiteturas, como o Simultaneous multithreading (SMT), que otimiza o uso do core, dando a impressão de que ele executa dois conjuntos de instruções por vez. Uma famosa implementação dessa arquitetura é o Hyper Thread, da Intel.

Para o sistema operacional, um processador com dois cores, e com hyper thread habilitado, quatro cores serão visualizados (dois cores físicos, cada um com dois cores lógicos, resultando em quatro cores lógicos). Na maioria dos casos, o SMT fornece um ganho considerável de performance, mas podem existir casos onde ele atrapalha, devido ao uso de recursos compartilhados, entre outros fatores.

Para esse artigo, o termo CPU será usado frequentemente e se refere a um core enxergado pelo sistema operacional, seja ele físico ou lógico.

Processos e threads

Um dos papéis do sistema operacional é coordenar a execução das instruções de diferentes programas. O Windows faz isso por meio de processos e threads. O processo é uma estrutura do Windows que contém diversos elementos, como memória, código (as instruções), etc. Todo processo possui pelo menos uma thread.

A thread é uma estrutura que o Windows utiliza para, principalmente, organizar a execução de instruções. Quando se diz que “uma thread está executando”, significa que a CPU está executando as instruções associadas àquela thread. Basicamente, as instruções que ficam salvas em executáveis, ou em DLLs, são carregadas para a memória e o Windows é responsável por preparar e configurar a primeira thread para executar corretamente essas instruções. O programa pode, usando serviços do sistema operacional (ex.: função CreateThread, da API do Windows) criar mais threads. A thread contém todas as informações necessárias para que as execuções das instruções (BOX 1) aconteçam e que permita ao Windows interromper e continuar a execução da thread quando, por exemplo, uma outra thread de maior prioridade precisar ser executada. Threads de diferentes processos podem concorrer por uma CPU e o Windows contém todos os mecanismos que coordenam essa concorrência.

Apenas uma thread pode estar executando em cada CPU por vez. Se há quatro CPUs disponíveis, haverá no máximo quatro threads em execução (lembre-se que, nesse artigo, o termo CPU se refere a CPU lógica enxergada pelo sistema operacional). O Windows armazena o tempo total de CPU usado para cada thread. Por exemplo, se uma thread executa por cinco milissegundos, é interrompida, e depois executa por mais 10 milissegundos, então o tempo total gasto pela thread até o momento são de 15 milissegundos.

O SQL Server é um processo “multi-thread”, o que significa que ele possui diversas threads trabalhando para executar o código T-SQL e garantir seu funcionamento.

BOX 1. Threads e execução de instruções

Ao executar uma instrução, a CPU obtém o código da próxima instrução da memória. Um registrador especial da CPU, chamado “Instrunction Pointer” (IP), contém o endereço da próxima instrução a ser executada. À medida que a CPU carrega a instrução da memória, ela incrementa o IP, de forma que contenha o próximo endereço da instrução a ser executada. Há inclusive instruções que alteram o valor do IP, permitindo à CPU buscar a próxima instrução em um endereço diferente, alterando dessa forma o fluxo (muito utilizado para implementar estruturas condicionais como IF). Assim, “executar uma tarefa” se resume a obter a próxima instrução da memória e executá-la, repetindo esse processo até que a última instrução do programa seja executada.

Além do IP, há outros registradores que podem conter valores necessários para a execução de uma instrução. Programas são isso, apenas a execução de uma instrução após a outra, e diferentes programas possuem sequências diferentes. Uma thread no Windows é uma estrutura de dados, semelhante a um struct do C/C++ ou a uma tabela, que fica na memória do Kernel do Windows. Entre as diversas informações dessa estrutura, estão os registradores da CPU. A operação de “retirar” uma thread da CPU é salvar todas as informações dos registradores nessa estrutura, incluindo o IP. Assim, o Windows pode interromper uma determinada sequência de execução, iniciar outra (carregando as informações salvas na estrutura respectiva), e posteriormente retomar a sequência, bastando apenas recarregar os valores dos registradores. Isso é a troca de contexto (Context Switch). As instruções são carregadas para a memória quando o processo se inicia e podem ser carregadas sob demanda, com o uso de DLLs, por exemplo. Todo processo tem uma memória associada e as instruções são carregadas nessa memória. Assim, as threads de um determinado processo sempre apontam para os endereços de memória associados com o mesmo. Você pode obter muito mais detalhes dessa estrutura no livro Windows Internals.

O uso de CPU

Há várias ferramentas disponíveis para se medir o consumo de CPU no Windows. Vale citar o Process Explorer (link para download na seção Links), Task Manager (Gerenciador de Tarefas) e o perfmon (ou System Monitor).

Essas ferramentas geralmente apresentam o consumo de CPU como uma porcentagem. Essa informação indica quanto tempo a CPU esteve ocupada durante um intervalo de tempo. Uma CPU ocupada está executando alguma instrução. Por exemplo, se uma CPU está em 50% durante um intervalo de um segundo, significa que a CPU passou 500 milissegundos processando instruções (500ms/1000ms). A mesma lógica pode ser aplicada para o cálculo de uso de CPU de uma única thread: se uma thread gasta 70% de CPU em um intervalo de um segundo, isso significa que a thread gastou 700 milissegundos executando instruções naquele intervalo. A fórmula está descrita a seguir:

Tempo CPU Gasto
Intervalo

Conforme mencionado, o Windows registra o tempo total de CPU gasto para cada thread existente desde a criação da mesma. Essas informações são armazenadas nas estruturas internas que somente o código do sistema tem acesso. Uma aplicação que deseja ter acesso a essa informação pode usar funções da API do Windows como GetThreadTimes e QueryThreadCycleTime. Como é um valor acumulado durante toda a vida da thread, obter o total de CPU usado em um intervalo de um segundo, por exemplo, segue um algoritmo bem simples:

  • Tempo T1: obter o gasto total de CPU até o momento;
  • Aguardar um segundo;
  • Tempo T2: obter o gasto total de CPU até o momento;
  • Total de CPU usado no intervalo: T2-T1.

Para obter o tempo gasto em um intervalo qualquer, basta uma subtração. A Figura 1 ilustra isso melhor.

Gasto de CPU em um intervalo

Figura 1. Gasto de CPU em um intervalo

Veja que o ciclo de vida de uma thread ao longo de três segundos e a cada segundo, o gasto de CPU é medido subtraindo o valor de CPU atual pelo valor de CPU anterior. Os retângulos de cor azul indicam o tempo gasto pela thread executando algo. Os retângulos com a cor cinza indicam o tempo que a thread está fora da CPU. O texto “CPU:” indica o tempo total de CPU gasto pela thread. No instante “0”, a thread é criada e o tempo total de CPU é 0. Após um segundo, o tempo total de CPU para a thread foi de 500 milissegundos, que foram os 500ms gastos no final do intervalo entre 0s e 1s (representado pelo retângulo azul). No intervalo entre 1s e 2s, não houve qualquer gasto de CPU pela thread, porém, como o Windows guarda o tempo total gasto, o valor ainda permanece em 500. Quando se passa três segundos desde que a thread foi criada, o tempo total de CPU passou a ser 1500. A diferença entre o tempo coletado no instante 3 e o tempo coletado no instante 2 revela que a thread gastou 1000 milissegundos de CPU, ou, um segundo nesse intervalo. Como a coleta é feita a cada um segundo (intervalo de monitoramento), então a thread gastou 100% de CPU entre o instante 3 e 2, gastou 0 % de CPU entre o instante 2 e 1, e 50% de CPU entre o instante 1 e 0. Essa fórmula simples pode ser aplicada a praticamente qualquer caso em que se deseje mensurar o gasto de CPU em um determinado intervalo. Posteriormente nesse artigo, será demonstrada uma variação desse exemplo para calcular o gasto de CPU de um trecho de um script T-SQL.

As ferramentas que exibem o consumo de CPU seguem a mesma lógica, medindo o tempo gasto de CPU em um intervalo. Também, boa parte dessas ferramentas permitem alterar esse intervalo. Isso significa que o percentual de uso de CPU pode mudar dependendo do intervalo utilizado. Por isso é importante certificar-se de que, ao comparar o gasto de CPU de diferentes ambientes, o intervalo usado seja igual em todos.

No caso de computadores com mais de uma CPU, o consumo de CPU total da máquina é uma média: em um computador com duas CPUs, onde uma está em 100% e a outra em 0%, o consumo total será de 50%. Se as duas CPU’s estiverem em 50%, o consumo total também será de 50%. A fórmula é apresentada a seguir:

fórmula

É importante entender que existem duas métricas a respeito do gasto de CPU: tempo total e uso durante um intervalo. O que as ferramentas geralmente mostram é o segundo caso. Mas o primeiro merece sua atenção pois é o valor reportado pelas DMV’s do SQL Server. O tempo total de CPU é o tempo gasto desde a criação da thread. No caso do SQL Server, é o tempo total gasto desde o início de uma requisição, como um comando de SELECT, ou o total, somadas todas as execuções de uma query.

Há ainda o tempo de espera, que é o tempo que a thread passou aguardando por recursos, como I/O ou locks. O tempo de espera mais o tempo total de CPU é o tempo total gasto para executar uma tarefa e nesse artigo será chamado de “duração”:

duração = Tempo CPU + Tempo de Espera

                    

    
    

        Quer ler esse conteúdo completo?
        Tenha acesso completo