Início / dba / Perguntas / 27547
Accepted
Bryan
Asked: 2012-10-25 10:32:01 +0800 CST

Desempenho de consulta e dicas de junção, custo do plano e duração

  • 772

Tendo alguma dificuldade em identificar por que a duração de uma consulta diminuiria ao usar OPTION (HASH JOIN)ou OPTION (MERGE JOIN), embora o custo do plano aumentasse.

Fundo

Eu tenho um banco de dados de relatórios usando esquema em estrela tradicional (tabelas de dimensão/fato). O SQL é gerado pelo ORM na ferramenta de BI. Tenho alguma flexibilidade com o SQL gerado em termos de adição de dicas do otimizador, mas é isso (sem refatoração de consulta, etc.).

Problema

Quando a consulta abaixo é executada sem sugestões do otimizador, a duração média é de cerca de 90 segundos e o custo estimado da subárvore é de cerca de 2,9. Quando executado com as dicas OPTION (MERGE JOIN)ou OPTION (HASH JOIN), a duração média é de cerca de 3 segundos, mas o custo estimado da subárvore é de cerca de 4,9.

Eu verifiquei que as estatísticas nas tabelas afetadas estão atualizadas usando UPDATE STATISTICS <schema>.<table> WITH FULLSCAN;. Os índices também foram adicionados com base nas recomendações do otimizador.

Aqui está a consulta (sim, é feia, veja o comentário do ORM acima):

SELECT  a11.trans_00_key  TRANS_00_KEY,
    a11.region_id  REGION_ID,
    Max(a15.region_cd)  REGION_CD,
    a11.state_id  STATE_ID,
    Max(a13.district_cd)  DISTRICT_CD,
    a12.cntrct_nbr  CNTRCT_NBR,
    a11.proj_nbr  PROJ_NBR,
    Max(a11.proj_nbr)  PROJ_NBR0,
    CONVERT(DATETIME, CONVERT(VARCHAR(10), (a12.sys_date_yr + '-' + a12.sys_date_mon + '-01'), 101))  CustCol_5,
    a12.proj_ctgry_nbr  PROJ_CTGRY_NBR,
    a11.type_of_work  TYPE_OF_WORK,
    a11.funct_rng  FUNCT_RNG,
    Isnull(a11.fis_id, -1)  FIS_ID,
    Max(Isnull(a14.fis_dscr, 'Blank'))  FIS_DSCR,
    CASE WHEN a12.bid_amount > 1 THEN a12.bid_amount ELSE a12.eng_est_amt END  CustCol_7,
    Sum(a11.est_amt)  WJXBFS1,
    (Sum(a11.ltd_amt) - (Sum(a11.ltd_ind_bill_cst) + Sum(a11.ltd_ind_non_bill_cst)))  WJXBFS2,
    Sum(a11.ltd_cost_cntrct)  WJXBFS3,
    ((Sum(a11.ltd_amt) - (Sum(a11.ltd_ind_bill_cst) + Sum(a11.ltd_ind_non_bill_cst))) - Sum(a11.ltd_cost_cntrct))  WJXBFS4,
    (Sum(a11.est_amt) - (Sum(a11.ltd_amt) - (Sum(a11.ltd_ind_bill_cst) + Sum(a11.ltd_ind_non_bill_cst))))  WJXBFS5
FROM    sys_trans_detail_fact   a11
    JOIN    sys_trans_hdr_fact  a12
      ON    (a11.proj_nbr = a12.proj_nbr AND
    a11.trans_00_key = a12.trans_00_key AND
    a11.state_id = a12.state_id)
    JOIN    district_lkp    a13
      ON    (a11.state_id = a13.state_id)
    JOIN    fis_lkp a14
      ON    (Isnull(a11.fis_id, -1) = Isnull(a14.fis_id, -1))
    JOIN    region_lkp  a15
      ON    (a11.region_id = a15.region_id)
WHERE   (((a11.trans_00_key)
 IN (SELECT r12.trans_00_key
    FROM    sys_trans_detail_fact   r12
    WHERE   r12.fund_src_name_id IN (3, 7, 5)))
 AND a11.fund_src_name_id IN (6, 8, 2, 3, 7, 5, 4)
 AND a11.state_id IN (8, 4, 19, 14, 20, 23, 17, 25, 16, 18, 24, 2, 12, 22, 5, 11, 6, 1, 21, 7, 15, 10, 9, 3, 13, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36)
 AND a11.status_id = 1
 AND a11.extract_dttm IN (CONVERT(DATETIME, '2012-09-01 00:00:00', 120))
 AND a11.cost_cat_id IN (10, 4))
GROUP BY    a11.trans_00_key,
    a11.region_id,
    a11.state_id,
    a12.cntrct_nbr,
    a11.proj_nbr,
    CONVERT(DATETIME, CONVERT(VARCHAR(10), (a12.sys_date_yr + '-' + a12.sys_date_mon + '-01'), 101)),
    a12.proj_ctgry_nbr,
    a11.type_of_work,
    a11.funct_rng,
    Isnull(a11.fis_id, -1),
    CASE WHEN a12.bid_amount > 1 THEN a12.bid_amount ELSE a12.eng_est_amt END

Plano de execução real sem dicas

Estou confuso sobre o motivo pelo qual o plano mostra 572 milhões de linhas reais para a busca do índice destacadas em vermelho.

Plano de execução, sem dicas

Plano de execução real usando OPTION (HASH JOIN)dica

Plano de execução, dica de junção de hash

Eu li que as dicas do otimizador são o último recurso depois de verificar se os índices apropriados foram aplicados e se as estatísticas estão atualizadas. Nesse caso, o SQL Server parece estar escolhendo o melhor plano com base no custo, mas há uma penalidade significativa (cerca de 87 segundos) em termos de duração da consulta. Isso soa como um caso em que a dica do otimizador deve ser usada? Caso contrário, quais outros itens devo verificar para garantir que o otimizador escolha o melhor plano para custo e duração?

t-sql sql-server-2008-r2

1 respostas

  1. Best Answer

    Outra opção para otimizar dicas, se você não pode nem tocar na consulta, é usar o Plan Guides . No entanto, como sua instrução de consulta é dinâmica com muitos tokens não parametrizados, duvido que seja possível.

    Como você conhece seus dados, acho melhor ficar apenas no caminho das dicas de consulta. As estatísticas são conhecidas por serem mentiras, mentiras e malditas mentiras . Ele só pode fornecer uma forma geral dos dados, mas para condições específicas (por exemplo, valores fund_src_name_id, valores state_id, combinação dos dois, etc.) pode apenas produzir o plano errado.

    • 1

relate perguntas