主页 / dba / 问题 / 136768
Accepted
Daniel Björk
Asked: 2016-04-28 10:36:15 +0800 CST

查询不会编译/运行

  • 772

我有一个不会在生产服务器上运行但在测试服务器上运行的查询。查询计划似乎无法编译或其编译的查询计划非常糟糕。我已经使用全扫描更新了所有统计信息并重建了所有相关表的索引但没有成功，在任何表中都没有那么多行。我无法更改查询，因为它是 AOS (AX 2012) 创建的。我应该怎么做才能按原样使用查询？

询问

DECLARE @P1 AS BIGINT = 5637144576
DECLARE @P2 AS NVARCHAR(4) = N'1003'
DECLARE @P3 AS INT = 212
DECLARE @P4 AS BIGINT = 5638885273
DECLARE @P5 AS BIGINT = 5637144576
DECLARE @P6 AS INT = 865
DECLARE @P7 AS BIGINT = 5637144576
DECLARE @P8 AS BIGINT = 5637144576

SELECT     t1.balance01, 
           t1.recid, 
           t2.amountcur, 
           t2.dataareaid, 
           t2.recid, 
           t3.recid, 
           t3.voucher, 
           t3.accountnum, 
           t3.approved, 
           t3.closed, 
           t3.dataareaid, 
           t4.party, 
           t4.dataareaid, 
           t4.recid 
FROM       spectrans T1 
CROSS JOIN custtransopen T2 
CROSS JOIN custtrans T3 
CROSS JOIN 
           ( 
                      SELECT     virt.id AS dataareaid , 
                                 t4.accountnum, 
                                 t4.party, 
                                 t4.partition, 
                                 t4.recid 
                      FROM       custtable T4 
                      INNER JOIN virtualdataarealist VIRT 
                      ON         t4.dataareaid = virt.virtualdataarea 
                      UNION ALL 
                      SELECT     t4.dataareaid , 
                                 t4.accountnum, 
                                 t4.party, 
                                 t4.partition, 
                                 t4.recid 
                      FROM       custtable T4 
                      INNER JOIN dataarea DAT 
                      ON         ( 
                                            t4.dataareaid = dat.id 
                                 AND        dat.isvirtual = 0)) T4 
WHERE      (( 
                                 t1.partition=@P1) 
           AND        ((( 
                                                       t1.speccompany=@P2) 
                                 AND        ( 
                                                       t1.spectableid=@P3)) 
                      AND        ( 
                                            t1.specrecid=@P4))) 
AND        (( 
                                 t2.partition=@P5) 
           AND        ((( 
                                                       t1.refcompany=t2.dataareaid) 
                                 AND        ( 
                                                       t1.reftableid=@P6)) 
                      AND        ( 
                                            t1.refrecid=t2.recid))) 
AND        (( 
                                 t3.partition=@P7) 
           AND        ( 
                                 t2.refrecid=t3.recid 
                      AND        ( 
                                            t2.dataareaid = t3.dataareaid) 
                      AND        ( 
                                            t2.partition = t3.partition))) 
AND        (( 
                                 t4.partition=@P8) 
           AND        ( 
                                 t3.accountnum=t4.accountnum 
                      AND        ( 
                                            t3.dataareaid = t4.dataareaid) 
                      AND        ( 
                                            t3.partition = t4.partition)))
  • 如果我使用来自测试服务器的执行计划，它将在不到一秒的时间内执行。
  • 我也试过OPTION (RECOMPILE)了DBCC FREEPROCCACHE
  • 没有阻塞

PROD 和 TEST 之间的差异表“sys.configurations”：

PROD    TEST    description
    30  0   Blocked process reporting threshold
    1   0   Enable or disable Database Mail XPs
    1   0   Sets the FILESTREAM access level
    1   2   maximum degree of parallelism
    230000  22528   Maximum size of server memory (MB)
    1   0   Dedicated Admin Connections are allowed from remote clients

环境细节

PROD：Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 版权所有 (c) Microsoft Corporation Enterprise Edition：Windows NT 6.3（Build 9600：）上基于核心的许可（64 位）

TraceFlag   Status  Global  Session
1117    1   1   0
1118    1   1   0
1224    1   1   0
2371    1   1   0
2505    1   1   0
3226    1   1   0
4199    1   1   0

测试：Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) o​​n Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

TraceFlag   Status  Global  Session
1117    1   1   0
1224    1   1   0
2371    1   1   0
2505    1   1   0
3226    1   1   0
4199    1   1   0

兼容级别：两者均为 120。

数据详情

产品

SELECT COUNT(*) FROM SPECTRANS -- 4601
SELECT COUNT(*) FROM CUSTTRANSOPEN -- 14162
SELECT COUNT(*) FROM CUSTTRANS -- 137127
SELECT COUNT(*) FROM CUSTTABLE -- 35617
SELECT COUNT(*) FROM VIRTUALDATAAREALIST -- 3
SELECT COUNT(*) FROM DATAAREA -- 5

预估执行计划： http: //pastebucket.com/326386
统计- http://pastebucket.com/326459

测试

 SELECT COUNT(*) FROM SPECTRANS -- 10753
 SELECT COUNT(*) FROM CUSTTRANSOPEN -- 7150
 SELECT COUNT(*) FROM CUSTTRANS -- 77342
 SELECT COUNT(*) FROM CUSTTABLE -- 36297
 SELECT COUNT(*) FROM VIRTUALDATAAREALIST -- 3
 SELECT COUNT(*) FROM DATAAREA -- 5

实际执行计划： http: //pastebucket.com/326387
统计- http://pastebucket.com/326458

sql-server performance

3 个回答

  1. Best Answer

    由于您运行的是 Microsoft SQL Server 2014 - 12.0.2000，这是第一个包含新基数估计器的 RTM 版本，我强烈建议您尝试更新到最新的 CU 之一。

    如msdn 上的这篇博文所述

    您需要应用 SP1，但您还必须启用跟踪标志 4199 才能激活修复程序。

    SQL Server 2014 Service Pack 1 对新的 Cardinality Estimator (new CE) 进行了各种修复。发行说明还记录了修复。

    您已经激活了 TF 4199，因此改进应该会自动激活。

    我强烈建议您更新并使用新的 Cardinality Estimator，但使用所有最新的修复程序，而不是禁用新的 CE alltogheter。

    如果您仍然对这个单一查询有问题，您可以添加一个计划指南，OPTION (QUERYTRACEON 9481)单独说明这个查询。

    • 5

  2. 我发现您的查询在条件上有很多冗余，并且您使用的交叉连接非常适合简单连接。这可能会使计划者感到困惑。也许您可以尝试对查询进行以下重新排列（它在功能上完全相同，但使用连接并删除所有冗余比较）以查看计划器在生产和测试中的结果？

    DECLARE @P1 AS BIGINT = 5637144576
DECLARE @P2 AS NVARCHAR(4) = N'1003'
DECLARE @P3 AS INT = 212
DECLARE @P4 AS BIGINT = 5638885273
--DECLARE @P5 AS BIGINT = 5637144576
DECLARE @P6 AS INT = 865
--DECLARE @P7 AS BIGINT = 5637144576
--DECLARE @P8 AS BIGINT = 5637144576

SELECT t1.balance01, t1.recid, t2.amountcur, t2.dataareaid, t2.recid, t3.recid, t3.voucher, t3.accountnum, 
       t3.approved, t3.closed, t3.dataareaid, t4.party, t4.dataareaid, t4.recid 
FROM ( 
      SELECT     virt.id AS dataareaid , t4.accountnum, t4.party, t4.partition, t4.recid 
      FROM       custtable t4 
      INNER JOIN virtualdataarealist VIRT ON (t4.dataareaid = virt.virtualdataarea)
      WHERE t4.partition = @P1
      UNION ALL 
      SELECT     t4.dataareaid , t4.accountnum, t4.party, t4.partition, t4.recid 
      FROM       custtable t4 
      INNER JOIN dataarea DAT ON ( t4.dataareaid = dat.id AND dat.isvirtual = 0)
      WHERE t4.partition = @P1
     ) t4 
JOIN custtrans     t3 ON (t3.partition = t4.partition AND t3.dataareaid = t4.dataareaid AND t3.accountnum = t4.accountnum)
JOIN custtransopen t2 ON (t2.partition = t4.partition AND t2.dataareaid = t4.dataareaid AND t2.refrecid = t3.recid)
JOIN spectrans     t1 ON (t1.partition = t4.partition AND t1.refcompany = t4.dataareaid AND t1.refrecid = t2.recid)

WHERE --t1.partition=@P1 AND -- redundant (already set in t1 join t2)
      t1.speccompany=@P2 AND 
      t1.spectableid=@P3 AND 
      t1.specrecid=@P4 AND
      t1.reftableid=@P6 AND
      -- AND t2.partition=@P5 -- already defined in the subselect
      -- AND t3.partition=@P7 -- redundant (already set in t2 join t3)
      -- AND t4.partition=@P8 -- redundant (already set in t3 join t4)
    • 2

  3. 我们解决了这个问题。

    该问题是由 SQL 2014 中新的基数估计器引起的。我们通过激活跟踪标志 9481 并从缓存中删除查询计划来禁用新的估计器，然后查询再次运行。

    • 2

相关问题