0

I have come across a strange scenario of performance in data warehouse where we are maintaining data for multi tenants. Same query is running in less time at Test Environment and Its taking long to execute at PROD.

I have tried these things:

  1. When RLS removed. Query runs fine (taking 25sec)

  2. Hard Coding RLS predicate function runs fine too. Only when RLS function is applied to filter tenant data, it executes for 10min.

I think RLS predicate function is doing something on the backed which I can't figure out.

ALTER FUNCTION [dbo].[fn_RowLevelSecurity] (@FilterColumnName sysname)
RETURNS TABLE
WITH SCHEMABINDING
as
RETURN SELECT 1 as fn_SecureTenantData
where @FilterColumnName = user_name();

I have tried without where clause it runs fine but took data for all tenants.

user3786482
  • 1
  • 1
  • Have you investigated the execution plan and the statistics IO through statisticsparser.com? – dfundako Jul 27 '18 at 16:24
  • Yes, tried both execution plan doesn't help me a lot although statistics IO stats are fine when RLS disabled (when security policy is removed) but one table is behaving abnormally when RLS applied others are fine and this table taking 95% of logical reads. – user3786482 Jul 27 '18 at 17:02

0 Answers0