What are the performance impact will happen when we implement RLS for azure sql database

Question

Hi All,

I am new to SQL server RLS concept please help me out.

When we implement RLS (to handle multi tenancy) on azure SQL transaction DB what are performance impact will happen.

Answer 1

On multi-tenant scenarios I have seen the worst impact that RLS can have (filtering by tenantId/customerid). Execution plan of all existing queries will change, and you may see a good number of indexes will no longer be used since you are adding a new filtering. So, the performance impact you may see is slow queries or scans over tables (timeouts on queries that scan big tables) until you make adjustment to indexes.

My suggestion, you really might be better off to not use RLS and move to an Azure SQL elastic pool and horizontal sharding based on tenant iD or customer id.

Answer 2

In difference to Alberto, I don't have any direct experience of the RLS feature. Then again, I have been involved with severak system that have implemented their own row-level security. (For instance, because these systems are older than SQL 2016, the version where RLS first appeared.)

If you just slap on RLS with any further thinking, it is quite likely that you will see queries degrade of the simple reason that you are changing the queies, and you may not have appropriate indexes.

But this is not the same as RLS is a performance hog. RLS is basically only an aid in application development for something you could do yourself. If you implement RLS properly - only your own, or using the built-in stuff - the overhead should not be dramatic.

I don't have any direct experience of Elastic Pool, but I have a client that has a multi-tenant app, and they have settled for elastic pool. One advantage with having one database per tenant, is that each tenant can get its own plans. A plan that is good for a small tenant, may not be good for the tenant with 25% of the database. And vice versa.

Answer 3

Hi @Arunkumar Thank you for posting your question on Microsoft Q&A and for using Azure services.

My understanding is that you are asking the performance impact of having row-level security in Azure SQL database.

In addition to @Alberto Morillo and @Erland Sommarskog 's answer in the sense that depending on the complexity of the function used to implement the security policy there might be a performance impact. But that is no different than having a WHERE clause in the SELECT statement. It might have a performance impact or not, depending on the complexity of the clause itself.
For best practices see documentation Row-Level Security
It's highly recommended to create a separate schema for the RLS objects: predicate functions, and security policies. This helps to separate the permissions that are required on these special objects from the target tables.
Avoid recursion in predicate functions wherever possible to avoid performance degradation.
Avoid using excessive table joins in predicate functions to maximize performance.

Regards,
Oury