In my SQL Server 2012 multi-tenant database, each tenant's records are identifiable by a
column (aka the Shared Schema and Database approach). While some renters, especially the more recent ones, have few rows, others have numerous.
The query optimizer in SQL Server typically creates a query plan based on the inputs made during its first execution and reuses it for all subsequent queries, even if new inputs are made. It is referred to as sniffing for parameters.
Our database's issue is that SQL Server sometimes creates these plans using parameters that refer to a smaller tenancy, which is acceptable for that tenant, but catastrophically fails when it reapplies the cached plan to a bigger tenant (usually timing out, in fact). Usually, we are only made aware of this issue when one of our bigger tenants contacts us to report time-out difficulties; in order to fix it, we then need to manually flush all the query plans from the system.
You may use the query hint (
OPTIMIZE FOR UNKNOWN) to prevent SQL Server from caching query plans, but doing so adds additional cost since the query plan must be produced each time the query is executed. Another issue is that Entity Framework, which we use, does not allow for the specification of the
OPTIMIZE FOR UNKNOWN
hints for questions
So the issue is: What is the best parameter sniffing technique for multi-tenant databases? Is it possible to prevent parameter sniffing throughout the whole database without having to define it for each individual query? Then, is that even the best course of action? Should I divide the data in a different way? Is there an other strategy that I'm not considering?
I've encountered similar issues before and have effectively overcome them by entering my settings 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.
the logic of the SP would then only make use of the new local variables that were created on a 1-1 basis for each argument that was handed in. Most significantly, I lose out on the genuinely terrible performance I get when the parameter sniffing engages. I am probably losing out on some improvement that SQL Server could execute for me.
In your situation, you may want to try doing this just for the multi-tenant id (which, based on my understanding, is a parameter to all SPs?) and let SQL Server improve the other parameters if it can.