Non parallel plan only in Azure when SESSION_CONTEXT is used
I'm observing strange difference between query plans that I'm getting on my local machine and on Azure SQL. I'm trying to implement row level security, where I read user identifier from SESSION_CONTEXT and then in TVF I check whether the user has access.
On my local machine - SQL Server 2019 Developer edition, DB in compatibility level 150 the query plans are as expected. But when I run it on Azure DB which is also 150 compatibility level, I only get non-parallel query plans with
NonParallelPlanReason="NonParallelizableIntrinsicFunction". I tried a Hyperscale database as well as a DB that is in Elastic Pool and the result is same on both DBs.
You can reproduce that with following code:
CREATE TABLE Users ( UserIdentifier nvarchar(100) PRIMARY KEY CLUSTERED )
INSERT INTO Users (UserIdentifier) VALUES ('MyUserIdentifier')
CREATE TABLE TableWithRLS (
Id int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DataColumn nvarchar(100) NULL
INSERT INTO TableWithRLS (DataColumn)
SELECT TOP 10000000 A.[name] FROM sys.all_columns AS A
CROSS JOIN sys.all_columns AS B
CROSS JOIN sys.all_columns AS C
CREATE OR ALTER FUNCTION CheckAccess (@userIdentifier varchar(100))
SELECT TOP 1 1 AS HasAccess FROM dbo.Users WHERE UserIdentifier = @userIdentifier
EXEC sp_set_session_context N'UserIdentifier', N'MyUserIdentifier', 1
-- This query gets always non-parallel query plan on Azure
SELECT MAX(DataColumn) FROM TableWithRLS AS X
CROSS APPLY CheckAccess(CAST(SESSION_CONTEXT(N'UserIdentifier') AS nvarchar(100)))
When I select the value from session context into a variable first, then it generates parallelizable query plan even in Azure.
DECLARE @userIdentifier AS nvarchar(100) = CAST(SESSION_CONTEXT(N'UserIdentifier') AS nvarchar(100)) SELECT MAX(DataColumn) FROM TableWithRLS AS X CROSS APPLY CheckAccess(@userIdentifier)
Unfortunately I can't do that (or at least I'm not aware how to do that) because I need an inline TVF.
Query plan from Azure: https://www.brentozar.com/pastetheplan/?id=ByxZm45e9
Query plan from local: https://www.brentozar.com/pastetheplan/?id=BylHXV9lc
Is there any difference in SESSION_CONTEXT implementation in Azure that could be causing that? Or does any one has any other ideas what could be the issue?
From the XML of the AzureDB plan:
This is due to the context intrinsic being used and Azure SQL DB specifically setup to disable parallelism on these intrinsics (but this is not the case for box unless specific items are enabled). There are ways to make the box product (on-prem) work the same, but I believe you'd like it the other way around.
Essentially, this is how Azure SQL DB is currently configured, and I don't know if there are ways to get around this in your subscription (I don't generally work with Azure SQL DB). I couldn't find anything in the current documentation to describe this behavior.