top of page

Gargalos Silenciosos no SQL Server

13 de nov.
3 min de leitura

Sabe quando uma procedure crítica no SQL Server roda em milissegundos e, do nada, começa a dar timeout e travar toda a aplicação? Ou quando o sistema engasga no horário de pico e todo mundo culpa o disco?


Compartilho abaixo como lidar com dois dos cenários mais chatos do dia a dia de operações de banco de dados.


1. O Clássico Parameter Sniffing

A proc compila um plano de execução baseado em um parâmetro atípico e a performance desmorona. Na hora do desespero, muita gente sai reiniciando o serviço ou recriando índices, o que só mascara o problema ou piora a situação.


A solução cirúrgica: isole o plan_handle da consulta problemática na sys.dm_exec_query_stats e rode um DBCC FREEPROCCACHE (plan_handle). Atenção à ressalva: Jamais rode o DBCC FREEPROCCACHE sem passar o handle específico, ou você limpará o cache de toda a instância, forçando o banco a recompilar todos os planos e gerando um pico massivo de CPU.


Se o padrão de distorção de dados for recorrente, você pode avaliar o uso de variáveis locais dentro da proc ou o OPTION (RECOMPILE). Mas cuidado: o RECOMPILE força a criação de um novo plano a cada execução. Se for uma query de altíssima concorrência (rodando dezenas de vezes por segundo), o custo de CPU para ficar gerando novos planos pode se tornar um gargalo ainda pior que o próprio Parameter Sniffing.


Para facilitar o dia a dia e evitar acidentes, criei uma procedure (sp_LimpaCache) que localiza o plan_handle pelo texto da query, limpa apenas esse plano específico e ainda guarda um log de auditoria de quem fez a limpeza e quando. O código está no final do post.


2. Contenção no TempDB (Concorrência Avançada)

Outro cenário que derruba muito ambiente de alta concorrência é a contenção de alocação em memória no TempDB. Você abre o monitoramento e vê waits de PAGELATCH_UP ou PAGELATCH_EX batendo direto nas páginas PFS, GAM e SGAM.


O sistema engasga pela criação e destruição simultânea de tabelas temporárias em horários de pico, e a equipe de infraestrutura acha que é gargalo de disco.


Se você pegar esse padrão, a solução vai além de apenas dividir o TempDB em múltiplos arquivos de dados. Avalie habilitar o Memory-Optimized TempDB Metadata (disponível a partir do SQL Server 2019). Essa feature tira a disputa das tabelas de sistema da frente e elimina o gargalo na raiz. É um teste obrigatório para ambientes transacionais pesados.


Bônus: Código da sp_LimpaCache

Aqui está o script do arquivo "SP_LimpaCache -- Permite limpar os planos de execução de queries específicas - Inclui Tabela e Procedure.sql" para automatizar a limpeza pontual de planos com segurança:


-- SP_Limpacache -- Permite limpar os planos de execução de queries específicas - Inclui Tabela e Procedure


-- Uso no banco DBManager que precisa já estar criado


-- Criação da tabela

USE [DBManager]

GO


SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


CREATE TABLE [dbo].[tb_limpa_cache](

[id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[plan_handle] [varbinary](64) NULL,

[sql_text] [nvarchar](max) NULL,

[username] [nvarchar](128) NULL,

[host_name] [nvarchar](128) NULL,

[date_time] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



-- Criação da procedure


SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


CREATE procedure [dbo].[sp_LimpaCache] @sql_text varchar(8000) null = default, @plan_handle varbinary(64) null = default

as

declare @vsql_text varchar(4000);


if @sql_text is null and @plan_handle is null

begin

print '-----------------------------------------------------------'

print '- Limpar SQL do Cache '

print '-----------------------------------------------------------'

print 'Instruções:'

print ''

print '---------------------------------------------------------------------------------'

print ' Primeiro'

print ' Entrada: Informar parte do texto do SQL que se deseja descobrir o plan_handle'

print ' Saida : Um ou vários plan_handles serão retornados, se retornar mais de um, '

print ' verificar pelo texto qual é o plan_handle da sua consulta '

print '---------------------------------------------------------------------------------'

print 'Exemplo:'

print 'exec DBManager..sp_LimpaCache @sql_text = ''parte_do_texto_da_query'''

print ''

print '-----------------------------------------------------------------'

print 'Segundo'

print ' Entrada: Informar o plan_handle do SQL que deverá ser apagado'

print ' Saida : O SQL será apagado do cache'

print '-----------------------------------------------------------------'

print 'Exemplo:'

print 'exec DBManager..sp_LimpaCache @plan_handle = 0x04001200EEEEE40B1088A325D101000001000000000000000000000000000000000000000000000000000000'


end


if len(@sql_text) > 8000

begin

print 'Texto do SQL tem que ser inferior a 8000'

return 0

end


if @plan_handle is not null

begin

select @vsql_text = text

from sys.dm_exec_sql_text(@plan_handle);


dbcc freeproccache(@plan_handle);


insert into tb_limpa_cache values (@plan_handle, @vsql_text, SUSER_SNAME(), HOST_NAME(), GETDATE());

end

else if @sql_text is not null

SELECT plan_handle, st.text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE '%' + @sql_text + '%';

GO


Limpar um plano específico com a sp_LimpaCache ou ativar o Memory-Optimized TempDB resolve a emergência, mas se o seu ambiente sofre com lentidões recorrentes em horários de pico, alto consumo de CPU ou dúvidas sobre arquitetura e licenciamento, a causa raiz pode estar na configuração da instância ou no desenho das consultas.


Agende um Diagnóstico Técnico de 30 minutos (via Meet) direto no seu cenário com um de nossos especialistas da CloudDB.


Acesse o link para escolher o melhor dia e horário na nossa agenda:      https://linktr.ee/CloudDB 


 
 
 

Comentários


Logo_quadrada.png

CLOUD DB LTDA - 28.961.479/0001-41

© 2020 Direitos Reservados CloudDB ®  O plágio é considerado crime e sua principal referência é a lei 9.610 - ♥ SEO WIX Partner - Phoenix

bottom of page