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.
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
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.