Início / dba / Perguntas / 174179
Accepted
George K
Asked: 2017-05-22 22:34:25 +0800 CST

Ajude-me a ajustar a consulta de longa duração

  • 772

Estou com um problema com a seguinte consulta em execução no aplicativo (Microsoft Dynamics AX):

DECLARE @p1 INT;
SET @p1 = NULL;
DECLARE @p2 INT;
SET @p2 = 0;
DECLARE @p5 INT;
SET @p5 = 2 + 4096;
DECLARE @p6 INT;
SET @p6 = 8193;
DECLARE @p7 INT;
SET @p7 = 0;
EXEC sp_cursorprepexec
     @p1 OUTPUT,
     @p2 OUTPUT,
     N'@P1 nvarchar(5),@P2 int,@P3 nvarchar(5),@P4 int,@P5 nvarchar(5),@P6 nvarchar(5),@P7 datetime,@P8 nvarchar(21),@P9 numeric(28, 12)',
     N'SELECT A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ FROM INVENTTRANS A,INVENTTRANS B,INVENTTABLE C WHERE ((A.DATAAREAID=@P1) AND (A.TRANSTYPE=@P2)) AND ((B.DATAAREAID=@P3) AND (((B.INVENTTRANSID=A.INVENTTRANSIDFATHER) AND (B.TRANSREFID=A.TRANSREFID)) AND (B.TRANSTYPE=@P4))) AND ((C.DATAAREAID=@P5) AND (C.ITEMID=B.ITEMID)) 
AND EXISTS (SELECT ''x'' FROM INVENTSETTLEMENT D WHERE ((D.DATAAREAID=@P6) AND ((((D.TRANSDATE=@P7) AND (D.VOUCHER=@P8)) AND (D.TRANSRECID=A.RECID)) AND (D.COSTAMOUNTADJUSTMENT<>@P9))))
 GROUP BY A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ ORDER BY B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_',
     @p5 OUTPUT,
     @p6 OUTPUT,
     @p7 OUTPUT,
     N'dat',
     8,
     N'dat',
     2,
     N'dat',
     N'dat',
     '2017-05-17 00:00:00.000',
     N'IM17008141934',
     '0.000000000000';
EXEC sp_cursorfetch
     @p2,
     2,
     1,
     1;

O plano estimado para esta consulta está aquiPlano estimado

Versão XML aqui

A consulta continua em execução por quase 3 horas. Também capturei métricas do WhoIsActive sp para esta sessão de consultas:

wait_info                       (2ms)PAGEIOLATCH_SH:ecc_wrk:4(*)     
CPU                             88,765
CPU_delta                       47
tempdb_allocations              0
tempdb_current                  0
tempdb_allocations_delta        0   
tempdb_current_delta            0
blocking_session_id             NULL
blocked_session_count           16
reads                           26,610,593
reads_delta                     20,903
writes                          0
writes_delta                    0
physical_reads                  1,212,764
physical_reads_delta            418 
used_memory used_memory_delta   276 
status                          0
open_tran_count                 1
host_name                       ***
database_name                   ***
program_name                    Microsoft Dynamics AX

Como posso ver, ele lê uma enorme quantidade de dados e é executado por uma enorme quantidade de tempo. Também capturei a consulta e a executei no estúdio de gerenciamento, onde levou 1 segundo para ser concluído. O plano de execução real está aqui:insira a descrição da imagem aqui

Versão XML aqui

Não consigo entender o que o problema pode me e onde cavar. Qualquer ajuda sobre o que fazer a seguir será profundamente apreciada.

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Em primeiro lugar, o código que você postou não é o código executado pelo aplicativo. Não há Fetch Queryoperador, então você não está recuperando nenhuma linha.

    Como você observou no chat, você mesmo construiu o código, mas é difícil adivinhar qual será exatamente o código executado pelo AX, isso depende de algumas configurações e cálculos feitos pelo kernel do AX.

    Em primeiro lugar, é provável que seu cursor seja FAST_FORWARDe o número de linhas buscadas pode ser tudo de uma vez ou vários registros por vez.

    O número de linhas buscadas de uma vez é definido pelo parâmetro Tamanho Máximo do Buffer/tamanho da linha. Consulte Configuração do Servidor do Microsoft Dynamics AX 2012

    A definição do cursor será algo assim

    declare @p1 int
set @p1=189527589
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=2
exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS<>0) AND (A.JOBTYPE<>3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

    ou isto

    declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
--
-- Fast Forward(16)+Parameterized(4096)+AutoFetch(8192)+AutoClose(16384)
--
set @p5=16+4096+8192
declare @p6 int
set @p6=8193
declare @p7 int
--
-- Number of Rows for AutoFetch. 
-- This is calculated by Maximum Buffer Size (24K default) / Row Length
--
set @p7=4
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(5),@P2 nvarchar(21)',N'SELECT A.SALESID,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND (SALESID>@P2))',@p5 output,@p6 output,@p7 output,N'ceu',N'SO-100004'
-- @p2 contains cursor handle for fetch call
exec sp_cursorfetch @p2,2,1,@p7

    Dito isto, na maioria das vezes, quando recebo uma consulta de baixo desempenho no AX, a consulta se comporta da mesma forma com ou sem cursor, e é raro precisar do código real, incluindo os cursores, para poder reproduzir o problema no Management Studio , no entanto, se você realmente precisar da definição do cursor, o mais fácil é reproduzi-la em um ambiente de teste.

    Depois de reproduzir o problema, você precisa localizar a consulta incorreta no código.
    Se você puder reproduzir o problema em um ambiente de teste (porque o AX é bastante propenso à detecção de parâmetros), poderá usar o log de rastreamento de instrução SQL interno para localizar a pilha de chamadas para a consulta.

    Você também pode examinar a ferramenta Trace Parser para capturar rastreamentos com os valores de parâmetro reais e o rastreamento de código até a consulta. Isso pode ajudar muito também.

    Depois de saber onde a consulta é executada, você pode colocar um ponto de interrupção logo antes da consulta e, quando acessá-lo, acionar o SQL Server Profiler para capturar o código exato que está sendo executado enquanto você percorre a seleção no depurador do AX.

    Uma vez que você tenha o código problemático (se você realmente precisar dele incluindo cursores), os princípios de ajuste regulares são válidos.

    Suas primeiras tentativas devem ser a indexação porque suas opções para alterar a consulta são um pouco limitadas no AX. Então, novamente, o plano estimado que você postou tem apenas um custo estimado de 0,04 e usa buscas de índice, não tenho certeza se esse é o seu problema.

    O AX é muito propenso a sniffing de parâmetros por causa de todos os select *resultados em muitas pesquisas de chave.

    Você pode tentar usar algumas palavras-chave para influenciar o plano, ou seja, forceliteralsdesabilitar a parametrização e forçar um novo plano a ser compilado para cada execução ou você pode definir algumas metas de linha usando firstfaste é isso.

    Consulte a documentação de sintaxe da instrução Select para ver quais opções você tem, mas não exagere nas palavras-chave (como sugeri em minha resposta à sua outra pergunta ). Meu primeiro palpite é que você está se deparando com sniffing de parâmetros.

    • 10

  2. Eu consegui resolver o problema. Eu executei um rastreamento e capturei o que estava acontecendo no servidor. Tom V, muito obrigado. Na verdade, era uma forma de sniffing de parâmetros . O AX executou primeiro uma consulta como esta:

    declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=28688
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=9
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(5),@P2 int,@P3 nvarchar(5),@P4 int,@P5 nvarchar(5),@P6 nvarchar(5),@P7 datetime,@P8 nvarchar(21),@P9 numeric(28, 12)',N'SELECT A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ FROM INVENTTRANS A,INVENTTRANS B,INVENTTABLE C WHERE ((A.DATAAREAID=@P1) AND (A.TRANSTYPE=@P2)) AND ((B.DATAAREAID=@P3) AND (((B.INVENTTRANSID=A.INVENTTRANSIDFATHER) AND (B.TRANSREFID=A.TRANSREFID)) AND (B.TRANSTYPE=@P4))) AND ((C.DATAAREAID=@P5) AND (C.ITEMID=B.ITEMID)) AND EXISTS (SELECT ''x'' FROM INVENTSETTLEMENT D WHERE ((D.DATAAREAID=@P6) AND ((((D.TRANSDATE=@P7) AND (D.VOUCHER=@P8)) AND (D.TRANSRECID=A.RECID)) AND (D.COSTAMOUNTADJUSTMENT<>@P9)))) GROUP BY A.INVENTTRANSIDFATHER,B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_ ORDER BY B.INVENTTRANSID,B.TRANSREFID,C.MODELGROUPID,C.COSTGROUPID,C.DIMENSION,C.DIMENSION2_,C.DIMENSION3_',@p5 output,@p6 output,@p7 output,N'dat',8,N'dat',2,N'dat',N'dat','2017-06-05 00:00:00',N'IM17008151695',0.000000000000
select @p1, @p2, @p5, @p6, @p7

    E então (evento inicial de RPC):

    declare @p2 int
set @p2=0
declare @p3 int
set @p3=24592
declare @p4 int
set @p4=8193
declare @p5 int
set @p5=9
exec sp_cursorexecute 1073741872,@p2 output,@p3 output,@p4 output,@p5 output,N'dat',9,N'dat',10,N'dat',N'dat','2017-06-05 00:00:00',N'IM17008151695',0.000000000000
select @p2, @p3, @p4, @p5

    Então o Profilerjogou Sort Warninge depois de 2 horas finalmente terminou. Como você pode ver na primeira declaração, o valor do parâmetro para o TransTypecampo era "8", no segundo caso - "9". Resolvemos o problema usando forceliterals como você sugeriu.

    • 2

relate perguntas