If you’ve been working on Dynamics projects for any length of time, you’ve probably seen a requirement come up involving attachments. In my own experience, there are few projects I’ve been part of where some form of conversation about attachments didn’t come up. When I initially started working with Dynamics it was common to hear someone say attachments were bad for performance. I initially accepted this and repeated this saying often, but I became irritated that I didn’t actually know why. I started asking people who I felt were very knowledgeable and they didn’t have any depth of explanation. The only reason that would come up was around database space consumption and this alone wasn’t enough for me until one day I came across an answer.
Attachments Anatomy 101
Attachments in CRM are stored in two different database tables which are ActivityMimeAttachment (Email Attachment) and Annotation (Note). If you’re using server-side synchronization for email attachments, they will be stored in the ActivityMimeAttachment table. If a user uploads a file to a note, the attachment is stored in the Annotation table. When you have a system where users are allowed to attach files you need to keep a watchful eye on these two tables because even with a small number of users the space taken up by attachments can easily grow exponentially. Think about your Outlook inbox throughout the year where people routinely send you attachments. If users are doing this as a matter of business with leads for example, you can quickly get into trouble.
Attachment Space Consideration
If you’re on-premises, your database size will grow, but one could argue it is more convenient from a system maintenance side because you can perform a single database backup and not have to worry about attachments which are in some other place. While this is true, I believe the extra effort in handling attachments elsewhere is overall a much better scenario than the mere backup convenience.
In an online environment, the consequence of attachment growth is more severe because online storage is a real expense that has a higher visibility due to cost. At the time of this writing, an office 365 tenant has a total space of 50 GB and that includes all services used, not just your Dynamics system. So it is a significant topic to address in design rather than after the fact because you end up trying to create a solution while you are forced to purchase more and more space.
Dynamics Is Still Old School
Dynamics being the proverbial trojan horse for selling other Microsoft platforms has historically had a heavy dependence on SQL Server. The attachment feature came out at a time when SQL Server had less functionality than it does today. To build provide the attachment feature the product team had a couple of basic options. The first option would have been to create a service that handled them in a configurable fashion and stored them on a Windows server for example. The next was to store the attachments in the CRM database. It would have certainly added implementation complexity to create a separate storage mechanism for attachments so (in my guess) the decision was made to go with database storage. To accomplish this they needed to serialize the file so they could store it in a database column. From then on attachments are serialized in a base 64 string format which means all attachments are converted to a string and then stored in a database table column. This was an easy and effective mechanism for storing attachments and was a great addition to the platform.
The challenge is that while this works well enough in smaller systems it gets pretty crazy from a space perspective in large-scale scenarios. It also means that as far as the database is concerned, it doesn’t distinguish between table data and attachments because effectively they are the same thing. While I don’t expect that the product team can always predict the future they have options to consider when newer functionality is released on dependent platforms.
Seeing a need in the industry for handling files the SQL Server product team came out with a feature called File Streams in SQL Server 2008. I assumed that this feature would eventually be added as an option because it would allow files to be stored on disk as opposed to in the database tables directly. Files Streams were never utilized, and I’m not sure why. I believe at the time when CRM online was still in its infancy it would have been a great thing, but I try always to give the benefit of the doubt that other matters were a higher priority. Besides, they probably don’t see it as a critical problem that can’t be addressed so it is what it is today.
Dynamics 365 online is changing into a pure Azure based system so the hardline dependencies on the SQL Server platform will go down and the various capabilities of the Azure platform will be utilized. The product team has already announced that Azure blob storage will be usable for online system backups so I believe it may not be long before blob storage could be used for attachments as well. I do believe this will have limited functionality as the preference is to upsell customers on SharePoint and OneDrive.
Database Lock Escalation Dangers
We’ve already mentioned the space considerations for attachments, but now we can talk about the real danger of attachments in an enterprise system. I mentioned earlier that storage attachments as column data obscure the difference between an attachment and normal table data. The situation which can occur in enterprise-class systems with a large number of users is what I refer to as a concurrency overload. It’s when a large number of concurrent users ask for attachments at the same time. If you have a number of attachments being retrieved at the same time in addition to other normal system loads it could potentially push the database into lock escalation which is where it begins using page locks instead of row locks.
Row locks are great because multiple users can update records in the same entity at the same time without interfering with each other unless they happen to be updating the same record at the same time. The downside to row locks is that the more you have the more memory is consumed. This is especially true in a busy database with both user and business automation processes performing transactions. If the database resorts to saving memory with page locks it will then lock a group of records instead of the individual records. This means that one of the two or more people who were updating different records will then be blocked by the transaction of the first. If this happens enough you can end up in a severe deadlock situation where a large number of transactions are all held up by a series of page locks. With the database being the one single point of failure for a Dynamics system this is a bad situation to be in.
As a consequence of database deadlocks, our font and back-end servers will have threads waiting on transactions to complete. As these threads continue to wait the Windows is checking on them to see when they complete so it can perform garbage collection on the memory they consume. There comes the point when Windows will stop checking long running threads. If the threads are stale long enough Windows will place the thread in what is called Gen 2 garbage collection. This is Windows saying “I give up!”. When threads start to enter Gen 2 garbage collection there is a special cycle where Windows server will stop all thread processes in an attempt to clean up of Gen 2 threads. Having the Windows server stop to clean up memory is not going to provide a great experience to users so as this continues to happen it will cause even more stress on the entire system with the eventual possibility of the entire system becoming unresponsive to users.
Bottom Line on Attachments
There are a few factors that cause native Dynamics attachments to be lesser quality choices for file management. The first is that they can take up a significant amount of space and it can be difficult to predict what the usage profile will look like in a production system. Secondly, in large systems, too many users querying attachments at the same time can potentially consume a large amount of database memory and cause lock escalation which is one of the ways that we can end up with poor performance. Lastly, I just don’t think it’s the best option when it comes to user experience. Attaching files through notes, in my opinion, is just not friendly or convenient. It was only until recently that these attachment were even searchable.
My advice is to look at all your options when it comes to attachments during system design. This means looking at SharePoint, OneDrive and OneNote. I’m sure soon Microsoft Teams will come into the picture and Teams uses SharePoint behind the scenes. You can even go a custom route and store attachments in your own in-house platform of choice. Granted it is often the case that tight budget necessitates the use of native attachments. Just understand what you’re potentially getting into and hopefully, in enterprise project scenarios there are at least future options that can be used to pivot out of native Dynamics attachments into something better suited for the user consumption and data volume.
If you’re interested in learning more about understanding and managing system performance take a look at my course below.