SQL Server 2019: Session Context & Parallel Plans

You might have heard about the wonders of Context functions (SESSION_CONTEXT, CONTEXT_INFO) – they can offer some interesting solutions for the access control, but do you know what it can mean for the integration with the high performing queries?

Session Context

Managing session context information is not something that everyone is doing on their daily basis, but sometimes … well, just sometimes … some developers might decide to use this feature in order to control the access or to pass some important information along with the login. For that purpose we have SESSION_CONTEXT (SQL Server 2016+) & CONTEXT_INFO (SQL Server 2008+, limited to 128 bytes of binary information) functions.

All recognition goes to Konstantin Kosinsky (LinkedIn) (Principal Software Engineer at Microsoft) who has discovered this case and shared it kindly with me.
In this case I am just a blogger and not the one who has discovered this limitation.

Working with a fresh installation of the SQL Server 2019, I will use the ContosoRetailDW database restoring it from the C:\Install folder and store Data & Log files in the F:\Data & F:\Log folders respectively, plus I shall set up the Clustered Columnstore Index on the FactOnlineSales table with the following script:

USE [master]

if exists (select * from sys.databases where name = 'ContosoRetailDW')
	alter database ContosoRetailDW
		set SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
RESTORE DATABASE [ContosoRetailDW] 
    FROM  DISK = N'C:\Install\ContosoRetailDW.bak' WITH  FILE = 1,  
        MOVE N'ContosoRetailDW2.0' TO N'F:\Data\ContosoRetailDW.mdf', 
        MOVE N'ContosoRetailDW2.0_log' TO N'F:\Log\ContosoRetailDW.ldf',  
        NOUNLOAD,  STATS = 5;
 
alter database ContosoRetailDW
    set MULTI_USER;
GO
use ContosoRetailDW;

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

GO
ALTER DATABASE [ContosoRetailDW] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [ContosoRetailDW] MODIFY FILE ( NAME = N'ContosoRetailDW2.0', SIZE = 2000000KB , FILEGROWTH = 128000KB )
GO
ALTER DATABASE [ContosoRetailDW] MODIFY FILE ( NAME = N'ContosoRetailDW2.0_log', SIZE = 400000KB , FILEGROWTH = 256000KB )
GO

use ContosoRetailDW;

ALTER TABLE dbo.[FactOnlineSales] DROP CONSTRAINT [PK_FactOnlineSales_SalesKey];

CREATE CLUSTERED COLUMNSTORE INDEX PK_FactOnlineSales
	on dbo.FactOnlineSales;

Yes, I have set the compatibility level to 150, because as you should know – some of the magical unicorns fly only under the latest compatibility level, and I will prove you right away this:

Let’s setup some the value to our store, from which we want the information to be filtered for each user automatically, when processing the transactions from the FactOnlineSales table: 

EXEC sys.sp_set_session_context 'N'StoreKey', 19674;

Now, let’s count the transactions inside our store (which is non-existing and there will be 0 returned, but the point here is to do some work done:

SELECT COUNT(*) FROM [dbo].[FactOnlineSales] 
    WHERE StoreKey=SESSION_CONTEXT(N'StoreKey');

This plan looks ugly, right ? Instead of the expected aggregate predicate pushdown we have a lot of additional pretty much useless work with the overall estimated cost of 4.44 Query Bucks. We shall address the shape of the plan (yeah, its a thing of the implicit casting here). At least our execution plan runs in parallel.

Switching gears into the compatibility level with SQL Server 2017, let’s repeat the query we have executed previously:

ALTER DATABASE ContosoRetailDW 
    SET COMPATIBILITY_LEVEL = 140;

SELECT COUNT(*) FROM [dbo].[FactOnlineSales] 
    WHERE StoreKey=SESSION_CONTEXT(N'StoreKey');


For the awesome price of 8.77 Query Bucks (Wait, why are we doubling it ?), we have a single-threaded execution plan. That is not THAT awesome! :(
I guess we need to take a more detailed look at the warning, but lets see what is going on there with the Filter & Compute Scalar iterators ?

We are burning our Query Bucks like it is some kind of a crazy Student Party and there is no Batch Execution Mode and as a matter of a fact we are converting our data into … SQL_VARIANT ? Yes, we do and that is by itself is a very bad idea, since Aggregate Predicate Pushdown supports the operations up to 8 bytes (I mean like so many other SQL Server Query Operations). Well, SQL_VARAINT naturally is not a good friend of the Batch Execution Mode at all, as you would expect.
We need to fix this situation, especially because our SELECT warning sign will tell us exactly that the IMPLICIT CONVERSION is not helping much, but most probably leads us into the wrong performance.

For making things right we need to solve the query by ourselves by explicitly casting the output of our function SESSION_CONTEXT(N’StoreKey’) to the INT, or as another alternative we might have as well used a variable:

SELECT COUNT(*) FROM [dbo].[FactOnlineSales] 
	WHERE StoreKey = CAST(SESSION_CONTEXT(N'StoreKey') AS INT);

For the further tests we shall reconfigure the threshold for parallelism to the unbearable 1.

EXEC sys.sp_configure N'cost threshold for parallelism', N'1';
GO
RECONFIGURE WITH OVERRIDE
GO

Re-executing (& re-evaluating our lives with it):

SELECT COUNT(*) FROM [dbo].[FactOnlineSales] 
	WHERE StoreKey = CAST(SESSION_CONTEXT(N'StoreKey') AS INT);

gives us the very same execution plan as before – single threaded …

Let’s see how it would work with the 150 (SQL Server 2019 Compatibility Level) 

ALTER DATABASE ContosoRetailDW 
    SET COMPATIBILITY_LEVEL = 150;

SELECT COUNT(*) FROM [dbo].[FactOnlineSales] 
    WHERE StoreKey = CAST(SESSION_CONTEXT(N'StoreKey') AS INT);


Wonderful! We have a parallelism here … A pretty fake one which brings nothing, besides proving that you can actually get a query with SESSION_CONTEXT function running in parallel, but only with the SQL Server 2019 compatibility level.

Looking at the 140 (SQL Server 2017 Compatibility Level) Execution Plan we can dive into a XML just to find out the following picture:

Yeah, if we are not in the SQL Server 2019 Compatibility Level – we can’t get parallelism when working with our wonderful SESSION_CONTEXT because it is simply not supported and this is one of the smaller under-the-hood SQL Server 2019 improvements.
To solve this problem as mentioned earlier you could declare a variable and put the value out of the SESSION_CONTEXT into it:

ALTER DATABASE ContosoRetailDW 
    SET COMPATIBILITY_LEVEL = 140;

DECLARE @storeKey INT = CAST(SESSION_CONTEXT(N'StoreKey') AS INT);
SELECT COUNT(*) FROM [dbo].[FactOnlineSales] 
	WHERE StoreKey = @storeKey;

and would get pretty much the same parallel plan as you would otherwise.

The limitation with the SESSION_CONTEXT is general and it is not about the Predicate existence, but simply any mention within a query will remove your parallelism and here is the same example as on the previous execution, but just enhanced with the direct innovation in the SELECT:

ALTER DATABASE ContosoRetailDW 
    SET COMPATIBILITY_LEVEL = 140;

DECLARE @storeKey INT = CAST(SESSION_CONTEXT(N'StoreKey') AS INT)
SELECT COUNT(*), SESSION_CONTEXT(N'StoreKey') 
    FROM [dbo].[FactOnlineSales] 
    WHERE StoreKey = @storeKey;

Switching back to the SQL Server 2019 Compatibility Level (150) solves the problem again:

ALTER DATABASE ContosoRetailDW 
    SET COMPATIBILITY_LEVEL = 150;

DECLARE @storeKey INT = CAST(SESSION_CONTEXT(N'StoreKey') AS INT)
SELECT COUNT(*), SESSION_CONTEXT(N'StoreKey') 
    FROM [dbo].[FactOnlineSales] 
    WHERE StoreKey = @storeKey;

Contrary to the later added function, the old one CONTEXT_INFO() does not suffer the same problem as you can see on the slightly modified queries:

ALTER DATABASE ContosoRetailDW SET COMPATIBILITY_LEVEL = 140
GO
SELECT COUNT(*) FROM [dbo].[FactOnlineSales] WHERE StoreKey=CONTEXT_INFO();
GO

ALTER DATABASE ContosoRetailDW SET COMPATIBILITY_LEVEL = 150
GO
SELECT COUNT(*) FROM [dbo].[FactOnlineSales] WHERE StoreKey = CONTEXT_INFO();  
GO


Both of the invocations run in parallel without any problems.

Some of those who are capable of looking deeper under the hood or question the content they read would objectify that actually SESSION_CONTEXT() would prevent any of the parallelism to take place even on the Rowstore Indexes and the proof is here – just run the same queries against the non-columnstore indexes:

ALTER DATABASE ContosoRetailDW 
    SET COMPATIBILITY_LEVEL = 140;

SELECT COUNT(*) FROM [dbo].[FactSales] 
    WHERE StoreKey = CAST(SESSION_CONTEXT(N'StoreKey') AS INT);


ALTER DATABASE ContosoRetailDW 
    SET COMPATIBILITY_LEVEL = 150;

SELECT COUNT(*) FROM [dbo].[FactSales] 
    WHERE StoreKey = CAST(SESSION_CONTEXT(N'StoreKey') AS INT);

It is great to see some small but still important fixes to get into the engine without great fanfare and given that the lack of parallelism for the function SESSION_CONTEXT() was hiding for 2 major releases … I hope that even though the resources are definitely limited everywhere – we shall have a better new feature support in the future.
I do not hold my breath though :)

By Niko Neugebauer | February 8, 2020 | Azure SQLDatabase, performance, SQL Server | No Comments |

Leave a Reply

Your email address will not be published. Required fields are marked *