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

Setting the SQL compatibility level to optimize database performance is just one of those.  This will equip the database with the latest optimization features of the Azure SQL database or SQL server. It is particularly relevant for demonstration databases because the default compatibility level is usually old.

Keys are another consideration. When we define keys to improve performance, we must make note of the time it takes to complete a loop through the subset of records. Different sized subset of records will take different times but the performance deteriorates if the subset of records cannot be located or read efficiently. The best selection of keys will be such that it supports the code that needs to run on the data.  Proper selection of such keys will maximize the performance.

Data access is another. Data that the client needs goes from the database server through the multitenant application to the client. If the data is cached, it is returned from the cache instead of the database and some time and cost can be saved. If the data isn’t cached, it is fetched from the database over the network and then saved in the cache. There might be more than one cache – say a global and a local where the global cache is for all users connected to the multitenant solution and a local cache is tenant-specific.

Connections to the database are yet another performance consideration.  The multitenant solution provider must maintain a connection pooling which can dramatically decrease memory consumption by the multitenant application.  The database connection pooling also simplifies deployment of the multitenant application as a three-tier architecture where the tiers are installed independently. Administrators are no longer required to manually create the SPNs and setup the delegation when the client, application and database server are hosted independently.

Data read/write performance requires significant investment.  Record identifiers and SQL variant columns don’t prevent the use of BULK inserts. Filtering can usually be done with a single predicate. The exceptions in the multitenant application in which the filtering does not involve a single predicate is when the filtering is based on a computed column that requires external logic or there is a second predicate provided.

Adding constraints on a SQL column can also be useful to performance. The name of the constraint is not important if it is not used by another column in the database.

Clients can also be made performant and not just the servers. The hardware of the computer running the client, the choice of the browser, and the network bandwidth and latency between the client and the server are all important to boost the performance of the clients.