Deleting a lot of records fast

Deleting a lot of records fast

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

Running Microsofts Demo VPC on Windows 7

Running Microsofts CRM Demo VPC (Available on Partnersource) on Windows 7 is a bit problematic but can be done. I found a good blog that showed how it can be done using VirtualBox but there were some extra things that needed to be done to make it work.
As many of you probably are aware, from Windows Server 2008, only 64 bit versions of the server operating systems are available which isn’t very strange since the 32 bit versions of Windows only support up to 3.5 GB of memory which is really enough for even a small server.

Hence the Virtual Machine that Microsoft have released with a demo environment of CRM 2011 is based on this 64 bit environment. Since Windows Virtual PC, the Virtualization Technology for Windows 7 does not support 64 bit guest operating systems, it has been created on Hyper-V and is meant to be run on this virtualization technology. However, this is not really what you average CRM salesperson is running in his or her Lenovo X11 for many obvious reasons, power management probably being one of the most striking.

So, it is it just not possible to run the demo environment on a Windows 7 machine? Yes, it is. On the blog Leon’s CRM Musings there is a very informative posting on how to make the virtual harddrive work in the VirtualBox virtualization technology. I was quite amazed when I saw this working as this is quite similar to installing an operating system on a computer, ripping out the harddrive and screwing it into a completely different computer and hoping it will work. However, the Hyper-V virtualization and VirtualBox virtualization must be very similar from the guest operating system perspective, otherwise the operatingsystem would have given a lot of errors.

Do note the part in the posting that the demo image does not support being run as a SATA drive but must be run as an IDE harddrive.

There are some comments that both SharePoint and CRM are installed on port 80. This is in part true, but they are installed on different websites separated by different host headers.

However, the sharepoint site did not work when I tried running it, hence the document manangement from CRM did not work either. After checking the IIS website bindings and the host-file (C:system32driversetchosts) I noticed that the host headers for the SharePoint site and some other sites were pointed at the IP 192.169.100.1 and a quick check with ipconfig showed that this was not the ip of the current computer. Hence I just changed the mapping to be 127.0.0.1 (always means “this” computer).

I then closed all IE-windows, flushed the DNS (ipconfig /flushdns) just to be sure, then tried the sharepoint site, and it worked. As did the document management in CRM.

I saw some people in Leon’s blog commenting on the networking issues of making the exchange server communicate with the CRM-server. This can be done be setting the networkadapter in VirtualBox to “Internal Network” on the Exchange machine and using two adapters on the CRM machine, one with NAT (external) and one internal using the “Internal Network”. Both the internal networks adapaters need to be configured manually, but the external one can have a dynamic IP. If anyone doesn’t know how, please leave a comment and I will tell you how.

Regarding optimization of speed I strongly recommend upgrading your computers memory to at least 8 GB so that you can give the virtual machine between 4-6 GB. Try to give it even multiples of 512 MB as that will make for better memory management.

Another well known fact regarding VPC:s is disk access. It is highly recommended that you use a separate disc on a high speed controller (i.e. USB 3.0/eSATA/internal SATA).

As for processor power, it is not as essential, eventhough it is always nice. Try to allocate at least 2 cores to it. One for the SQL-server and one for IIS/GUI.

The recommendation that Leon gives to reduce the memory for the SQL server I am not so sure that is a good idea as CRM is very SQL heavy due to its meta data driven nature. However, if you only have 4 GB:s of memory and you have to make this VPC work, then you might have to try it. Kingston memory modules are very cheap now, 2×4 GB for a T410 is less than €100 last time I checked.

I have several computers, my “sales computer” is a Lenovo T410s Corei5 8Gb with 2 SSD HD. I use one of the harddrive for my host operating system and one for the virtual machines. I have removed the DVD-drive since I never use it, and the extra drive is a lot better use of the space.

Last of all, cudos to Leon for a great solution with VirtualBox for the VPC!

Update:
An additional thing to do is to convert the VHD to the Virtualbox native VDI-format for virtual harddrive using the media Media manager in VirtualBox. This is probably a more optimized format for Virtualbox and hence a bit Quicker. (I have not tested it, it is just a thought)
I have also tried to run the VPC on an external USB SSD, both USB 2.0 and USB 3.0 with 2 GB of RAM with 2 Cores on my Core i5 with the converted disk and it works fine. The SSD gives it a lot of extra speed.

Gustaf Westerlund
CEO, Chief Architect and co-Founder at CRM-konsulterna AB
www.crmkonsulterna.se