我们有一个客户正在针对一个非常大的数据库使用 CRM。他们的开箱即用操作速度减慢，并且我们观察到在这些速度减慢期间 SPID 被阻塞。我们捕获了 CRM 生成的 SQL，并让 DBA 对其进行分析。
我们的 DBA 可能会找到一种通过调整 SQL 结构或优化器提示来提高性能的方法。然而，我们正在分析的SQL是在CRM的严格控制之下的。
所以这是我的问题......
使用 fetch xml 有没有办法影响最终从 CRM 生成的 SQL 的结构/优化器提示？或者，如果我们的 DBA 建议更改 SQL 以使其更快，我们是否必须向 Microsoft 寻求解决方案？
We have a client that is using CRM against a very large database. They are experiencing slowdowns on out of the box actions, and we observe blocked SPIDs during these slowdowns. We captured the SQL generated by CRM and have a DBA analyzing that.
Our DBA may find a way to increase the performance by adjusting the SQL either in structure or optimizer hints. However, the SQL we are analyzing is under the strict control of CRM.
So here is my question…
With fetch xml is there a way to influence the structure/optimizer hints for SQL that is ultimately generated from CRM? Or if our DBA were to suggest changes to the SQL to make it faster, do we have to turn to Microsoft for a solution?
发布评论
评论（2）
即使您无法更改 SQL，您也可以改进正在交互的物理表的索引和统计信息。如果您发现该领域的改进，则可以改进优化器生成的查询计划。
查看数据库引擎优化顾问 (DTA) - 它可以提供帮助您可以在单个查询的基础上或在工作负载分析的基础上进行这种类型的优化。
Even if you can't change the SQL, you might be able to improve the indexing and statistics for the physical tables you're interacting with. If you find improvements in that area, it can improve the query plan the optimizer generates.
Check into the Database Engine Tuning Advisor (DTA) -- it can help you with this type of optimization, either on a single-query basis or on a workload-analysis basis.
正如 mwigdahl 所说，除了影响生成的 SQL 语句之外，还有其他方法可以调整数据库性能。这些也是唯一受支持的更改，您可以自己进行。
Microsoft 认为不支持对数据库进行更改。请参阅 Microsoft Dynamics 支持的扩展客户关系管理：
您无法直接调整 FetchXML 到 SQL 的转换。你只能优化它，不能包含不必要的信息等等。如果您确实遇到性能问题，我建议您将 CRM 支持纳入您的调查中。
As stated by mwigdahl, there are other possibilites to tweak the database performance than to influence the generated SQL statements. These are also the only supported changes, you could do yourself.
Microsoft considers changes to the database not supported. See Supported Extensions for Microsoft Dynamics CRM:
You cannot adjust the translation of FetchXML to SQL directly. You are only able to optimize it, not including unnecessary information and so on. If you have real performance problems, I would recommend to include the CRM support in your investigations.