Abstract:

Offloading processing to a database server can cause it to spend a significant proportion of time running code, rather than responding to requests to store and retrieve data

Source:

https://docs.microsoft.com/en-us/azure/architecture/antipatterns/busy-database/

Summary

Modern database systems have evolved to run code, which can be efficient since processing is colocated with the data (typically on the same machine).  For fast computations, this might be ideal, but this leads to problems with long-running processes.  Since the database is a shared resource, this can easily become a bottleneck.  With this anti-pattern, the database becomes viewed as both a service and repository.  This article proposes moving certain types of processing out of the database and into a separate service, and analyzing the impact of the change by monitoring CPU time and DTU's (database throughput unit, which measures the performance of a mix of basic database operations https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu).  They provide some basic examples of how to accomplish this, and do note that fixing this anti-pattern can cause other unintended consequences.

Extending the Solution

This is an interesting approach, and could easily be built off of with a lot more detail.  For example, using experiments to find the thresholds under which processing performance starts to degrade as processing is moved off the database would be helpful(recall that we are attempting to increase overall database availability, not increase the processing time of a particular stored procedure call).  Network latency for the long-running operation will increase, but that could be compared to overall request times for other users of the system under varying types of loads.  Consider that the best approach to offloading the processing is more a function of business priorities rather than just being some form of absolute value, so a more in-depth discussion of potential refactor strategies based on business need might have value.  For example, identifying and measuring types of processing/calculation refactoring that:

• maximize overall database availability
• mimimize refactor cost (e.g. certain types of refactoring might be extremely time consuming for very little actual benefit) 

relevant content:

- ensure the database only does 

Problem description

Many database systems can run code. Examples include stored procedures and triggers. Often, it's more efficient to perform this processing close to the data, rather than transmitting the data to a client application for processing. However, overusing these features can hurt performance, for several reasons:

• The database server may spend too much time processing, rather than accepting new client requests and fetching data.
• A database is usually a shared resource, so it can become a bottleneck during periods of high use.
• Runtime costs may be excessive if the data store is metered. That's particularly true of managed database services. For example, Azure SQL Database charges for Database Transaction Units (DTUs).
• Databases have finite capacity to scale up, and it's not trivial to scale a database horizontally. Therefore, it may be better to move processing into a compute resource, such as a VM or App Service app, that can easily scale out.

This antipattern typically occurs because:

• The database is viewed as a service rather than a repository. An application might use the database server to format data (for example, converting to XML), manipulate string data, or perform complex calculations.
• Developers try to write queries whose results can be displayed directly to users. For example a query might combine fields, or format dates, times, and currency according to locale.
• Developers are trying to correct the Extraneous Fetching antipattern by pushing computations to the database.
• Stored procedures are used to encapsulate business logic, perhaps because they are considered easier to maintain and update.
•