主页 / dba / 问题 / 174179
Accepted
George K
Asked: 2017-05-22 22:34:25 +0800 CST

帮助我调整长时间运行的查询

  • 772

我在从应用程序 (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;

此查询的估计计划在这里预计计划

此处为 XML 版本

查询持续运行了将近 3 个小时。我还为此查询会话从 WhoIsActive sp 捕获了指标：

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

如我所见，它读取大量数据并执行大量时间。我还捕获了查询并在 management studio 中执行了它，只用了 1 秒就完成了。实际的执行计划在这里：在此处输入图像描述

此处为 XML 版本

我不明白我可能会遇到什么问题以及在哪里挖掘。对于下一步该做什么的任何帮助，我们将不胜感激。

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    首先，您发布的代码不是应用程序执行的代码。没有Fetch Query运算符，因此您不会检索任何行。

    正如您在聊天中提到的，您自己构建了代码，但很难猜测 AX 执行的代码到底是什么，这取决于 AX 内核所做的一些设置和计算。

    首先，您的游标很可能是FAST_FORWARD，并且获取的行数可以是一次所有内容，也可以是一次多条记录。

    一次提取的行数由最大缓冲区大小参数/行大小定义。请参阅Microsoft Dynamics AX 2012 服务器配置

    游标定义看起来像这样

    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

    或这个

    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

    也就是说，大多数时候当我在 AX 中遇到性能不佳的查询时，无论有无游标，查询的行为都相同，而且我很少需要包括游标在内的实际代码才能在 Management Studio 中重现问题，但是，如果您真的需要游标定义，那么最简单的方法就是您是否可以在测试环境中重现它。

    一旦可以重现问题，就需要在代码中找到有问题的查询。
    如果您可以在测试环境中重现该问题（因为 AX 很容易进行参数嗅探），您可以使用内置的SQL 语句跟踪日志来查找查询的调用堆栈。

    您还可以查看Trace Parser 工具，以使用实际参数值和代码跟踪到查询来捕获跟踪。这也有很大帮助。

    一旦您知道查询的执行位置，您就可以在查询之前放置一个断点，当您点击它时，将启动SQL Server Profiler以捕获正在执行的确切代码，同时您在 AX 调试器中单步执行选择。

    一旦你有了有问题的代码（如果你真的需要它，包括游标），常规的调整原则是有效的。

    您的第一次尝试应该是建立索引，因为您更改查询的选项在 AX 中有点受限。然后，您发布的估计计划的估计成本仅为 0.04，并且使用索引搜索我不确定这是您的问题。

    AX 非常容易进行参数嗅探，因为所有这些都会select *导致大量的键查找。

    您可以尝试使用一些关键字来影响计划，即forceliterals禁用参数化并强制为每次执行编译一个新计划，或者您可以使用设置一些行目标firstfast，仅此而已。

    请参阅Select 语句语法文档以查看您有哪些选项，但不要过度使用关键字（正如我在回答您的其他问题时所建议的那样）。我的第一个猜测是您遇到了参数嗅探。

    • 10

  2. 我已经设法解决了这个问题。我已经运行了跟踪并捕获了服务器上发生的事情。汤姆 V，非常感谢。它实际上是某种形式的参数嗅探。AX 首先执行这样的查询：

    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

    然后（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

    然后Profiler扔了Sort Warning，2 小时后终于完成了。正如您在第一个语句中看到的，TransType字段的参数值为“8”，在第二种情况下为“9”。我们按照您的建议使用forceliterals解决了这个问题。

    • 2

相关问题