A note about performance tuning the database server:

 

There is a lot of documentation on performance tuning of a relational database, but this article covers some of those for a multitenant application.

 

Multitenant databases tend to grow rapidly in number of instances. The purchasing model matters significantly for cloud SQL Databases.

There are two purchasing models: 1) Virtual v-core based purchasing model - this model provides a choice between a provisioned compute tier and a serverless compute tier. With the provisioned compute tier, the amount of computer resources is always provisioned for the workload. With the serverless compute tier, the autoscaling of the compute resources is specified instead. With autoscaling, the databases are paused and resumed with charges only for storage during period of inactivity.

2) the database transaction unit-based purchasing model – this model provides bundled compute and storage packages balanced for common workloads. The compute sizes are declared in DTUs for single databases and elastic DTUs for elastic pools. The vCore based model allows us to independently choose compute and storage resources. Customers prefer DTU-based for simple, preconfigured resource options and vCore-based for value flexibility, control, and transparency.

 

Storage costs are calculated differently based on each purchasing model. Storage is included in the price of the DTU. It is possible to add extra storage in the standard and premium tiers of a cloud SQL database.

 

Application characteristics determine both the purchasing model and the performance tuning of existing instances.  Chatty applications make excessive data access operations that are sensitive to network latency. Batching ad-hoc queries or moving queries to stored procedures is an effective remediation in this case. These will reduce the number of data access operations to the database.

Another case where remediation is justified is when databases have an intensive workload that can’t be supported by an entire single machine. Scaling out of the workload is a suitable remediation in this case.

Applications that have suboptimal queries might not respond well to increased compute size. This is easy to spot with such queries in the data access layer and show symptoms such as missing ‘where’ clauses, missing indexes, or outdated statistics. These applications benefit from standard query performance-tuning. Besides query, applications may have inherent data access concurrency issues such as deadlocking and starvation. Reducing roundtrips between the database and the application by caching data on the client side is an effective remediation in this case.

 

Query tuning and usage of hints are standard query optimizer techniques. These apply to databases as well. Tuning queries might reduce aggregate resource demands. The query optimizer sniffs parameters and evaluates the current value of a parameter to determine whether it can generate a more optimal query plan. When this does not work sufficiently, query hints or directives can be specified and the default behavior via parameter sniffing can be overridden.

 

Customers generally find this on a case-by-case basis. In general, they hit capacity limits for individual databases more often. These capacity limits still exist for pooled databases and instance databases but there are two options to solve problems with large databases.

Cross-database sharding is a technique to spread database operations over multiple databases. The data is split on a single dimension into multiple databases. Splitting different functions of the application into different databases is another such technique. As the application becomes busier, the compute sizes for each function can vary because the load is split across multiple machines.

 

Reference: https://1drv.ms/w/s!Ashlm-Nw-wnWhLMfc6pdJbQZ6XiPWA?e=fBoKcN