SQL interceptors

SQL interceptors are a way to apply filtering by tenant for securing multi-tenant applications. Here is a good read on it http://xabikos.com/2014/11/18/Create-a-multitenant-application-with-Entity-Framework-Code-First-Part-2/

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")

Test SQL connection from a windows machine

I wanted to test SQL connection from a Windows machine which didn't have any SQL tools installed. Found a cool way to do so as mentioned below:

1. Create a blank udl file e.g. test.udl
2. Double click it
3. Enter the connection properties and click Test Connection. Sweet!

 

SQL / SSRS - Selecting 95% and 99% (percentile) values

I was trying to generate a SSRS report where I wanted to display the 95% and 99% (percentile) values to remove the outliers.
Unfortunately there isn't a percentile function in SSRS unlike excel.

So I had to use SQL to derive these values.

To derive 95% value, select top 5% by ordering the counters of the instance in desc order. Now order these 5% in ascending order and select top 1
Select @95P = (select top 1 t.coulmnname from (select top 5 percent coulmnname from tablename order by columnname desc)t order by t.coulmnname asc)

To derive 99% value, select top 1% by ordering the counters of the instance in desc order. Now order these 1% in ascending order and select top 1
Select @99P = (select top 1 t.coulmnname from (select top 1 percent coulmnname from tablename order by columnname desc)t order by t.coulmnname asc)