Balancing resources in SQL Server

Level: 3 where 1 is noob and 5 is totally awesome
System: SQL Server

As software developer I believe in decoupling, and I'm certain that decoupling is just as important in database design as well. I really like having things in minimum chunks, and keeping them independent as possible. Independence and decoupling equals scalability. This post is based on a professional experience, which supports my belief.

At my work we are developing a billing platform, and this platform has several clients. Each client use our platform to bill a certain amount of customers. So this set the scene.

Some time ago, we had one database for each client in one instance, which is a easy pattern. But in SQL Server it can easily be an expensive anti-pattern, and it is certainly a pattern which is my last choice. The problem with this pattern, it is only effective, when the databases is equal regarding size and usage.

The buffer pool

The explanation is, there is only one buffer pool pr. instance. SQL Server is an in-memory database, but in most cases, there is not enough memory for a whole database in the memory. And if there is more databases in an instance, then it is almost certain there no room in the memory for all the bases. To solve this issue, SQL server has the buffer pool, which is a cache. When querying, SQL Server will get the result from the buffer pool. If it not available in the buffer pool, it reads from the storage to the buffer pool. If there if no room in the buffer pool, it will flush some of the buffer pool, to make room for the requested data. Everything has to go through the buffer pool.

The process of getting data from storage to the bufferpool, generates PAGEIOLATCH waits. A high count of PAGEIOLATCH waits can be a sign of much data is being loaded from disk to the buffer pool.

Let us say, we have 3 databases in one instance. They respectively supports 25.000, 150.000 and 300.000 customers. The sizes of the databases reflects the number of customer, because more customers generates more data. What will happen, is the database with the most usage, will push out the data from the other bases, out of the buffer pool. This will give the lesser bases more read from the disk, producing more PAGEIOLATCH wait, adding more latency to the lesser bases. Actually, the smallest databases will suffer the most.

The poor solution  

I'm tempted to call this, the poormans solution, but it can be very expensive. The solution is to extend the buffer pool, with more memory. But there is a physical and economical limit to this. SQL Server 2014 has a new feature called buffer pool extensions, where it is possible to extends the buffer with storage(preferable SSD drives). I guess extending the buffer pool, will only cache more of the database with most uses. Besides, we have tried this feature, and have yet to see some great results. I would really like to hear, if some has used this feature with success.

The better solution

A easy way to balance databases resourcing over an instance, is by using the resource governor feature in sql server. It works excellent, but it is really a doubled edged sword. Used wrong it can really downgrade performance. It would also require some nursing, which I prefer to keep to a minimum.

The best solution

I find the best solution is to have more instances. If I have 3 clients, I will assign an instance to each, make it thier domain. Each instance, should have hard assigned a certain amount of memory, and their databases would never be able to inflict each others performance. 

This was the solution we did end up with, and it gave us better performance overall.