Sql-server – Performance tuning for multi-tenant database

database-designindexmulti-tenantperformancesql server

The database, MS SQL 2014 Standard Edition, is becoming a bottleneck in our multi-tenant web application. Aware that most performance issues can be solved by making code more efficient, however looking beyond that now.

When load is high, the database seems to grind to a halt. At seemingly random times, it takes a massive slowdown (thinking table locking when one tenant inserts a load of data). Other times, we see deadlocked transactions. In the latest slowdown, a big chunk was deleting single rows from this table with queries like DELETE FROM tableX WHERE ID = x, resulting in a lot of Transaction (Process ID 183) was deadlocked on lock resources with another process and has been chosen as the deadlock victim errors.

Which of the below strategies would you recommend, if any?

Changing clustered indexes from the auto-incrementing int PK to
being tenantID + id. (note all tables have tenantID column and
already a non-clustered index on tenantID):

This would be an attempt to stop page/table locks due to activities from one tenant from affecting all other tenants. This seems recommended practice for multi-tenant DBs. Otherwise if a tenant inserts 4,000 rows or similar this seems to lock the table.
Changing from default
isolation level to Read Committed Snapshot so writes don't block
reads and vice versa
Moving to faster physical disks – currently
on a RAID 10 array of SSDs, looking at the Intel NVMe PCI-e SSDs

We use SQL Mirroring with High Safety Mode as we cannot lose a single booking/row due to a hardware error. Was under the impression all writes are written to disk, and the transaction won't complete till written to disk on the mirror. Networking between the two servers is sub 1ms and no contention. There's plenty RAM in the machine (64GB and doubling to 128GB in a few days), but does host a second multi-tenant DB (~40GB) for a different product.

Best Answer

Not as a counter-point to anything stated in @sp_BlitzErik's answer, but to more fully address one specific question out of the several posed by the O.P.:

Changing clustered indexes from the auto-incrementing int PK to being tenantID + id. (note all tables have tenantID column and already a non-clustered index on tenantID)

Simply stated: Yes, yes, yes, and probably again yes :-). Absolutely cluster on TenantID (first) and then the ID / Identity column. Right now you have the clustered index on just the IDENTITY column, and that values gets copied into the non-clustered index on TenantID, in which case you have two indexes, and the Identity value is in both. Consider the following regarding the current setup:

It is taking up more disk space to have the Identity column duplicated
When you INSERT or DELETE rows, both indexes need to be modified to account for the new / removed rows. That increases the potential for getting deadlocks (and you mention in a comment on @sp_BlitzErik's answer that this is indeed happening).
If it is not obvious, maintaining two indexes takes more time and more transaction log space than maintaining just one.
Given that most queries should (hopefully!) have the TentantID in the WHERE clause, this forces most queries to use the non-clustered index instead of going straight to the clustered index. This is an unnecessary performance hit on most of your queries. Only back-end / maintenance queries (i.e. those that can operate across the Tenants) would use the Identity column only to find rows, and it is highly doubtful that those represent the majority of the queries being processed.

For more details as to why you should proceed with this change, and at least one problem you might run into and how you can get passed it, please see my answer to the following question, also here on DBA.StackExchange:

Composite Primary Key in multi-tenant SQL Server database

Related Solutions

Sql-server – Is this schema usable for multi-tenant clients?How to remove multi-cascade paths

To achieve multi-tennancy you should limit the number of relationships you have to the factor that determines the tennant.

For example, each of Course, Department and Staff have a foreign key to School. However, these child tables are also interrelated. Is it necessary for Staff to have a schoolid? You can get to the school a staff member works for by following the foreign key to Course.

Similarly, you have grand-child tables (SD and DSC) which are relating tables in curious ways. What has "months indicating duration of the course" to do with which staff member is teaching the course? Is it really necessary for DSC to relate to Staff? Also, you appear to have repeating groups defined in your SD and SDC tables. I believe you may have a normalization issue.

By over-relating tables you create cascade cycles. Consider the following ERD as an alternative:

ERD

Here there is only one potential cycle to be concerned with, instead of the several that are in your current model. Fewer potential cycles means less extra work that might need to be done with triggers instead of declarative referential constraints. This keeps your system simpler and more maintainable.

Postgresql – Multi-tenant database constraints

This is what I ended up going with:

create table blog_posts (
    id uuid not null,
    tenant_id uuid not null,
    title text not null,
    content text not null,
    primary key (id)
);

create unique index blog_posts_tenant_id_id_idx on blog_posts (tenant_id, id);

create table comments (
    id uuid not null,
    tenant_id uuid not null,
    blog_post_id uuid not null references blog_posts (id),
    content text not null,
    primary key (id)
);

alter table comments add foreign key (tenant_id, blog_post_id)
    references blog_posts (tenant_id, id)
    on update cascade;

Best Answer

Related Solutions

Sql-server – Is this schema usable for multi-tenant clients?How to remove multi-cascade paths

Postgresql – Multi-tenant database constraints

Related Question