Deleting a lot of records fast

Deleting a lot of records fast

by | Feb 8, 2018

A quick one today…

Needed to delete a couple of million records for a customer and the natural thing was to use the Bulk Deletion service, well, I turned it on and it was extremely slow. Only got about 10 records/s which would cause the entire delete to take over a week. I have checked with Microsoft and this is not a bug, but it is working as designed and is not designed to be super fast. According to Microsoft bulk deletion jobs are put on the async queue on low priority to allow other more important jobs higher prio.

And a favorite quote of mine from Purvin Patel of Microsoft “Does a dump truck need to outrace a Ferrari?” – and I think that the answer to that question is: it depends. Sometimes it does.  

Personally I would sometimes like it to be as fast as possible when removing a lot of records.

I also checked to see how fast the deletion would be with SSIS and Kingswaysoft. Used the following settings:

  • VM about 5 ms from the Dynamics 365 instance (important that it not be too far, use an Azure VM for this)
  • Used 64 threads
  • Used Execute Multiple batching with 10 (cannot use more that 10 if you are using a lot of threads, ie more than 2)
  • VM has 8 virtual cores and 32 GB memory
  • Loading in batches of 2000. Only loading the id-column, as that is all that is needed.

With this setup, I got somewhere around 345 records deleted per second. Which is a tad more than 34x faster than the bulk delete.

So, want to delete a lot of stuff, maybe Bulk Delete is not the way to go. Not yet anyway, let’s hope Microsoft makes it faster!

(this post was updated on Feb the 9:th 2018)

Gustaf Westerlund
MVP, Founder and Principal Consultant at CRM-konsulterna AB
www.crmkonsulterna.se

Common performance problem

by | Oct 26, 2007

Now Convergence is over and I feel stuffed with info and experiences. There is so much I would like to write about and so little time. I will try to spread it out a bit.

During one of the sessions Clint Warriner, an Escalation Engineer (he writes hotfixes) held a very interesting chalk-&-talk about CRM system maintanance and performance best practices.

It was crammed with goodies, and I will write more about it later, but one interesting thing he talked about was the possible delay in loading forms. He said this, most often, depends on the SQL server. When loading forms the list of which reports are relevant and so on, are also loaded from the Reporting server. This can, in some cases, take some time, and in some bad cases, really afect the load time of forms.

They have created a hotfix for this, that can be requested from Microsoft support (no cost) if you give the referece: KB 941592. It simply caches the information on what reports exist so that the request to the SQL-server doesn’t have to be executed every time.

I will get back to the subject of performance tuning later.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se