title: Multi-tenant SQL Server databases and parameter sniffing
body: <p>I have a multi-tenant database in SQL Server 2012 where each tenant's rows are identified by a <code>tenant_id</code> column (aka the <a href="http://msdn.microsoft.com/en-us/library/aa479086.aspx" rel="noreferrer">Shared Database, Shared Schema</a> approach). Some tenants, particularly the newer ones, have very few rows, while others have many.</p>
  
  <p>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 <a href="http://www.sommarskog.se/query-plan-mysteries.html" rel="noreferrer">parameter sniffing</a>.</p>
  
  <p>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.</p>
  
  <p>There is a query hint you can use to prevent SQL Server from caching query plans (<a href="http://www.benjaminnevarez.com/2010/06/how-optimize-for-unknown-works/" rel="noreferrer"><code>OPTIMIZE FOR UNKNOWN</code></a>) 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 <code>OPTIMIZE FOR UNKNOWN</code> hint on queries.</p>
  
  <p>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?</p>