Lesson Learned #114: Could Row Level Security changes the execution plan of a query?

Microsoft

Dec 02, 2019

Today, I worked on a service request that remember me the impact that we could have using row level security afterwards that our databases has been developed.

A customer reported a performance issue after implementing the row level security.

Current situation

Our customer have the following design:

A table where we have the LogData, that contains data and group code.

CREATE TABLE [dbo].[LogData](
    [InsertID] [bigint] NOT NULL,
    [ID] [varchar](100) NOT NULL,
    [groupcode] [varchar](255) NOT NULL,
CONSTRAINT [PK_LogData_current] PRIMARY KEY CLUSTERED
([ID] ASC) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE dbo.[LogData] ADD DEFAULT ('G1') FOR [groupcode]
GO

create index LogData1 on [dbo].[LogData] ([InsertiD])

I have a table relation with group_user that contains per user that groups that belongs to

create table dbo.group_user ([groupcode] varchar(255),username varchar(128))

INSERT INTO dbo.group_user VALUES('G1',1)

Insert some rows in the table LogData to have some data to play

DECLARE @n as integer = 0
WHILE(@n<=100000)
begin
SET @n=@n+1
insert into LogData ([InsertID], [ID]) values(@N,@N)
end

Query Execution before adding row level security

When I run the query: SELECT MAX(INSERTID) FROM LogData the execution plan looks like normal based on the index that I have:

Row Level Security Implementation

I created the function:

CREATE FUNCTION dbo.[fn_securitypredicate](@groupcode AS varchar(255))
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @groupcode IN (
SELECT dbo.group_user.[groupcode]
FROM dbo.group_user
WHERE dbo.group_user.username =
CASE
       WHEN is_member('G1') = 1 THEN CAST('G1' AS nvarchar(128))
       WHEN is_member('G2') = 1 THEN CAST('G2' As nvarchar(128))
       WHEN is_member('G3') = 1 THEN CAST('G3' As nvarchar(128))
       ELSE user_name()
END);

I created the security policy:

CREATE SECURITY POLICY dbo.[LogData_Check]
ADD FILTER PREDICATE dbo.[fn_securitypredicate]([groupcode]) ON dbo.Logdata
WITH (STATE = ON, SCHEMABINDING = ON)
GO

When I executed the query SELECT MAX(INSERTID) FROM LogData , I found a different execution plan.
- I found that SQL Server will execute a Clustered Index Scan instead of Index Scan. Why? because it needs to access the groupcode to check the security predicate.

Workaround to have the same execution plan

Modify or create an index that contains the groupcode on it, either adding as a sorted column or included column.

create index LogData2 on [dbo].LogData ([InsertiD],[groupcode])
create index LogData3 on [dbo].LogData ([InsertiD]) include ([groupcode])

When I executed the query SELECT MAX(INSERTID) FROM LogData , I found a very similar execution plan to the first one using an index scan, because the index contains the field to check the security function.