influencing CRM generated SQL (optimizer hints, etc)

We have a client that i开发者_运维知识库s 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?

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.

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:

Modifications to the physical schema of the database, other than adding or updating indexes. This includes any actions performed against the database without going through System Customization. Modifying tables, stored procedures, or views in the database is not supported. Adding tables, stored procedures, or views to the database is also not supported because of referential integrity or upgrade issues. Adding indexes is supported per the guidelines in the “Microsoft Dynamics CRM 2011 Implementation Guide”. This applies to all Microsoft Dynamics CRM databases and the Microsoft Dynamics CRM for Microsoft Office Outlook local database.

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.