Capturing session_id scheduler assignment using SQLCMD Mode

Yesterday I was working on a SSMS SQLCMD-mode script and I noticed that periodically my session_id changed across test executions.  I was tracking wait statistics based on specific session IDs, so I had to periodically add new session ids to my extended events session definition to make sure I captured my session’s wait stats accordingly. It was an isolated instance, so I could rely on the various session ids only being associated with my test.

It got me thinking about capturing session_id and scheduler assignment using SQLCMD mode. I wasn’t so much interested in the new session ids being used, but rather, I was interested in where my session ids were landing from a scheduler perspective. Certainly you could test this through an application, PowerShell script, etc. But SQLCMD mode offered a quick way to do this and I thought I would explore further. 

There are a few scenarios that I thought would be interesting, but for now I’ll just show one of them. For this scenario I had SQL Server instance (10.50.2500) with four visible schedulers and without multiple NUMA nodes (stay tuned on that for another time).  I started off by creating a table in tempdb to track session_id, scheduler_id, parent_node_id, cpu_id and a counter of connects:

:CONNECT CAESAR

USE tempdb;

CREATE TABLE [dbo].[SQLCMDRoundRobin](

    [sqlcmdroundrobin_id] [int] NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1),

      [session_id] [smallint] NOT NULL,

      [scheduler_id] [int] NOT NULL,

      [parent_node_id] [int] NOT NULL,

      [cpu_id] [smallint] NOT NULL,

      [connect_counter] [int] NOT NULL

) ON [PRIMARY];

GO

Before I executed my test, I launched a separate background set of query executions (looping) which put the system under CPU pressure across all schedulers.

Next, I connected using SQLCMD mode and used MERGE to INSERT/UPDATE specific session_id/scheduler_id combos.  I used “GO 10000” to connect-and-execute 10,000 times:

:CONNECT CAESAR

USE tempdb;

SET NOCOUNT ON;

MERGE  dbo.SQLCMDRoundRobin AS target

USING (

      SELECT      r.session_id,

                  s.scheduler_id,

                  s.parent_node_id,

                  s.cpu_id

      FROM sys.dm_exec_requests AS r

      INNER JOIN sys.dm_os_schedulers AS s ON

            r.scheduler_id = s.scheduler_id

      WHERE r.scheduler_id IS NOT NULL AND

            r.session_id = @@SPID) AS source

                  (session_id, scheduler_id, parent_node_id, cpu_id)

ON (target.session_id = source.session_id AND

    target.scheduler_id = source.scheduler_id)

WHEN MATCHED THEN

      UPDATE SET connect_counter = target.connect_counter  + 1

WHEN NOT MATCHED THEN

      INSERT (session_id, scheduler_id, parent_node_id, cpu_id, connect_counter )

      VALUES (source.session_id, source.scheduler_id,

             source.parent_node_id, source.cpu_id,  1);

GO 10000

Then I executed the following query to look at the distribution based on every SQLCMD connect:

:CONNECT CAESAR

USE tempdb;

SELECT session_id, scheduler_id, parent_node_id, cpu_id, connect_counter

FROM [dbo].[SQLCMDRoundRobin];

GO

This returned:

Removing all concurrent activity, I saw the following distribution instead:

I also tested the workload and CPU driving background activity with the processor affinity mask explicitly set, and the distribution was as follows:

I’d like to apply this framework to more scenarios – including for those on NUMA systems and also against a wider variety of workloads, since this has an impact on how the sessions will get distributed.  When I get the chance, I’ll write about the observations here, but I thought I would share the capturing method in the meantime.