This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
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.
[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]
- 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
insert into LogData ([InsertID], [ID]) values(@N,@N)
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))
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @groupcode IN (
WHERE dbo.group_user.username =
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))
CREATE SECURITY POLICY dbo.[LogData_Check]
ADD FILTER PREDICATE dbo.[fn_securitypredicate]([groupcode]) ON dbo.Logdata
WITH (STATE = ON, SCHEMABINDING = ON)
- 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])