Microsoft recently (in February) published some updates to their documentation regarding Service protection API limits or as they are sometimes referred to, throttling. Some of these, like the new recommendations on how to handle batching are rather interesting and I thought I’d give my 2 cents about this. They are also eluding a bit regarding how the network infrastructure is set up for the deployment and how to optimize when handling larger workloads using the affinity cookie setting. I did find this rather interesting too.
In short, within a 5 minute sliding window, you cannot exceed the following for one specific user.
Not more than 6 000 requests.
Not more than 1 200 (20 minutes) execution time – equal to 4 parallell processes if running at full capacity
Not more than 52 request at the same time (concurrently).
Generally, if you do not use batching, you would typically run into the first (1) or the third if running unlimited threading. If using a connection pooling with 52 connections, then you probably run into the 1:st, and if you use complex request that cause cascading behaviours or batching, then you typically run into the second (2). There are exceptions that match these. Do refer to the official docs above for details about that.
Now to the interesting part. There is a new section that is attempting to tells us how to maximize throughput. First of all, I think this is great. We really need this and we need Microsoft to tell us how to not only use the platform, but how to efficiently use their platform.
“Let the server tell you how much it can handle” This section is interesting as it recommends a rather complex approach to how to work with performance. As they further down recommend using threading, they essentially recommend building logic that dynamically increases and decreases the number of threads as the platform informs you that it has capacity. This brings me back to university math, and trying to figure out the derivative of an unknown function by sampling and finding the local max. I do however, think this is a rather tall order to recommend to your average developer. But it would be a great community tool, so feel free to build it. Consider the challenge set. Best would be if Microsoft included this in the SDK of course.
“Use multiple threads“ In this part they recommend using multiple threads. This is also my experience that this is a good idea as the processing time and latency per package causes certain delay on a per-message basis. By utilizing multi-threading with multiple connections, this overhead can be reduced. As there is a limit of 52 concurrent connections, I would recommend using a maximum of that amount of connections/threads per user.
Avoid batching Now this is really interesting. The previous recommendation was to use batching to be “nicer” to the API and get increased performance. The recommendation now is the direct opposite. This is based on the fact that the overhead in a WebAPI JSON-message is significantly smaller than that in a SOAP message and that this will reduce the difference between using batching and non-batching. They do, however, recommend using smaller batch sizes still. This is also my experience when working with Kingswaysoft. I typically (it depends on the instance and which table I am using) start with 16 threads with batches of 10 or 20. This has typically given me the best performance, with performance of +300 records/s.
There is also a comment about the fact that the using batching does not bypass the entitlement limits, ie. 20 000 API calls/24 hours for an enterprise user/100 000 API calls for all non-licensed users and so on. See more on the Entitlement limits based on which license you have here. Hence this calculation is done by after exploding the batch on the servers. This is also news to me as I previously was told that batching was exactly the way to go to limit the amount of calls.
Removing the affinity cookie – server multiplexing The details being eluded to in this section are very interesting. If I understand it correctly, the logic is as follows:
The point being that, shutting off the affinity cookie int the HttpClient will allow for more wider use of all the servers in the node (the entire setup of all the Frontends, backends, NLB etc.)
What I do wonder, is if it would be possible to store the Affinity Cookie, and hence pool it on the client side. As each time you need to hit a new front end you will loose some time while it warms up your instance, and it would hence be advantageous to be able to more tightly control this. Maybe even this could be another community tool for someone interested? I also think, I havn’t tested this, that you will get better results when working with removed affinity cookie if you do use batching, at least until all the frontends have been warmed up to your instance.
Do also note a very important sentence; “This increases throughput because limits are applied per server“. We do not know how many servers are used in a node frontend, but probably more than 10. Removing the affinity cookie could hence increase performance by at least one order of magnitute.
User multiplexing As all API limits are calculated on a per-user basis, another way to increase performance is to use what I like to call user multiplexing. This means that operations are done using several different application users at the same time. There is of course some admin work that needs to be done to set these up, and there is no OOB way of doing this but with SSIS and Kingswaysoft it is rather straight forward; just create several connections, one per user, configure them per user and then use the “Balanced Data Distributor” which can be found in the productivity pack to spread the data to different destinations that are using the different connections.
My tips My tips for getting good performance, for large scale datasets, are hence the following based on these new facts:
Continue to use batching, but don’t use huge batches. Probably around 5-20 will be ok.
Use multithreading. I typically use around 16, but that was before I knew about the removal of the affinity cookie. Hence I would recommend 16 per server. But I cannot tell you how many servers there are.
Use the remove affinity cookie setting, and if possible, figure out some way of pooling the affinity cookies instead.
Make sure your application can handle the exceptions regarding the API-limit and have some reasonable strategy for working with them. I have found that blasting the API for 5 minutes at max speed, then backing off for 5 minutes, then going full throttle again for 5 minutes, has given me better throughput overall than “being nice” and just finding the “right” speed to use to not be throttled. Not sure this strategy will work in the long run though.
Use application user multiplexing.
Suggestions to ETL vendors and others My suggestions to ETL vendors and others who build connections to Dataverse that require high performance are:
Start by visualizing the affinity cookie setting so that it is possible to set this as wanted.
Include multithreading, batching and application user multiplexing into the standard dataverse connections.
Figure out if there are an points to pooling the affinity cookies, and if so, include this into the connection.
Make the connection auto-optimize with the data it is currently sending. Ie. how many threads, size of batches, size of affinity cookie pool and number of application users to utilize.
Have different strategies for utilizing application users instead of just spreading the data evenly, it could be that one is used until it receives an exception an then it is put on hold for 5 minutes and then another is being used. Or a combination of these two if there are five application users, 3 might be used for data transfer, and two on hold in case one gets an exception and needs to be put on hold.
I hope this has given you some insights and that my 2 cents got you this far. Feel free to leave a comment if you have an questions!
Some people might have heard about an industry best practice that you should never have custom columns (fields) on the systemuser table (entity) in dataverse. Is this true and why so? This article is based on my understanding of how the inner workings of dataverse works and hence what you need to think about when designing your application to not unintentionally create an application that destroys your environments performance. In short, be careful about adding custom columns to the systemuser and if you do, only add fields that have static data, ie data that doesn’t often change. Let me describe this in more detail.
First of all, I would like to give credit to a lot of this to my friend and former Business Application MVP Adam Vero, who described this in detail for me, I have also discussed this with other people and since had it confirmed but not actually seen it documented as such, why it might not be fully official. I do, however, not see any problems with people understanding this, rather the opposite.
Dataverse is an application platform that has security built into it as an integral part, there are security roles, system users, teams and business units that form the core pieces of the security in the system. As the system will often need to query data from these four tables, it has a built in “caching” functionality that per-environment loads these four tables and precalculates them into an in-memory table for easy and fast access. This is then then stored in-memory for as long as the data in these four tables is kept static, in other words, nothing is changed, no updates, no creates, no deletes.
What could then happen if you add a column to the systemuser table? Well, that depends. If this column is a column that you set when the user is created and then never change that, that isn’t a problem, as this wouldn’t affect the precalculated in-memory table. However, if the data of these columns are constantly being changed, like for instance, if you add a column called “activities last 24h” and then create a Flow which every time an email, appointment etc. is created it will increment this by one per day and reset it every night for every user. Then every time, this writes to any user, the precalculated in-memory table will be flushed and recalculated before it can be used again causing a severe performance hit that can be very hard to troubleshoot.
How would you create a solution for a the “activities last 24h”, as described above then? Well, I would probably create a related entity called userstatistics with a relationship to systemuser. In this case it could even be smarter to have a 1:N relationship to this other entity as you could then have many userstatistics per user and measure differences in activities day by day.
But wouldn’t the NLB:s (Network Load Balancer) make this irrelevant as each environment is hosted together with many others? Well, I cannot, due to NDA talk about the details of how the NLBs actually work for the online environements, but I can say this, no, it is still relevant. The NLB will make it so for performance reasons.
As for teams, it is only the owner teams that count in this equation, the access teams are only being used for sharing or other types of grouping and hence never part of this pre-calculation.
And the savvy person would then of course realize that the multiplied size of:
systemusers x owner teams x business units x security roles
Does make up the size of this pre calculated table and for large implemenations, this can give indications of where performance can start to make a difference as every time a user does not have organizational level privilige, the system has to go through the entire table to check what is right. And then of course the POA. But that table is story for another day and another article.
Just final word. The platform is constantly shifting and even though this was true and probably still is true, there might be changes going on or that have happened that I am unaware of, that have changed how this works. If I hear of this, I will let you know.
As you probably will have heard by now, my colleague and friend, Jonas Rapp suffered a stroke about two weeks ago. Daryl LaBar, Carina Claesson, Benedikt Bergmann and I talked about this on the XrmToolCast which you can listen to here: https://xrmtoolcast.libsyn.com/coming-together-to-support-jonas
I would, with this post, like to give my personal perspective on how I use FetchXml Builder as an integral part of making scripts with SSIS and Kingswaysoft.
And before I begin, I would just like to urge everyone to donate a dollar or two to Jonas as he is having tough time. Or just share your story of why you love to use his tools. Everything matters. And if you want to donate, just use this address: https://fetchxmlbuilder.com/donate. If he has helped you, as he has helped me, why not help him back. Why not leave a comment with how much you donated, and when we have 10 comments, I will share how much I have donated.
With one of my customers, I work a lot with trying to manage their rather larger database, around 500 GB, and doing what I can to make sure that it doesn’t grow out of proportion. This also entails looking into GDPR rules and making sure that personal information is removed, and kept according to specific rulesets that become rather complex. So complex that one advanced find query isn’t enough to define them. And the removal of data is not seldom in the millions of records, why I need a tool that can do deletes quickly. Hence, I have found that SSIS and Kingswaysoft with Azure Data Factory for deploying these packages to be run continously, is a good method.
However, there is a very important piece of the puzzle that is missing when you want to do this, and that is being able to assemble the FetchXmls to the nitty gritty level that they are as efficient as possible and for that job, there really is only one tool, FetchXml Builder. Hence I often set up my general idea of how I would like the rules to be executed and then start assembling the data often into different Cache transforms, and then filter the dataflows using lookups based on this.
So, for me, FetchXml Builder is essential when working with this and this is my story of how critical it is for me.
Dataverse, and new terms for picklists, fields and more. This has been described in detail by many others in the community like Jukka Niiranen. However Microsoft are saying that there will be no new API, that all API:s will stay the same so this article is just going to discuss this subject briefly.
First of all, they will, of course do exactly what they are saying. The current WebAPI will of course be available for many years to come as we have seen the old 2011 SOAP API still be available and used a lot, still today, often for the reason that there isn’t full functional parity between the API:s.
Some tools allow you to seamlessly choose between the API:s, like Kingswaysoft, and I have asked Daniel Cai, the owner and head developer/architect at Kingswaysoft which API I should use. I don’t know if he has changed his recommendation, but last year, he still recommended, if my memory hasn’t failed me, to use the old SOAP API due to stability and functionality issues. I will let him, on their blog describe their thoughts on this, if they want to.
However, I think this is rather telling of how Microsoft act and will act regarding an older API, that it will be maintained and kept alive for many years.
That brings us to the interesting question if there will be a new API based on these new terms, I frankly do not know, but my deduction would say a definite yes.
It think the easiest way of seeing this is falsifying this and extrapolating it. In other words, what would happen if they didn’t create a new API and we looked at the platform as a whole in 5-7 years without knowing the history? It would look totally crazy. A platform with one set of terms in the UI and another set of terms in the API:s. It would make documentation really complex as there would be need for translations between these two concepts everywhere and the bar for getting developers into the platform would be higher than necessary.
Hence the only reasonable deduction is that they will make a new API with the new terms. It will probably be release in 6 months or so, but the current WebAPI will still continue to work for many years to come.
These are my personal thoughts and are not based on any information from Microsoft, despite the fact that I am an MVP. I urge you to make your own deductions and I think you will find that this seems reasonable. Please share your thoughts disagreements or otherwise in the comment section below.
The reason I often recommend Kingswaysoft over other methods of data migration or even sometime data integration is rather simple, it can do what others can’t. Not even Microsoft. And it can do it fast.
So have I been payed by Kingswaysoft to write this? No, not a penny, I havn’t even been given a free license even though they might if I asked. I think it is just fair that I explain why I am such a strong proponent for this product and if anyone disagrees, please feel free to drop a comment below.
API knowledge
Kingswaysoft both know how the API:s of the Power Platform/CDS/Dataf??x (called CDS below) work, sometimes even better than Microsoft themselves as I have seen feedbacks given in such detail from them on what is missing from specific API:s to reach feature parity, that it is scary. Kingswaysoft also has a blog which has details recommendations and built in recommendations in the product on how to maximize performance with the Dataflex API. They have also built in handling for throttling, multi-threading, batching and more to just make it transparent. When looking at other integration products and connectors. I have never seen anything that is close to this depth.
Datamodel and Dynamics knowledge
Dynamics 365 Sales, Customer Service and other first party apps have some very peculiar oddities that need to be taken into consideration. These oddities range from for example:
How to delete Marketing List members – by using the party member and list as key
How audit logs are handled
How Activities work, with activity pointer and activity parties
Setting CreatedOn/CreatedBy/ModifiedOn/ModifiedBy
Reading/Writing personal views (saved advanced finds)
Setting statusvalues of some fringe entities
Even though many tools, like Power Automate, Data Flows, Azure Data Factory, etc. all seem to handle common tasks like creating or reading a contact or account rather well, it soon becomes a problem when you start looking at some of the areas above. And you often need to in a migration.
It can use the power of SSIS
SQL Server Integration Services is a very powerful framework once you start getting your head around it. Yes, it has some quirks to it but in general it can do some rather cool things and is good when you want to sequence many different tasks in order to reduce overall runtime. Using built in features like Cache Transforms with memory storage, you can make filters using memory based lookups with millions of records super fast, once everything is loaded.
…and extend it
And of course Kingswaysoft didn’t settle for just building connectors to Dynamics 365. They have a pack called “Productivity Pack” that adds a lot of nice features to SSIS that makes your day a lot easier.
And if you have problems – great support
And if you ever run into problems, their support is great. We have identified buggs and they have fixed the bug and sent us a special deploy just a day or two after.
Flip side
There is always a flip side. I think one is that SSIS isn’t always your most stable product. For this I don’t think Kingswaysoft are to blame but it affects their product experience none the less.
Another obvious flip side is that this requires a skillset that is a bit “off the tracks” even though it isn’t super hard to learn the basics, becoming really proficient with SSIS takes time.
Finally
So, a product as good as this is probably super expensive, right? No. It isn’t. If you are running things from within Visual Studio, then you don’t even need a license, but if you plan to run a migration or so, I certainly recommend one anyway, to get access to support. And buy the Ultimate license to get access to the productivity pack and all the other connectors while you’re at it. It is worth it.
Just to be clear, I am not saying that the other products are bad. I am just saying that once you choose a product, you will start to decend the rabitts hole. If the product isn’t up to speed you will have a couple of options:
Back up and redo with another technology
Patch with another technology
Create some kind of workaround
Neither of these solutions is rather palatable. Hence it is often tempting to choose a product that you know can do the job. And apart from coding, SSIS with Kingswaysoft will very seldom have any issues.
The team member licensing option is something that has been a subject of debate for quite some time. What can it actually be used for and what not? I have heard “experts” suggesting that it be used for integrations but if your read the Dynamics 365 Licensing Guide, appendix A, you can read all about what Team member licensing is and isn’t. Typical scenarios for Team members are:
Read only users
Users that use only slim parts of the system, and not the “cool” First-Party-App features in Sales, Customer Service, Field Service etc.
Users that just track activities
There are some updates to the details of what a team member can and cannot do, and I think the most important is that a team member cannot CUD (Create, Update or Delete) accounts any more.
How urgent is this? Well, for new instances being created, this will be enforced as of April 1 2020. But for existing instances, it will be enforced on July 1 2020. Hence, you still have some time if you have an exiting org.
You can opt-in to early access updates. Goes without saying that you shouldn’t do in your production system, and probably not even in you dev/test environments since as soon as you have you won’t be able to depoy changes. So, having an out-of-ALM-environment where you can test this might be a good idea.
So, the BIG question is, “Are we compliant?”
Well, there is actually a report that you can generate from the Power Platform Admin Center and I have recorded a video below that shows how you can use that and collate the data with pivoting in Excel.
Recent Comments