This is a continuation of an article that describes operational considerations for hosting solutions on Azure public cloud.    

There are several references to best practices throughout the series of articles we wrote from the documentation for the Azure Public Cloud. The previous article focused on the antipatterns to avoid, specifically the improper instantiation antipattern. This one focuses on busy database antipattern. 

Database stores data but frequently some code is frequently used with calculations for that data. These are stored in the database as stored procedures and triggers. There is a lot of advantages to running code local to the data which avoids the transmission to a client application for processing. But the overuse of this feature can hurt performance due to the server spending more time processing, rather than accepting new client requests and fetching data. A database is also a shared resource, and it might deny resources to other requests when one of them is using a lot for computations. Runtime costs might shoot up if the database is metered. A database may have finite capacity to scale up. Compute resources are better suitable for hosting complicated logic while storage products are more customized for large disk space. The antipattern occurs when the database is used to host a service rather than a repository or it is used to format the data, manipulate data, or perform complex calculations.  Developers trying to overcompensate for the extraneous fetching antipattern often write complex queries that take significantly longer to run but produce a small amount of data. Stored procedures are used to encapsulate business logic because they are considered easier to maintain and update. They lead to this antipattern. 

This antipattern can be fixed in one of several ways. First the processing can be moved out of the database into an Azure Function or some application tier. As long as the database is confined to data access operations using only the capabilities that the database is optimized and will not manifest this antipattern. Queries can be simplified to fetching the data with a proper select statement that merely retrieves the data with the help of joins. The application then uses the .NET framework APIs to run standard query operators. 

Database tuning is an important routine for many organizations. The introduction of long running queries and stored procedures often goes against the benefits of a tuned database. If the processing is already under the control of the database tuning techniques, then they should not be moved.  

Avoiding unnecessary data transfer solves both this antipattern as well as chatty I/O antipattern. When the processing is moved to the application tier, it provides the opportunity to scale out rather than require the database to scale up. 

Detection of this antipattern is easier with the monitoring tools and the built-in supportability features of the database. If the database activity reveals significant processing and very low data emission, it is likely that this antipattern is manifesting. 

Examine the work performed by the database in terms of transaction units, number of queries processed and the data throughput which can be narrowed down by callers and this may reveal just the database objects that are likely to be causing this antipattern 

Finally, periodic assessments must be performed with the database.