Azure SQL Query Performance Degradation when Row Level Security is ON

Question

In Azure SQL database, the same query is completed in 4 seconds when Row Level Security is active, and in 1 second when Row Level Security is disabled.

When I compared the execution plans of the queries, I found that the optimizer could not use parallelism when Row Level Security was active. You can see the below screenshot.

My questions are;

1- Is it technically possible for the optimizer to use parallelism when Row Level Security is active?

2- If possible, what should I do to make the optimizer use parallelism even when Row Level Security is active?

Answer 1

This is a known issue documented here. The culprit is maybe the way the function used by the security policy was built. To avoid this, try to use simple and deterministic filter predicates that do not involve complex calculations or joins with other tables.

A workaround may be to try using MAXDOP query hint (OPTION(MAXDOP 2)) but the article suggested that method and others did not work. Hope this method work in your scenario or one of the workarounds Microsoft Support tried.

• Update the statistics and rebuild the indexes.
• Use Recompile or grant memory hint
• Use the latest compatibility model
• Clear buffer pool.
• Clear procedure cache.