Thursday, June 20, 2019

SQL interceptors

SQL interceptors are a way to apply filtering by tenant for securing multi-tenant applications. Here is a good read on it

However, you need to be careful with it since they modify your query at runtime. 
Specifically, DbExpressionBuilder.Bind(databaseExpression) in the interceptor causes a random variable to be created which creates a random query text for the same query on every reinitialize which is generally a recycle on the VM where the application is running.

This puts unnecessary unnecessary pressure on QDS (Query Data Store).
Also, if you force a query plan for a specific query hash, a new query hash will be generated the next time so the forced plan will not work.

To fix this, make sure to bind it with a specific variable name like DbExpressionBuilder.BindAs(databaseExpression, "Filter")

No comments:

Post a Comment