Início / dba / Perguntas / 136768
Accepted
Daniel Björk
Asked: 2016-04-28 10:36:15 +0800 CST

A consulta não compila/executa

  • 772

Tenho uma consulta que não será executada no servidor de produção, mas será executada no servidor de teste. Parece que o plano de consulta não compila ou está compilando um plano de consulta muito ruim. Atualizei todas as estatísticas com fullscan e reconstruí os índices de todas as tabelas envolvidas sem sucesso, não são tantas linhas em nenhuma das tabelas. Não estou conseguindo alterar a consulta porque é o AOS (AX 2012) que está criando. O que devo fazer para poder usar a consulta como está?

Consulta

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)))
  • Se eu usar o plano de execução do servidor de teste, ele será executado em menos de um segundo.
  • Eu tentei OPTION (RECOMPILE)e tambémDBCC FREEPROCCACHE
  • Não há bloqueio

Tabela de diferenças "sys.configurations" entre PROD e TEST:

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

Detalhes do ambiente

PROD: Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 20 de fevereiro de 2014 20:04:26 Copyright (c) Microsoft Corporation Enterprise Edition: Licenciamento baseado em núcleo (64 bits) no Windows NT 6.3 (Build 9600: )

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

TESTE: Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 20 de fevereiro de 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64 bits) no 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

Nível de compatibilidade: 120 em ambos.

Detalhes dos dados

PROD

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

Plano de execução estimado: http://pastebucket.com/326386
Estatísticas - http://pastebucket.com/326459

TESTE

 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

Plano de execução real: http://pastebucket.com/326387
Estatísticas - http://pastebucket.com/326458

sql-server performance

3 respostas

  1. Best Answer

    Como você está executando o Microsoft SQL Server 2014 - 12.0.2000, a primeira compilação RTM incluindo o novo Estimador de cardinalidade, sugiro fortemente que você tente atualizar para uma das CUs mais recentes.

    Conforme declarado nesta postagem do blog no msdn

    Você precisa aplicar o SP1, mas também deve ativar o sinalizador de rastreamento 4199 para ativar a correção.

    O SQL Server 2014 Service Pack 1 fez várias correções no novo Estimador de Cardinalidade (novo CE). As notas de versão também documentam as correções.

    Você já tem o TF 4199 ativo, então as melhorias devem se tornar ativas automaticamente.

    Eu recomendo fortemente que você atualize e use o novo Estimador de cardinalidade, mas com todas as correções mais recentes, em vez de desabilitar o novo CE totalmente.

    Se você ainda tiver problemas com essa consulta única, poderá adicionar um guia de plano que diga OPTION (QUERYTRACEON 9481)apenas para essa consulta.

    • 5

  2. Descobri que sua consulta tinha muitas redundâncias nas condições e você usou junções cruzadas que eram boas candidatas a junções simples. Isso pode confundir o planejador. Talvez você possa tentar o seguinte rearranjo da consulta (funcionalmente é exatamente o mesmo, mas usa junções e remove todas as comparações redundantes) para ver o que o planejador apresenta na produção e no teste?

    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. Nós resolvemos o problema.

    O problema foi causado pelo novo estimador de cardinalidade no SQL 2014. Desativamos o novo estimador ativando o sinalizador de rastreamento 9481 e removendo o plano de consulta do cache, então a consulta funcionou novamente.

    • 2

relate perguntas