How to avoid throttling SQL Azure database with NHibernate
Lately I had one weird experience on with running NHibernate on Windows Azure and I think it’s worth to share with you. NHibernate is matured industry-level ORM that is proven itself over years. Although it is not so easy to use as Entity Framework it is still my favorite due to flexibility and wide databases support it provides. In this posting I will show you one important trick with enum mappings. When ignored you may put huge unnecessary load to your database.
We have one system launched to public where NHibernate is used as ORM. The system runs on Windows Azure (web roles and one worker role). One nice day out worker role that runs bunch of services ran into troubles with database. From logs we found out that throttling protection was activated on SQL Azure.
It’s possible to switch to some bigger database package on SQL Azure but as we don’t have this much data yet and the site is not heavy load I got very interested in what is going on. Why is our service shooting something expensive to database?
Problem with enum mapping
When analyzing the problem I found out the problem. Take a look at the following NHibernate mapping:
<property name="AlertType" column="alert_type" type="int" />
The problem is in this mapping – it defines enumerator as integer. As enums and ints can be casted from one to another, NHibernate doesn’t complain about this mapping but internally it doesn’t like it. This mapping screws something up in change tracking and NHibernate thinks that entity is changed. Yes, it loads entity and finds then out it is changed. This means that when session is flushed a load of update commands are executed on database and as the result SQL Azure throttling protection may activate.
Mapping enums correctly
When mapping enums we have to specify also their type. This mapping here is correct:
<property name="AlertType" column="alert_type">
<type name="MyProject.MyEnum, MyProject"></type>
</property>
After fixing all enum mappings to use correct enum types we calmed down NHibernate and this far we have had no problems with SQL Azure throttling anymore.
Thanks for the post. We have a similar set up and are wondering if you used tools beyond the logs to diagnose the problem?
As we discovered the problem through throttling protection, of course we found it out hard way :)
I ran some services code on Visual Studio and as NH is logging SQL to debug Windows I saw updates going to database even when there is no reason to update database. From SQL Azure query logs I saw also that there are way too much updates.
By default NHibernate handles enums as strings, so they are readable. When you specified the column without telling NHibernate how to handle Enums, then yes you’re going to run into change tracking problems.