SQL Server 2016 with Row Level Security - Addressing the Bottleneck

Question

I'm developing with Microsoft SQL Server 2016, and are currently facing a major performance drop when adding Row Level Security (RLS) to my database. I already think I've found the issue, which is Mr Query Optimizer who doesn't like my non deterministic filtering function very much. My question is if anyone had any experience with RLS, filtering functions, and optimizing a case like this. - Can indexing, a more clever RLS filtering function, etc improve the performance?

I use RLS to filter the returned/available rows from a query based on a filter function. Below I setup a function to filter rows based on a variable from the SESSION_CONTEXT() function. So it's much like adding a filter to the WHERE clause (except that it doesn't optimize the same, and this is way easier to apply to an existing huge application, since it's done on the database level).

Please note that the script below, and the tests, are a very simplistic version of the actual thing, but it does demonstrate a performance drop when filtering is applied. In the scripts, I've also included (commented out) some of the things I've already tried.

To setup, first run the the script below, this creates the database, sample table, filtering function and security policy.

-- note: this creates the test database 'rlstest'. when you're tired of this, just drop it.

-- initalize
SET NOCOUNT ON
GO

-- create database
CREATE DATABASE rlstest
GO

-- set database
USE rlstest
GO

-- create test table 'member'
CREATE TABLE dbo.member (
    memberid INT NOT NULL IDENTITY,
    ownercompanyid INT NULL
)
GO

-- create some sample rows where dbo.member.ownercompanyid is sometimes 1 and sometimes NULL
-- note 1: 
-- below, adjust the number of rows to create to give you testresults between 1-10 seconds (so that you notice the drop of performance) 
-- about 2million rows gives me a test result (with the security policy) of about 0,5-1sec on an average dev machine
-- note 2: transaction is merly to give some speed to this
BEGIN TRY
    BEGIN TRAN
    DECLARE @x INT = 2000000
    WHILE @x > 0 BEGIN
        INSERT dbo.member (ownercompanyid) VALUES (CASE WHEN FLOOR(RAND()*2+1)>1 THEN 1 ELSE NULL END)
        SET @x = @x - 1
    END
    COMMIT TRAN
END TRY BEGIN CATCH
    ROLLBACK
END CATCH
GO

-- drop policy & filter function
-- DROP SECURITY POLICY dbo.OwnerCompanyDataSecurityPolicy
-- DROP FUNCTION dbo.fn_filterMember

-- create filter function 
CREATE FUNCTION dbo.fn_filterMember(@ownercompanyid AS INT) RETURNS TABLE WITH SCHEMABINDING AS 
RETURN SELECT 1 result WHERE 
@ownercompanyid IS NULL OR 
(@ownercompanyid IS NOT NULL AND @ownercompanyid=CAST(SESSION_CONTEXT(N'companyid') AS INT)) 

-- tested: short circuit the logical expression (no luck): 
-- @ownercompanyid IS NULL OR 
-- (CASE WHEN @ownercompanyid IS NOT NULL THEN (CASE WHEN @ownercompanyid=CAST(SESSION_CONTEXT(N'companyid') AS INT) THEN 1 ELSE 0 END) ELSE 0 END)=1
GO 

-- create & activate security policy
CREATE SECURITY POLICY dbo.OwnerCompanyDataSecurityPolicy
ADD FILTER PREDICATE dbo.fn_filterMember(ownercompanyid) ON dbo.member
WITH (STATE = ON)

Next go ahead and run the following tests. Timings can be viewed on the "Messages" tab in SQL Server Management Studio (SSMS), and if you'd like to see where filtering step is applied, be sure to include the actual execution plan.

-- tested: add a table index (no luck)
-- CREATE INDEX ix_member_test ON dbo.member (ownercompanyid)

-- test without security policy
ALTER SECURITY POLICY dbo.OwnerCompanyDataSecurityPolicy
WITH (STATE = OFF)

-- note: view timings on the "Messages" tab in SSMS
SET STATISTICS TIME ON 
PRINT '*** Test #1 WITHOUT security policy. Session companyid=NULL:'
EXEC sys.sp_set_session_context @key='companyid',@value=NULL
SELECT COUNT(*) FROM member 

PRINT '*** Test #2 WITHOUT security policy. Session companyid=1:'
EXEC sys.sp_set_session_context @key='companyid',@value=1
SELECT COUNT(*) FROM member 
SET STATISTICS TIME OFF

-- test with security policy
ALTER SECURITY POLICY dbo.OwnerCompanyDataSecurityPolicy
WITH (STATE = ON)

SET STATISTICS TIME ON
PRINT '*** Test #3 WITH security policy. Session companyid=NULL:'
EXEC sys.sp_set_session_context @key='companyid',@value=NULL
SELECT COUNT(*) FROM member 

PRINT '*** Test #4 WITH security policy. Session companyid=1:'
EXEC sys.sp_set_session_context @key='companyid',@value=1
SELECT COUNT(*) FROM member 
SET STATISTICS TIME OFF

you might be out of luck here, sometime ago i had tested RLS with a parameter-less function and i had noticed two different executions: when the filter condition was false (so false for all rows) the filter was evaluated ONCE and no rows were returned. When the filter condition was TRUE (so true for all rows) one would expect a single filter evaluation and all rows to be returned...however the function was called for every single row in the table. — lptr
I was suprised at the query plan that RLS gave me (it was scanning the source table twiice). It got much worse when I included "short circuit" logic for users that could see all data. I worked around it by creating views in a seperate schema that contained the required logic. Which is unpleasant if you want the filtering to be "transparent" on the table. This solution means that you have the original tables for all data and another seperate schema for filtered data. — Nick.McDermaid

Jeroen Mostert Jeroen Mostert · Accepted Answer · 2020-01-20T19:07:38

Much the same "rules" apply for row-level security functions as they do for views, as they seem to work in a curiously analogous way. This means that, with an index on companyid

CREATE INDEX IX_Member_OwnerCompanyId ON dbo.member (ownercompanyid)

And a rewrite of the function as follows

CREATE FUNCTION dbo.fn_filterMember(@ownercompanyid AS INT) 
RETURNS TABLE 
WITH SCHEMABINDING AS 
RETURN 
    SELECT 1 AS result 
    WHERE @ownercompanyid IS NULL 
    UNION ALL
    SELECT 1 
    WHERE @ownercompanyid = CONVERT(INT, SESSION_CONTEXT(N'companyid'))

We get close to optimal results as the optimizer evaluates both branches independently, with one of them evaluating to nothing if the SESSION_CONTEXT value is NULL. If it's not, we still get a rather expensive seek and merge for all the rows that match the converted SESSION_CONTEXT (i.e. the ones that are not NULL). This is still slightly faster than the original function on my machine, though, by about the proportion of rows that aren't NULL.

I don't really see any way to optimize that further, although it's worth noting that it's really only expensive because the filters are not particularly selective. Also, unlike a plain table scan with SELECT COUNT(*) and no row-level security, the resulting query does not want to parallelize, which further hinders performance. I don't know what the exact problem is there (normally inline table-valued functions are no problem), but even forcing things with trace flag 8649 will not help. This appears to be a general problem with row-level security functions, because even a trivial, constant filter supported by the index (WHERE @ownercompanyid IS NULL) inhibits parallelism in some cases.

If you're not married to SESSION_CONTEXT, there is in fact a faster alternative: its older sibling CONTEXT_INFO.

The drawbacks of CONTEXT_INFO are exactly why SESSION_CONTEXT was invented, in that it's a single global (so different applications easily trample on each other's feet), it has a fixed type of BINARY(128) NOT NULL, it cannot be protected (so untrusted applications can clear it) and it can only be set with SET CONTEXT_INFO, which accepts no expressions or variables.

Despite all that, if using CONTEXT_INFO is an option it's worth considering, as the optimizer likes it much better than its keyed counterpart. To wit:

CREATE FUNCTION dbo.fn_filterMember(@ownercompanyid AS INT) 
RETURNS TABLE 
WITH SCHEMABINDING AS 
RETURN 
    SELECT 1 _
    WHERE @ownercompanyid IS NULL 
    OR @ownercompanyid = NULLIF(CONVERT(INT, CONVERT(BINARY(4), CONTEXT_INFO())), 0)

No UNION ALL this time as we don't want to induce two scans in this case. Set with either SET CONTEXT_INFO 0 (to "clear" it) or SET CONTEXT_INFO 1, and now queries are fast once more as parallelism is no longer inhibited. And while a regular index will speed this up, an even better option now is a columnstore index:

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Member_OwnerCompanyId ON dbo.member (ownercompanyid)

The resulting queries are as fast as they can possibly be, as the COUNT(*) is directly served from the columnstore, which is practically made for this. Of course, in a real application (not a simple COUNT(*)) the columnstore may or may not improve things, but at least it demonstrates the optimizer can use it (which is not the case if SESSION_CONTEXT() is used, as it falls back to row processing mode right after the columnstore scan, negating the benefits).

SQL Server 2016 with Row Level Security - Addressing the Bottleneck

1 Answers