We have implemented SQL Row-level security using SESSION_CONTEXT. From an architecture standpoint, this is a beautiful paradigm that allows our application to be clueless to the fact that only 1 tenant's data is being returned based on the logged in user.
However, we've come across some performance issues related to pre-filtering joins that result in full table scans or index scans. When I explicitly add FORCESEEK, the queries run significantly faster but SQL will never choose a seek on its own and it's my understanding that RLS is to blame for this. I verified that parameter sniffing and cached statistics are not to blame by forcing a recompute every time.
Have you had this happen before? Is there a way around it with RLS? Our backup plan is to scrap RLS completely and move to an Azure SQL elastic pool and horizontal sharding based on customer id.
Thanks so much for any guidance you can provide.
UPDATE: Below are the query execution plans for: 1) With RLS enabled on all 3 tables 2) With RLS enabled on all 3 tables + FORCESEEK option 3) With RLS disabled