Update February 16th, 2017: Initial testing (and a look under the hood) show that Dynamics 365 On-Premise (8.2.0.749) has received this performance enhancement! Go try it out now!
Original Post follows.
I've posted a new item on the CRM Ideas Portal to try to get Microsoft to look at the terrible delete and import times which occasionally occur in Microsoft Dynamics CRM. If you commonly experience slow deletes, slow imports, or see messages like this in your event viewer (which means your users are seeing this):
then I encourage you to up-vote this item on the portal. (Yes, that is 53 minutes to import what contained some security roles!) In my research I've found the compile time of the fn_CollectForCascadeWrapper is horrendous (in particular fn_CollectForCascadeDelete, fn_CollectForCascadeDeleteSchema, and fn_CollectForCascadeRemoveLink). These methods are generated based on your metadata. The more entities and relationships you add to CRM the worse these methods get. The more organizations you have on the SQL server, the more likely you are to have the issue to occur.
In it I suggest an alternative that proved to use 28x less CPU, execute 4x faster, and use nearly 10x less memory in the SQL plan cache (on one sample data set).
This was testing against CRM 2016 with SP1 but goes back to at least CRM 2011.
Update October 03, 2016: A great escalation engineer at Microsoft took on my case, quickly reproduced, and opened a design change request with the development team. The Work Item is 346488. He tells me other customers are asking to implement my proposal also. THANK YOU dear reader for pushing for this change with me. Please continue to do so! Currently it may be 2 releases (about a year) before we know if this will be implemented.
Original Post follows.
I've posted a new item on the CRM Ideas Portal to try to get Microsoft to look at the terrible delete and import times which occasionally occur in Microsoft Dynamics CRM. If you commonly experience slow deletes, slow imports, or see messages like this in your event viewer (which means your users are seeing this):
Query execution time of 3183.0 seconds exceeded the threshold of 10 seconds. Thread: 22; Database: Hoven_2016051_MSCRM; Server:v2016sql; Query: select * from dbo.fn_CollectForCascadeWrapper(System.Collections.Generic.List`1[Microsoft.SqlServer.Server.SqlDataRecord], 7102, 0, 1, 0) order by i.
then I encourage you to up-vote this item on the portal. (Yes, that is 53 minutes to import what contained some security roles!) In my research I've found the compile time of the fn_CollectForCascadeWrapper is horrendous (in particular fn_CollectForCascadeDelete, fn_CollectForCascadeDeleteSchema, and fn_CollectForCascadeRemoveLink). These methods are generated based on your metadata. The more entities and relationships you add to CRM the worse these methods get. The more organizations you have on the SQL server, the more likely you are to have the issue to occur.
In it I suggest an alternative that proved to use 28x less CPU, execute 4x faster, and use nearly 10x less memory in the SQL plan cache (on one sample data set).
This was testing against CRM 2016 with SP1 but goes back to at least CRM 2011.
Update October 03, 2016: A great escalation engineer at Microsoft took on my case, quickly reproduced, and opened a design change request with the development team. The Work Item is 346488. He tells me other customers are asking to implement my proposal also. THANK YOU dear reader for pushing for this change with me. Please continue to do so! Currently it may be 2 releases (about a year) before we know if this will be implemented.
