Como ajustar o Microsoft SQL Server para desempenho

Publicados: 2022-03-11

Para reter seus usuários, qualquer aplicativo ou site deve ser executado rapidamente. Para ambientes de missão crítica, alguns milissegundos de atraso na obtenção de informações podem criar grandes problemas. À medida que o tamanho do banco de dados cresce dia a dia, precisamos buscar os dados o mais rápido possível e gravar os dados de volta no banco de dados o mais rápido possível. Para garantir que todas as operações estejam sendo executadas sem problemas, precisamos ajustar nosso servidor de banco de dados para desempenho.

Neste artigo, descreverei um procedimento passo a passo para ajuste básico de desempenho em um dos principais servidores de banco de dados do mercado: Microsoft SQL Server (SQL Server, abreviado).

#1 Encontrando os culpados

Como acontece com qualquer outro software, precisamos entender que o SQL Server é um programa de computador complexo. Se tivermos um problema com ele, precisamos descobrir por que ele não está funcionando como esperamos.

Do SQL Server, precisamos extrair e enviar dados o mais rápido e preciso possível. Se houver problemas, alguns motivos básicos e as duas primeiras coisas a serem verificadas são:

As configurações de hardware e instalação, que podem precisar de correção, pois as necessidades do SQL Server são específicas
Se fornecemos o código T-SQL correto para o SQL Server implementar

Embora o SQL Server seja um software proprietário, a Microsoft forneceu várias maneiras de entendê-lo e usá-lo com eficiência.

Se o hardware estiver OK e a instalação tiver sido feita corretamente, mas o SQL Server ainda estiver lento, primeiro precisamos descobrir se há algum erro relacionado ao software. Para verificar o que está acontecendo, precisamos observar o desempenho de diferentes threads. Isso é obtido calculando as estatísticas de espera de diferentes encadeamentos. O SQL Server usa threads para cada solicitação do usuário, e o thread nada mais é do que outro programa dentro de nosso complexo programa chamado SQL Server. É importante observar que esse encadeamento não é um encadeamento do sistema operacional no qual o SQL Server está instalado; ele está relacionado ao thread SQLOS, que é um pseudo sistema operacional para o SQL Server.

As estatísticas de espera podem ser calculadas usando sys.dm_os_wait_stats Dynamic Management View (DMV), que fornece informações adicionais sobre seu estado atual. Existem muitos scripts online para consultar essa visualização, mas o meu favorito é o script de Paul Randal porque é fácil de entender e possui todos os parâmetros importantes para observar as estatísticas de espera:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

Quando executamos este script, precisamos nos concentrar nas linhas superiores do resultado porque elas são definidas primeiro e representam o tipo de espera máxima.

Precisamos entender os tipos de espera para que possamos tomar as decisões corretas. Para aprender sobre os diferentes tipos de espera, podemos acessar a excelente documentação da Microsoft.

Vamos dar um exemplo onde temos muito PAGEIOLATCH_XX . Isso significa que um thread está aguardando leituras de página de dados do disco para o buffer, que nada mais é do que um bloco de memória. Devemos ter certeza de que entendemos o que está acontecendo. Isso não significa necessariamente um subsistema de E/S ruim ou memória insuficiente, e aumentar o subsistema de E/S e a memória resolverá o problema, mas apenas temporariamente. Para encontrar uma solução permanente, precisamos ver por que tantos dados estão sendo lidos do disco: Que tipos de comandos SQL estão causando isso? Estamos lendo muitos dados em vez de ler menos dados usando filtros, como cláusulas where ? Estão ocorrendo muitas leituras de dados devido a varreduras de tabela ou varreduras de índice? Podemos convertê-los em buscas de índice implementando ou modificando índices existentes? Estamos escrevendo consultas SQL que são mal compreendidas pelo SQL Optimizer (outro programa dentro do nosso programa SQL Server)?

Precisamos pensar de diferentes ângulos e usar diferentes casos de teste para encontrar soluções. Cada um dos tipos de espera acima precisa de uma solução diferente. Um administrador de banco de dados precisa pesquisá-los completamente antes de tomar qualquer ação. Mas na maioria das vezes, encontrar consultas T-SQL problemáticas e ajustá-las resolverá de 60 a 70 por cento dos problemas.

#2 Encontrando consultas problemáticas

Como mencionado acima, a primeira coisa que podemos fazer é pesquisar consultas problemáticas. O código T-SQL a seguir encontrará as 20 consultas com pior desempenho:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

Precisamos ter cuidado com os resultados; mesmo que uma consulta possa ter um tempo de execução médio máximo, se for executada apenas uma vez, o efeito total no servidor será baixo em comparação com uma consulta que tenha um tempo de execução médio médio e seja executada muitas vezes em um dia.

#3 Consultas de ajuste fino

O ajuste fino de uma consulta T-SQL é um conceito importante. A coisa fundamental a entender é quão bem podemos escrever consultas T-SQL e implementar índices, para que o otimizador de SQL possa encontrar um plano otimizado para fazer o que queríamos. A cada nova versão do SQL Server, obtemos um otimizador mais sofisticado que cobrirá nossos erros ao escrever consultas SQL não otimizadas e também corrigirá quaisquer bugs relacionados ao otimizador anterior. Mas, não importa quão inteligente seja o otimizador, se não pudermos dizer a ele o que queremos (escrevendo consultas T-SQL apropriadas), o otimizador SQL não conseguirá fazer seu trabalho.

O SQL Server usa algoritmos avançados de pesquisa e classificação. Se somos bons em algoritmos de pesquisa e classificação, na maioria das vezes podemos adivinhar por que o SQL Server está realizando uma ação específica. O melhor livro para aprender mais e entender esses algoritmos é The Art of Computer Programming , de Donald Knuth.

Quando examinamos as consultas que precisam ser ajustadas, precisamos usar o plano de execução dessas consultas para descobrir como o SQL Server as está interpretando.

Não posso cobrir todos os aspectos do plano de execução aqui, mas em um nível básico posso explicar as coisas que precisamos considerar.

Primeiro, precisamos descobrir quais operadores recebem a maior parte do custo da consulta.
Se o operador está tendo muito custo, precisamos saber o motivo. Na maioria das vezes, as varreduras custarão mais do que as buscas. Precisamos examinar por que uma varredura específica (varredura de tabela ou varredura de índice) está acontecendo em vez de uma busca de índice. Podemos resolver esse problema implementando índices adequados nas colunas da tabela, mas como em qualquer programa complexo, não há solução fixa. Por exemplo, se a tabela for pequena, as varreduras serão mais rápidas do que as buscas.
São aproximadamente 78 operadores, que representam as diversas ações e decisões do plano de execução do SQL Server. Precisamos estudá-los a fundo consultando a documentação da Microsoft, para que possamos entendê-los melhor e tomar as devidas providências.

Relacionado: Índices SQL explicados, Pt. 1

#4 Reutilização do Plano de Execução

Mesmo se implementarmos índices adequados nas tabelas e escrevermos um bom código T-SQL, se o plano de execução não for reutilizado, teremos problemas de desempenho. Depois de ajustar as consultas, precisamos ter certeza de que o plano de execução pode ser reutilizado quando necessário. A maior parte do tempo de CPU será gasto no cálculo do plano de execução que pode ser eliminado, se reutilizarmos o plano.

Podemos usar a consulta abaixo para descobrir quantas vezes o plano de execução é reutilizado, onde usecounts representa quantas vezes o plano é reutilizado:

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

A melhor maneira de reutilizar o plano de execução é implementando procedimentos armazenados parametrizados. Quando não estamos em condições de implementar procedimentos armazenados, podemos usar sp_executesql , que pode ser usado para executar instruções T-SQL quando a única alteração nas instruções SQL são valores de parâmetro. O SQL Server provavelmente reutilizará o plano de execução gerado na primeira execução.

Novamente, como acontece com qualquer programa de computador complexo, não há solução fixa. Às vezes é melhor compilar o plano novamente.

Vamos examinar a seguir duas consultas de exemplo:

select name from table where name = 'sri';
select name from table where name = 'pal';

Vamos supor que temos um índice não clusterizado na coluna de name e metade da tabela tem o valor sri e poucas linhas têm pal na coluna de name . Para a primeira consulta, o SQL Server usará a verificação de tabela porque metade da tabela tem os mesmos valores. Mas para a segunda consulta, é melhor usar a varredura de índice porque apenas algumas linhas têm valor pal .

Mesmo que as consultas sejam semelhantes, o mesmo plano de execução pode não ser uma boa solução. Na maioria das vezes será um caso diferente, por isso precisamos analisar tudo cuidadosamente antes de decidir. Se não quisermos reutilizar o plano de execução, sempre podemos usar a opção “recompilar” em procedimentos armazenados.

Lembre-se de que, mesmo depois de usar procedimentos armazenados ou sp_executesql , há momentos em que o plano de execução não será reutilizado. Eles estão:

Quando os índices usados pela consulta mudam ou são descartados
Quando as estatísticas, estrutura ou esquema de uma tabela usada pela consulta mudam
Quando usamos a opção “recompilar”
Quando há um grande número de inserções, atualizações ou exclusões
Quando misturamos DDL e DML em uma única consulta

#5 Removendo índices desnecessários

Depois de ajustar as consultas, precisamos verificar como os índices são usados. A manutenção do índice requer muita CPU e E/S. Toda vez que inserimos dados em um banco de dados, o SQL Server também precisa atualizar os índices, portanto, é melhor removê-los caso não sejam utilizados.

O servidor SQL nos fornece dm_db_index_usage_stats DMV para encontrar estatísticas de índice. Quando executamos o código T-SQL abaixo, obtemos estatísticas de uso para diferentes índices. Se encontrarmos índices que não são usados, ou usados raramente, podemos eliminá-los para ganhar desempenho.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 Instalação do SQL Server e configuração do banco de dados

Ao configurar um banco de dados, precisamos manter os dados e os arquivos de log separadamente. A principal razão para isso é que escrever e acessar arquivos de dados não é sequencial, enquanto escrever e acessar arquivos de log é sequencial. Se os colocarmos no mesmo drive, não poderemos usá-los de forma otimizada.

Quando compramos uma rede de área de armazenamento (SAN), um fornecedor pode nos dar algumas recomendações sobre como configurá-la, mas essas informações nem sempre são úteis. Precisamos ter uma discussão detalhada com nosso pessoal de hardware e rede sobre como manter dados e arquivos de log separadamente e de maneira otimizada.

#7 Não sobrecarregue o SQL Server

A principal tarefa de qualquer administrador de banco de dados é garantir que o servidor de produção funcione sem problemas e atenda os clientes da melhor maneira possível. Para que isso aconteça, precisamos manter bancos de dados separados (se possível, em máquinas separadas) para os seguintes ambientes:

Produção
Desenvolvimento
Teste
Analítico

Para um banco de dados de produção, precisamos de um banco de dados com modo de recuperação completo, e para outros bancos de dados, um modo de recuperação simples é suficiente.

Testar em um banco de dados de produção colocará muita carga no log de transações, índices, CPU e E/S. É por isso que precisamos usar bancos de dados separados para produção, desenvolvimento, teste e análise. Se possível, use máquinas separadas para cada banco de dados, pois isso diminuirá a carga na CPU e E/S.

#8 Log de transações, tempdb e memória

O arquivo de log precisa ter espaço livre suficiente para operações normais porque uma operação de crescimento automático em um arquivo de log é demorada e pode forçar outras operações a esperar até que seja concluída. Para descobrir o tamanho do arquivo de log para cada banco de dados e quanto ele é usado, podemos usar DBCC SQLPERF(logspace) .

A melhor maneira de configurar o tempdb é colocá-lo em um disco separado. Precisamos manter o tamanho inicial tão grande quanto pudermos, porque quando atingir uma situação de crescimento automático, o desempenho diminuirá.

Como mencionado anteriormente, precisamos garantir que o SQL Server seja executado em uma máquina separada, de preferência uma sem nenhum outro aplicativo. Precisamos manter alguma memória para o sistema operacional, além de um pouco mais se for parte de um cluster, portanto, na maioria dos casos, cerca de 2 GB deve ser suficiente.

Para ambientes de missão crítica, um atraso de milissegundos na obtenção de informações pode ser um fator decisivo.

Conclusão:

Os procedimentos e sugestões discutidos aqui são apenas para ajuste básico de desempenho. Se seguirmos essas etapas, podemos, em média, obter cerca de 40 a 50 por cento de melhoria no desempenho. Para fazer o ajuste de desempenho avançado do SQL Server, precisaríamos nos aprofundar muito em cada uma das etapas abordadas aqui.

Leitura adicional no Blog da Toptal Engineering:

Resolvendo gargalos com partições e índices SQL
Guia de Migração de Oracle para SQL Server e SQL Server para Oracle