Multi-tenant SQL Server databases and parameter sniffing

Question

I have a multi-tenant database in SQL Server 2012 where each tenant's rows are identified by a tenant_id column (aka the Shared Database, Shared Schema approach). Some tenants, particularly the newer ones, have very few rows, while others have many.

SQL Server's query optimizer normally builds a query plan based on the parameters provided during its first execution, then re-uses this plan for all future queries even if different parameters are provided. This is known as parameter sniffing.

The problem we have with our database is that SQL Server sometimes builds these plans based on parameters that point to a smaller tenant, which works fine for that tenant, but then when it reapplies the cached plan to a larger tenant it fails catastrophically (usually timing out, in fact). Typically we find out about this situation only when one of our larger tenants contacts us about experiencing time-out errors, then we have to get into the system and manually flush all the query plans to correct it.

There is a query hint you can use to prevent SQL Server from caching query plans (OPTIMIZE FOR UNKNOWN) but this results in some extra overhead since the query plan is being regenerated every time the query is called. An additional problem is that we're using Entity Framework which offers no ability to specify the OPTIMIZE FOR UNKNOWN hint on queries.

So the question is -- what is the best practice for multi-tenant databases with regard to parameter sniffing? Is there a way to disable parameter sniffing database-wide without having to specify it on every query? If so, is that even the best approach? Should I be partitioning the data in some other way? Is there some other approach I'm not thinking of?

Christhood

AFAIK, you need to be using Stored Procedures to fix this. Unless there's some feature of EF that can get you around this. – Brogdon

OPTIMIZE FOR UNKNOWN does not have the behavior you describe. You are confusing it with OPTION (RECOMPILE). If you are on Enterprise Edition you can look at plan guides to provide the hint. Or there is a trace flag (4136) to disable parameter sniffing entirely but that applies to the instance not database. – Laroy

Answer 1

I have had similar problems, and have solved it successfully by passing my parameters in like this:

CREATE PROCEDURE [dbo].[InsertAPCheck]
@APBatchID  int = Null,
@BankAccountID  int = Null
AS
  /* copy parameters to temporary variables */
  SELECT @xAPBatchId = APBatchId, @xBankAccountID = @BankAccountID
 .
 .
 /* now run the meat of your logic using the temp variables */
 SELECT * FROM myTable where [email protected].

in other words, creating a local variable on a 1-1 basis for each parameter passed in and then only referencing those new variables within the logic of the SP. I am probably missing out on some optimization that SQL Server could do for me, but most importantly I miss out on the truly horrid performance I get when the param eter sniffing kicks in.

In your case, perhaps you could try doing this just for the multi-tenant id (which I assume is a parameter to all SP's?), and let SQL server optimize the rest of the parameters if it can.

Subarid

This is essentially what the OPTIMIZE FOR UNKNOWN query hint does. Unfortunately I can't use either approach with Entity Framework because I can't modify the query that's being generated. – Christhood

Answer 2

For dynamic SQL, such as the SQL that Entity Framework generates, inject a comment into the command text that contains an identifier for the current tenant. This essentially partitions SQL's execution plan cache by tenant, keeping execution plans isolated to the tenant but allowing them to be reused by the same tenant.

To inject a comment into the command text, you can subclass/implement DbConnection/IDbConnection and DbCommand/IDbCommand and apply the decorator pattern. Calls to DbCommand/IDbCommand.Execute* can append a commented tenant ID before invoking the inner method and then remove the comment after returning. Initialize Entity Framework, or whatever ORM you use, with the decorated connection.

If you have lots of tenants, it might make sense to partition the plan cache by tenant size category. Otherwise, you'll effectively do the same as OPTION (RECOMPILE) because plans will expire from the cache before they're reused.

Juvenile