Row Level Security Performance Implications

A conversation had me wondering about performance implications in SQL Server RLS feature, specifically on how to mitigate the performance impact;
TL; DR; ? Since security functions are actually Inline Table Valued Functions (ITVF), if you write them in a decent way the queries will run in the most efficent way possibile, avoiding RBAR processing.

Let’s set the case scene; I’m starting from the ColdRoomTemperatures_Archive table in the WideWorldImporters sample database, why? I already had the DB restored and ready to go, it has a reasonable number of rows and the column ColdRoomSensorNumber it’s a prefect candidate for the example I had in mind.

I copied the data above in a Test DB, doubled the number of rows to get to around 6M rows, and added a couple of indexes:

CREATE UNIQUE CLUSTERED INDEX CX ON Temperatures(ColdRoomTemperatureID)

CREATE INDEX ix_ColdRoomSensorNumber ON Temperatures(ColdRoomSensorNumber) INCLUDE (Temperature)

The query that we’re going to look at is a simple aggregate of the average temperature:

SELECT ColdRoomSensorNumber, AVG(Temperature)
FROM dbo.Temperatures
GROUP BY ColdRoomSensorNumber

It creates the following plan, nice and easy:

Let’s create the users that we’ll be testing the Security policy to, first:

CREATE USER User1 WITHOUT LOGIN
CREATE USER User2 WITHOUT LOGIN
CREATE USER User3 WITHOUT LOGIN
CREATE USER User4 WITHOUT LOGIN

GRANT SHOWPLAN TO User1 --Just so I can get the actual execution plan when executing the query as this user
GRANT SELECT ON Temperatures TO User1
GRANT SELECT ON Temperatures TO User2
GRANT SELECT ON Temperatures TO User3
GRANT SELECT ON Temperatures TO User4

Then, it’s the turn of a security function and a security policy, I’ll start from the Hardcoded one, because I want to leave the best for last:

CREATE FUNCTION [RLSHardCoded]
(@ColdRoomSensorNumber INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
     RETURN
     SELECT
            1 AS [UserAccess]
     WHERE (USER_NAME() = 'User1' AND @ColdRoomSensorNumber = 1)
     OR (USER_NAME() = 'User2' AND @ColdRoomSensorNumber = 2)
     OR (USER_NAME() = 'User3' AND @ColdRoomSensorNumber = 3)
     OR (USER_NAME() = 'User4' AND @ColdRoomSensorNumber = 4)
GO

CREATE SECURITY POLICY SensorPolicyHardCoded
ADD FILTER PREDICATE dbo.[RLSHardCoded](ColdRoomSensorNumber) ON dbo.Temperatures
WITH (STATE = ON)

Now, let’s execute the same query as above and see how things play out:

EXECUTE AS USER = 'User1'
SELECT ColdRoomSensorNumber, AVG(Temperature)
FROM dbo.Temperatures
GROUP BY ColdRoomSensorNumber
REVERT

Hey, nice job optimizer, I was expecting way worse results!
We basically get a tree with a bunch of merge joins, of which in our code only one branch will actually return any rows.
The downside is that our base table will be accessed one time for each predicate, but since we’re seeking in every case it isn’t as dramatic, however, this approach isn’t well maintainable, as we all know that you shouldn’t hardcode things in the code, you silly.

So, let’s take a nicer approach and create a lookup table with some sample data:

CREATE TABLE [dbo].[RLSLookup](
	[Username] [varchar](20) NOT NULL,
	[ID] [int] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[Username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[RLSLookup] ([Username], [ID]) VALUES (N'User1', 1)
GO
INSERT [dbo].[RLSLookup] ([Username], [ID]) VALUES (N'User2', 2)
GO
INSERT [dbo].[RLSLookup] ([Username], [ID]) VALUES (N'User3', 3)
GO
INSERT [dbo].[RLSLookup] ([Username], [ID]) VALUES (N'User4', 4)
GO

Now we can write a decent Security function and policy:

CREATE FUNCTION [RLSLookupFun] (@ColdRoomSensorNumber INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
     RETURN
     SELECT
            1 AS [UserAccess]
     FROM   [dbo].[RLSLookup]
     WHERE  [Username] = USER_NAME()
            AND [ID] = @ColdRoomSensorNumber;

GO

CREATE SECURITY POLICY SensorPolicyLookup
ADD FILTER PREDICATE dbo.[RLSLookupFun] (ColdRoomSensorNumber) ON dbo.Temperatures
WITH (STATE = ON)

and execute the same exact query as above:

Much nicer!
We get a nested loop join, in which we’re actually looping only once, since the outer table result is used only as a seek predicate for the inner table, on which we already have a pret-a-porter index, which is as close as possibile to adding a predicate on the original query.
As the number of possibile users (or single users accessing multiple data) this approach gets exponentially better than hard-coding values in multiple ways.

Important note!

While testing out the code for this post, I realized that one of my pillars when dealing with performance “Keep datatypes consistent” is more relevant than ever.

Quick example:
Let’s simply take the same hardcoded function as above, and change only the input data type from INT to TINYINT; it’s not even that big of a deal you would say, uh? (keep in mind, the ColdRoomSensorNumber column in the base table is INT)

DROP SECURITY POLICY SensorPolicyHardCoded;
GO

ALTER FUNCTION [RLSHardCoded]
(@ColdRoomSensorNumber TINYINT)
RETURNS TABLE
WITH SCHEMABINDING
AS
     RETURN
     SELECT
            1 AS [UserAccess]
     WHERE (USER_NAME() = 'User1' AND @ColdRoomSensorNumber = 1)
     OR (USER_NAME() = 'User2' AND @ColdRoomSensorNumber = 2)
     OR (USER_NAME() = 'User3' AND @ColdRoomSensorNumber = 3)
     OR (USER_NAME() = 'User4' AND @ColdRoomSensorNumber = 4);

GO

CREATE SECURITY POLICY SensorPolicyHardCoded
ADD FILTER PREDICATE dbo.[RLSHardCoded](ColdRoomSensorNumber) ON dbo.Temperatures
WITH (STATE = ON)
GO

Then run again the same select query, with these results:

A totally different plan, with a totally different and wasteful approach!
In this case, the optimizer isn’t able to use the existing index, so it basically scans the whole table and applies the predicate of the Security Function row by row, parallelizing the work and consuming a lot of CPU time too.
In all of this:

• You don’t get any warning
• You don’t get any complicit conversion message in the operator “mouse-hover” details
• You only see that there is an implicit conversion going on by exploring the properties of the “Compute Scalar” node (or by using SentryOne Plan Explorer)

Another reason to design your objects carefully, as in this case the optimizer won’t even help you with little yellow triangles, only awful performances and wasted CPU Cycles.
As someone says, “Love with your heart, use your head for everything else!”