Blog Post

Azure Database Support Blog
2 MIN READ

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

Jose_Manuel_Jurado's avatar
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.

 

 

Enjoy!

 

 

 

 

Updated Dec 02, 2019
Version 3.0