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.
4 comments:
Thank you for providing this info. I work with an IFD deployment and we have 60 orgs. I see this event in eventvwr all the time. Is it a matter of fixing dbo.fn_CollectForCascadeWrapper, creating an index somewhere, or something else? Any help is appreciated.
@tbh,
I do not have a fix for this issue. Our work around has to be avoid deletes and use bulk deletes to remove records off hours. We're trying to have Microsoft fix via a code change. The current status is that they are considering for a future release. If you have a support agreement, I encourage you to open up a support case with Microsoft regarding your performance issues and throw your support behind Work Item 346488.
Thank you, John for Update February 16th, 2017. That's a good news
Hi
Can you provide more details on the performance improvement? we are facing the same issue on our on-premise system (8.1) and wondering if upgrading to 8.2 would solve it.
Post a Comment