主页 / dba / 问题 / 27547
Accepted
Bryan
Asked: 2012-10-25 10:32:01 +0800 CST

查询性能和连接提示、计划成本和持续时间

  • 772

在确定为什么使用OPTION (HASH JOIN)or时查询的持续时间会减少OPTION (MERGE JOIN)，尽管计划成本会增加时，会遇到一些问题。

背景

我有一个使用传统星型模式（维度/事实表）的报告数据库。SQL由BI工具中的ORM生成。在添加优化器提示方面，我对生成的 SQL 有一定的灵活性，但仅此而已（没有查询重构等）。

问题

在没有优化器提示的情况下执行以下查询时，平均持续时间约为 90 秒，估计子树成本约为 2.9。当使用OPTION (MERGE JOIN)或OPTION (HASH JOIN)提示执行时，平均持续时间约为 3 秒，但估计的子树成本约为 4.9。

我已经验证受影响表的统计信息是最新的使用UPDATE STATISTICS <schema>.<table> WITH FULLSCAN;。还根据优化器建议添加了索引。

这是查询（是的，它很丑，请参阅上面的 ORM 评论）：

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

没有提示的实际执行计划

我很困惑为什么该计划显示了 5.72 亿个索引查找的实际行，并以红色标出。

执行计划，无提示

OPTION (HASH JOIN)使用提示的实际执行计划

执行计划、散列连接提示

我读过优化器提示是验证已应用适当的索引并验证统计数据是最新的最后手段。在这种情况下，SQL Server 似乎正在根据成本选择最佳计划，但在查询持续时间方面存在显着（约 87 秒）的损失。这听起来像是应该使用优化器提示的情况吗？如果不是，我应该检查哪些其他项目以确保优化器选择成本和持续时间的最佳计划？

t-sql sql-server-2008-r2

1 个回答

  1. Best Answer

    如果您甚至无法触摸查询，优化器提示的另一个选择是使用Plan Guides。但是，由于您的查询语句是动态的，有许多未参数化的标记，我怀疑这是可能的。

    既然你知道你的数据，我认为你最好只停留在查询提示的路径上。众所周知，统计数据是谎言、谎言和该死的谎言。它只能提供数据的整体形状，但对于特定条件（例如 fund_src_name_id 值、state_id 值、两者的组合等），它可能会产生错误的计划。

    • 1

相关问题