Minha consulta tem um desempenho ruim quando a executo sem arquivos OPTION (RECOMPILE). Sem recompilar, leva de 3 a 4 minutos para ser executado e, com recompilação, leva cerca de 15 a 20 segundos.
Não consigo alterar a consulta, atualizei todas as estatísticas e reconstruí todos os índices. Há apenas 1 sugestão de índice com uma melhoria de 9,4%.
Eu tentei:
-- clear all plans in cache
DBCC FREEPROCCACHE
-- Clear Buffer pool
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
O que mais posso fazer? Não consigo editar a consulta, portanto OPTION (RECOMPILE)não é uma solução válida para mim. A consulta pode variar um pouco de tempos em tempos, então o "guia de plano" não funcionará?
Observe que esta não é a consulta real. A consulta real é executada pelo Dynamics AX, então está usando um arquivo api_cursor. Eu tirei isso do sp_cursorpreparee preenchi os parâmetros manualmente.
DECLARE @p1 AS BIGINT = 5637144576
DECLARE @p2 AS NVARCHAR(32) = N'1003'
DECLARE @p3 AS NVARCHAR(32) = N'posp%'
DECLARE @p4 AS BIGINT = 5637144576
DECLARE @p5 AS NVARCHAR(32) = N'sv'
DECLARE @p6 AS NVARCHAR(32) = N'posp'
DECLARE @p7 AS BIGINT = 5637144576
DECLARE @p8 AS NVARCHAR(32) = N'1003'
DECLARE @p9 AS NVARCHAR(32) = N'posp'
DECLARE @p10 AS BIGINT = 5637144576
DECLARE @p11 AS NVARCHAR(32) = N'posp'
DECLARE @p12 AS BIGINT = 5637144576
DECLARE @p13 AS NVARCHAR(32) = N'1003'
DECLARE @p14 AS BIGINT = 5637144576
DECLARE @p15 AS NVARCHAR(32) = N''
DECLARE @p16 AS NVARCHAR(32) = N'sv'
DECLARE @p17 AS INT = 0
DECLARE @p18 AS INT = 0
DECLARE @p19 AS INT = 0
DECLARE @p20 AS INT = 0
DECLARE @p21 AS BIGINT = 5637144576
DECLARE @p22 AS NVARCHAR(32) = N'1003'
DECLARE @p23 AS INT = 2
DECLARE @p24 AS BIGINT = 5637144576
DECLARE @p25 AS NVARCHAR(32) = N'1003'
DECLARE @p26 AS BIGINT = 5637144576
DECLARE @p27 AS INT = 1
DECLARE @p28 AS INT = 101
select @p1, @p2, @p5, @p6, @p7
SELECT *
FROM (
SELECT Row_number() OVER(ORDER BY t2.NAME,t2.itemid) AS rownumber,
t2.itemid AS f1,
t2.product AS f2,
t2.NAME AS f3,
t2.recid AS f4,
t3.unitid AS f5,
t3.recid AS f6,
t4.recid AS f7,
t5.recid AS f8,
t6.recid AS f9,
t7.recid AS f10,
t7.instancerelationtype AS f11
FROM retailitemname T2
CROSS JOIN inventtablemodule T3
LEFT OUTER JOIN inventtable T4
ON (((
t4.partition=@P1)
AND (
t4.dataareaid=@P2))
AND ((
t4.itemid LIKE @P3 ESCAPE '\' )
AND (
t2.itemid=t4.itemid)))
LEFT OUTER JOIN ecoresproducttranslation t5
ON ((
t5.partition=@P4)
AND (((
t5.languageid=@P5)
AND (
FREETEXT(t5.NAME,@P6)))
AND (
t2.product=t5.product)))
LEFT OUTER JOIN inventtable t6
ON (((
t6.partition=@P7)
AND (
t6.dataareaid=@P8))
AND ((
FREETEXT(t6.namealias,@P9))
AND (
t2.itemid=t6.itemid)))
LEFT OUTER JOIN ecoresproduct t7
ON ((
t7.partition=@P10)
AND ((
FREETEXT(t7.searchname,@P11))
AND (
t2.product=t7.recid)))
WHERE ((((
t2.partition=@P12)
AND (
t2.dataareaid=@P13))
AND ((
t2.partition#2=@P14)
OR (
t2.partition#2 IS NULL)))
AND (((
t2.languageid=@P15)
OR (
t2.languageid=@P16))
AND ((((
t4.recid<>@P17)
OR (
t5.recid<>@P18))
OR (
t6.recid<>@P19))
OR (
t7.recid<>@P20))))
AND (((
t3.partition=@P21)
AND (
t3.dataareaid=@P22))
AND ((
t3.moduletype=@P23)
AND (
t2.itemid=t3.itemid)))
AND EXISTS
(
SELECT 'x'
FROM retailitemcategory t8
WHERE ((((
t8.partition=@P24)
AND (
t8.dataareaid=@P25))
AND (
t8.partition#2=@P26))
AND (
t2.itemid=t8.itemid))) )t1
WHERE ((
t1.rownumber>=@P27)
AND (
t1.rownumber<@P28))
OPTION (RECOMPILE)
Planos
- Plano de Execução Lenta (3-4min)
- Plano de Execução Rápida (15-20 segundos)
MAXDOP 0(1 minuto)
Novamente: observe que esses são os planos da consulta que criei, não necessariamente os usados quando o aplicativo está realmente usando um cursor.
Versão SQL:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 20 de fevereiro de 2014 20:04:26 Direitos autorais (c) Microsoft Corporation Developer Edition (64 bits) no Windows NT 6.1 (Build 7601: Service Pack 1) (hipervisor)
MAXDOPestá definido como 1 porque é um banco de dados AX 2012 (R3 CU10). Não queremos que grandes relatórios ou trabalhos bloqueiem todos os processadores. Não sabia que você poderia ver mais sugestões de índice no plano. Mas o lento tem apenas uma sugestão e o rápido tem 2. MAXDOP 0é mais rápido, mas ainda não tão rápido quanto recompilar.
A consulta do AX é executada quando você, através do serviço em tempo real, procura um produto no AX que não existe no banco de dados do canal. Esta pesquisa está incluída no varejo AX 2012 R3. Sem personalização.
A intenção é otimizar o processo no SQL Server. A consulta faz exatamente o que deveria fazer, mas é muito lenta sem a recompilação. Ele está usando o plano de consulta errado. Se usar o plano de consulta correto, é rápido. Portanto, não deve haver necessidade de fazer alterações no AX.
optimize for ad hoc workloadsestá ativado.
Não tenho certeza de como o componente de varejo funciona, mas como você diz que o capturou do cursorprepare e o reescreveu, parece insinuar que foi enviado do AOS de qualquer maneira.
Uma coisa que eu sugiro que você faça é ver se isso ajuda a executar
e reinicie seus servidores de camada intermediária.
Normalmente em um ambiente AX (como você explicou nos comentários) existem grandes discrepâncias na distribuição de dados entre as empresas, e reaproveitar o plano de uma empresa que contém 0 registros no varejo para uma consulta executada em uma empresa com dezenas de milhares de registros é vai ter sérios problemas de detecção de parâmetros.
Por favor, dê uma olhada no Parâmetro Sniffing do SQL Server com Dynamics AX, simplesmente maligno para mais detalhes.
Observe que não tenho certeza se essa configuração afetaria o componente de varejo, mas espero que sim, pois você afirmou nos comentários que a consulta é realmente executada pelo AOS, mas a configuração pode ser útil para a instalação do Dynamics como um todo.
Você afirma que não deve haver nenhum motivo para alterar nada no AX, mas pode muito bem haver, pois você também afirma que a consulta funciona bem quando recompilada.
Pontos relevantes do artigo vinculado:
Se isso não ajudar, você deve procurar no código AX de onde vem a consulta. Leia as instruções em Como monitorar consultas de execução longa no AX para obter instruções sobre como localizar a pilha de chamadas de origem da consulta. Depois de saber onde a consulta está no código, você pode (ou solicitar aos desenvolvedores) adicionar a palavra-chave forceliterals (novamente documentação ) para desativar os parâmetros e preencher os valores reais para compilar o plano de execução para cada combinação de parâmetros. Isso deve se parecer
OPTION (RECOMPILE)bastante, exceto que não será recompilado para os mesmos valores exatos.Com relação à sua
MAXDOPobservação, isso não faria muita diferença, pois o AX cria umFAST_FORWARDcursor que não suporta paralelismo , como Paul White explicou nesta resposta a uma pergunta minha .