查询执行计划 - 调整建议 [英] Query Execution plans - Tuning suggestion

问题描述

您是否可以通过所有成本和指标查看查询及其执行计划

并建议对查询重写和索引更改等进行调整建议降低成本和提高性能（或）它们看起来很好并且不需要进行任何更改。

你能不能对计划中的成本进行一些说明[比如 - 查询计划4中的索引扫描79％，这很好......而且估计没有。行和实际执行，
子树成本等我们如何根据这些指标得出结论]以供我理解

（1）

https://www.brentozar.com/pastetheplan /？id = rJdp6DsqN

（2）

https://www.brentozar.com/pastetheplan/?id=HJYhlds94

（3）

https://www.brentozar.com/pastetheplan/?id=r1nS2Q3qE

（4）

https://www.brentozar.com/pastetheplan/?id=r1JG6m3qV

非常感谢。

---

Kenny ShareNlearn

解决方案

Hi
Kenny shareNlearn ，

 

感谢您的发帖。

 

在您提供的执行计划中，有一些键查找。 

 

当我们查看索引扫描和表扫描部分时，我们能够消除被索引搜索替换的扫描，但这也引入了一个Key $ Look，这是
其他的东西您可能希望消除以提高性能。

 

在非聚集索引中找到数据时会发生键查找，但聚簇索引需要额外的数据来满足查询，因此会进行查找。 
如果表没有聚集索引，则会发生RID查找。

 

有关
的详细信息 键查找 ，请参阅
消除书签（键/删除） 。

 

在这个链接中，它会建议创建一个覆盖索引到
通过包含所有需要的列 到
消除来覆盖查询 主要查找 。 
请尝试使用以下脚本来更改索引。

 ALTER NONCLUSTERED INDEX [ix4_pcwdeptrans] ON [pcwdeptrans]（id）INCLUDE （statecode）
 ALTER NONCLUSTERED INDEX [ix2_pcwdepreceipt] ON [pcwdepreceipt]（id，deposittransid）INCLUDE（requesttime）
 ALTER NONCLUSTERED INDEX [ix2_pcwitemtotal] ON [pcwitemtotal]（id）INCLUDE（statecode，createdate，userid ）

 

希望它可以帮到你。

 

最好的问候，

拉结

Could you please go through the queries and its execution plan with all the cost and metrics

and suggest tuning recommendations on query rewrite and index changes etc to reduce the cost and better the performance (or) does they look fine and no changes are needed.

can you brief a bit on the cost from the plans [ say - index scan 79% in query plan 4 , is that fine... and on estimated no.of rows and actual execution, sub-tree cost etc how do we draw out the conclusions based on these metrics] for my understanding

(1)

https://www.brentozar.com/pastetheplan/?id=rJdp6DsqN

(2)

https://www.brentozar.com/pastetheplan/?id=HJYhlds94

(3)

https://www.brentozar.com/pastetheplan/?id=r1nS2Q3qE

(4)

https://www.brentozar.com/pastetheplan/?id=r1JG6m3qV

Thanks a lot.

---

Kenny ShareNlearn

解决方案

Hi Kenny shareNlearn,

 

Thank you for your posting.

 

In your execution plan which you provide , there are some key lookup. 

 

When we were looking at the index scan and table scan section we were able to eliminate the scan which was replaced with an index seek, but this also introduced a Key Lookup which is something else you may want to eliminate to improve performance.

 

A key lookup occurs when data is found in a non-clustered index, but additional data is needed from the clustered index to satisfy the query and therefore a lookup occurs.  If the table does not have a clustered index then a RID Lookup occurs instead.

 

For more information about Key Lookup , please refer to Eliminating bookmark (key/rid) lookups.

 

And in this link , it will advise to create a covering index to cover the query by including all of the columns that are needed to eliminate Key Lookup.  Please try following script to change your index.

ALTER NONCLUSTERED INDEX [ix4_pcwdeptrans] ON [pcwdeptrans] (id)  INCLUDE (statecode)
ALTER NONCLUSTERED INDEX [ix2_pcwdepreceipt] ON [pcwdepreceipt] (id,deposittransid)  INCLUDE (requesttime)
ALTER NONCLUSTERED INDEX [ix2_pcwitemtotal] ON [pcwitemtotal] (id)  INCLUDE (statecode,createdate,userid)

 

Hope it can help you.

 

Best Regards,

Rachel

这篇关于查询执行计划 - 调整建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！