Minha aplicação usa um banco SQL Server Standard que tem uma carga de uso muito grande.…
- User
Minha aplicação usa um banco SQL Server Standard que tem uma carga de uso muito grande. Frequentemente o servidor testa planos de execução diferentes para algumas queries, porém algumas vezes ele passa a usar um plano muito pior que o que estava sendo usado antes. Quando isso acontece, temos que ir manualmente no Query Store > Regressed queries e forçar um plano de execução mais performático nas piores queries. Quero parar com a necessidade desse processo manual
Assistant
Automatizando a Correção de Planos de Execução no SQL Server
Você está enfrentando um problema comum em bancos de dados SQL Server com alta carga, onde o otimizador de consultas ocasionalmente escolhe planos de execução piores do que os anteriores. Aqui estão algumas soluções para automatizar esse processo:
1. Configurar o Query Store para Ações Automáticas
-- Habilitar Query Store se ainda não estiver ativo ALTER DATABASE [SeuBanco] SET QUERY_STORE = ON; ALTER DATABASE [SeuBanco] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);2. Criar um Procedimento Armazenado para Automatizar a Correção
CREATE PROCEDURE dbo.AutoFixRegressedQueries @ForcePlanThreshold INT = 5, -- Limite de regressão em segundos @MaxQueriesToFix INT = 10 -- Número máximo de consultas para corrigir por execução AS BEGIN SET NOCOUNT ON; DECLARE @QueryID INT; DECLARE @PlanID BIGINT; DECLARE @CurrentAvgDuration FLOAT; DECLARE @PreviousAvgDuration FLOAT; DECLARE @QueryText NVARCHAR(MAX); DECLARE @PlanXML XML; DECLARE @Count INT = 0; -- Criar tabela temporária para armazenar consultas regressadas CREATE TABLE #RegressedQueries ( QueryID INT, PlanID BIGINT, CurrentAvgDuration FLOAT, PreviousAvgDuration FLOAT, QueryText NVARCHAR(MAX), PlanXML XML ); -- Identificar consultas regressadas INSERT INTO #RegressedQueries SELECT qsrs.query_id, qsrs.plan_id, qsrs.avg_duration, qsrs.last_good_plan_avg_duration, qt.query_sql_text, qp.query_plan FROM sys.query_store_runtime_stats qsrs JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id JOIN sys.query_store_query qsq ON qsrs.query_id = qsq.query_id JOIN sys.query_store_query_text qt ON qsq.query_text_id = qt.query_text_id JOIN sys.query_store_plan qp ON qsrs.last_good_plan_id = qp.plan_id WHERE qsrs.avg_duration > qsrs.last_good_plan_avg_duration * @ForcePlanThreshold AND qsrs.last_good_plan_id IS NOT NULL AND qsrs.execution_type_desc = 'Regular' ORDER BY (qsrs.avg_duration - qsrs.last_good_plan_avg_duration) DESC; -- Processar cada consulta regressada DECLARE QueryCursor CURSOR FOR SELECT QueryID, PlanID, CurrentAvgDuration, PreviousAvgDuration, QueryText, PlanXML FROM #RegressedQueries ORDER BY (CurrentAvgDuration - PreviousAvgDuration) DESC; OPEN QueryCursor; FETCH NEXT FROM QueryCursor INTO @QueryID, @PlanID, @CurrentAvgDuration, @PreviousAvgDuration, @QueryText, @PlanXML; WHILE @@FETCH_STATUS = 0 AND @Count < @MaxQueriesToFix BEGIN BEGIN TRY -- Forçar o plano de execução EXEC sp_query_store_force_plan @QueryID, @PlanID; -- Logar a ação (você pode criar uma tabela de log para isso) PRINT CONCAT('Forçado plano para consulta ID: ', @QueryID, ' | Melhoria esperada: ', CAST((@CurrentAvgDuration - @PreviousAvgDuration) AS VARCHAR(20)), 'ms | Consulta: ', LEFT(@QueryText, 100)); SET @Count = @Count + 1; END TRY BEGIN CATCH PRINT CONCAT('Erro ao forçar plano para consulta ID: ', @QueryID, ' | Erro: ', ERROR_MESSAGE()); END CATCH FETCH NEXT FROM QueryCursor INTO @QueryID, @PlanID, @CurrentAvgDuration, @PreviousAvgDuration, @QueryText, @PlanXML; END CLOSE QueryCursor; DEALLOCATE QueryCursor; DROP TABLE #RegressedQueries; END3. Agendar a Execução Automática
Você pode agendar a execução desse procedimento usando o SQL Server Agent:
-- Criar um job no SQL Server Agent USE msdb; GO EXEC dbo.sp_add_job @job_name = N'AutoFixRegressedQueries', @enabled = 1, @description = N'Procura e corrige automaticamente consultas regressadas no Query Store'; EXEC sp_add_jobstep @job_name = N'AutoFixRegressedQueries', @step_name = N'Executar procedimento de correção', @subsystem = N'TSQL', @command = N'EXEC dbo.AutoFixRegressedQueries;', @database_name = N'SeuBanco'; -- Agendar para executar a cada 4 horas EXEC dbo.sp_add_schedule @schedule_name = N'ExecucaoAutoFixRegressedQueries', @freq_type = 4, -- Diário @freq_interval = 1, @freq_subday_type = 4, -- Horas @freq_subday_interval = 4, -- A cada 4 horas @active_start_time = 000000; -- Meia-noite EXEC sp_attach_schedule @job_name = N'AutoFixRegressedQueries', @schedule_name = N'ExecucaoAutoFixRegressedQueries';4. Configurações Adicionais Recomendadas
-- Configurar o Query Store para reter mais dados ALTER DATABASE [SeuBanco] SET QUERY_STORE ( MAX_STORAGE_SIZE_MB = 1024, FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, SIZE_BASED_CLEANUP_MODE = AUTO, QUERY_CAPTURE_MODE = AUTO, MAX_PLANS_PER_QUERY = 200 );5. Monitoramento Contínuo
Considere implementar um sistema de monitoramento que:
- Registre todas as ações automáticas em uma tabela de log
- Envie alertas quando consultas críticas forem corrigidas
- Monitore a eficácia das correções ao longo do tempo
Esta abordagem automatizada reduzirá significativamente a necessidade de intervenção manual, permitindo que sua equipe se concentre em outras tarefas enquanto o sistema cuida automaticamente das consultas regressadas.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.