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 real usando OPTION (HASH JOIN)dica
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?