database design - Should MultiTenant Tables Include TenantID in Primary and Foreign Keys? -

database design - Should MultiTenant Tables Include TenantID in Primary and Foreign Keys? -

for multi-tenant single shared database should tenantid field included in primary key , clustered index? or adding additional index on tenantid performant?

we’re running performance issues on production scheme index clustered index on primary key.

all sql select statements start tenantid in linq entities statements such as

invoiceitems.tenantid = thecurrenttenantid order invoicedate current schema

tenants (tenantid uniqueidentifier primary key, tenantname) foreign keys (tenantid) indexes(clustered on tenantid)

customers (tenantid uniqueidentifier, customerid uniqueidentifier primary key, customername varchar(50)) foreign keys (tenantid, customerid) indexes (clustered on customerid)

invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) foreign keys (tenantid, billcustomerid, shipcustomerid) indexes (clustered on invoiceid)

invoiceitems (tenantid uniqueidentifier, invoiceitemid uniqueidentifier primarykey, invoiceid uniqueidentifier, lineitemorder int) foreign keys (tenantid, invoiceid) indexes (clustered on invoiceitemid)

sqlazure requires each table has clustered index it's on primarykeyid since that’s default. right index on each table. there various foreign keys in tables throughout scheme , none of foreign key table fields indexed.

we're trying resolve performance issues right , wondering best clustered index , if other indexes might helpful. we're hoping don't have alter existing clustered index unless absolutely have willing so. in sqlazure afaik cannot adjust clustered index in existing table - have create new table desired clustered index , insert records old table new table (and handle of foreign key constraints , other table dependencies).

all sql select statements start tenantid in linq entities statements.

invoiceitems.tenantid = thecurrenttenantid order invoicedate

some sql select statements have order - have other bring together status values when bringing in kid tables like

invoiceitems.tenantid = thecurrenttenantid , invoice.invoiceid = invoiceitems.invoiceid order invoicedate

here few ideas (we're open others besides this) - which of these best , why?

primary key index options

to speed access tenant's records

option 1 - add together non-clustered index on tenantid

invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) foreign keys (tenantid, billcustomerid, shipcustomerid) indexes (clustered on invoiceid, non-clustered on tenantid)

option 2 - alter primary key primaryid tenantid + primaryid , alter clustered index tenantid + primaryid.

invoices (tenantid uniqueidentifier primary key, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) foreign keys (tenantid, billcustomerid, shipcustomerid) indexes (clustered on tenantid + invoiceid)

foreign key index options

to speed joins

option 3 - add together non-clustered indexes on foreign key fields on foreignkeyid.

invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) foreign keys (tenantid, billcustomerid, shipcustomerid) indexes (clustered on invoiceid, non-clustered on billcustomerid, non-clustered on shipcustomerid)

option 4 - alter foreign keys foreignkeyid tenantid + foreignkeyid , add together index on tenantid + foreignkeyid

invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) foreign keys (tenantid, tenantid + billcustomerid, tenantid + shipcustomerid) indexes (clustered on invoiceid, non-clustered on tenantid + billcustomerid, non-clustered on tenantid + shipcustomerid)

sql select optimization index options

to speed used queries select fields invoices tenantid = value order invoicedate

option 5 - add together indexes on used sort order fields within each table besides tenantid.

invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) foreign keys (tenantid, billcustomerid, shipcustomerid) indexes (clustered on invoiceid, non-clustered on invoicedate)

option 6 - add together indexes on tenantid + “most used sort order field” within each table , add together non-clustered index on tenantid + “most used sort order field”

invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime) foreign keys (tenantid, billcustomerid, shipcustomerid) indexes (clustered on invoiceid, non-clustered on tenantid + invoicedate)

it seems you've given lots of thought. regardless of or else says way know sure measure yourself. in case becomes less of sql azure question , more of general sql server query optimisation question.

for situation there couple of tips started. you're using linq, don't have direct access actual queries beingness run in sql. might think know query should like, depending on version of ef you're using, can create interesting decisions on how construction query. find out queries beingness run you'll need utilize sql profiler. unfortunately sql profiler doesn't work against sql azure, you'll want re-create of db on local server somewhere , run application pointing @ local. export info tier application , related import in sql server management studio (ssms) useful this.

with actual queries can run them in ssms against database in azure execution plan. can create alter indexes, run query 1 time again , compare plans. if don't want mess main development db can create re-create pretty create database xxx re-create of yyyy.

don't tempted optimisations on local db. sql azure has different performance outline on premise sql installs.

with of said, if all of queries always going contain tenant id, yes expect including first part of clustered index improve query performance. other indexes, i'm not sure, i'd measure, measure, measure. remember indexes don't come free, every 1 create impacts on write performance , size of db, wouldn't go nuts , index everything.

finally, don't worry using guids pks, if db gets big plenty need federate tenant id (which construction looks handle quite nicely) identity columns stop becoming option.

database-design azure indexing sql-azure multi-tenant