SQL Server Multi-User Databases and Parameter Definition

Question

I have a multi-tenant database in SQL Server 2012 where each tenant row is identified by the tenant_id column (also called Shared Database, Shared Schema ). Some tenants, especially newer ones, have very few lines, while others have many.

The SQL Server Query Optimizer typically builds a query plan based on the parameters provided during its first execution, and then reuses the plan for all future queries, even if different parameters are provided. This is called the sniffing option .

The problem we have with our database is that SQL Server sometimes builds these plans based on parameters pointing to a smaller tenant that is great for that tenant, but then when it reuses the cached plan for a larger tenant , it does not work catastrophically (usually from time to time). Usually we only learn about this situation when one of our larger tenants contacts us about timeout errors, then we must log in and manually clear all request plans in order to fix it.

There is a hint that you can use to prevent SQL Server from caching query plans ( OPTIMIZE FOR UNKNOWN ), but this leads to some additional overhead because the query plan is regenerated each time the query is called. Another problem is that we are using the Entity Framework, which does not have the ability to provide the OPTIMIZE FOR UNKNOWN hint for queries.

So, the question is, what is the best practice for multi-tenant databases regarding sniffing options? Is there a way to disable the sniffing option for the entire database without pointing it to every request? If so, is that even the best approach? Should I break the data in some other way? Is there any other approach that I don't think about?

Answer 1

I had similar problems, and I successfully solved this by passing my parameters as follows:

 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 Id=@xAPBatchId.....etc. 

in other words, creating a local variable based on 1-1 for each parameter passed in and then only refer to these new variables in the SP logic. I probably lose sight of some of the optimization that SQL Server could do for me, but most importantly, I miss the truly terrible performance that I get when parafit foams.

In your case, perhaps you could try to do this only for the multi-tenant identifier (which, I suppose, is a parameter for all SPs?), And let the SQL server optimize the rest of the parameters, if possible.