SPID Association

Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected].

Can I associate a SQL Server connection server process ID (SPID) with the SQL Server User Mode Scheduler (UMS) that's responsible for scheduling the connection on a CPU in the machine?

No documented technique exists for correlating a SQL Server SPID with the UMS responsible for managing its execution. However, you can get the information by using the undocumented DBCC PSS command. A full discussion of DBCC PSS is beyond the scope of this Q&A. For information about this undocumented command, see Kalen Delaney's book Inside Microsoft SQL Server 2000 (Microsoft Press, 2001).

Thanks to SQL Server MVP Dan Guzman and his team at Abiliti Solutions for providing the T-SQL solution that Listing 1 shows. Listing 1 associates a SPID with its managing UMS; Web Listing 1 shows a Visual Basic (VB) approach to the same task. (To access Web Listing 1, enter InstantDoc ID 27105 at http://www.sqlmag.com and click Download the code.) Both methods parse DBCC PSS output, so first, run the T-SQL code that Listing 2, page 16, shows to generate some sample DBCC PSS output that you can use with either solution.

Trace flag 3604 directs output of certain DBCC commands to the client instead of to the SQL Server error log. The PSS call returns a lot of internals information for SPID 1. Most of the information is irrelevant for this example, but if you search the output, you'll notice two lines that look like

ec_umsContext->m_pSched->m_id (SchedulerId) = 0x0

The number directly to the right of the 0x is the ID of the UMS that manages execution for the SPID in question, which is SPID 1. This string shows that the UMS with an ID of 0 handles SPID 1. In other words, UMS 0 can schedule CPU time for the queries running on SPID 1. If the string had read

ec_umsContext->m_pSched->m_id (SchedulerId) = 0x1

you'd know that the UMS that has the ID 1 was managing SPID 1. By default, the number of UMSs matches the number of processors in the system, and the first UMS ID is always 0. So a 4-CPU machine has UMSs with IDs ranging from 0 to 3 by default. With that information, you can easily construct a T-SQL or VB solution for generating DBCC PSS output for all current SPIDs and parsing the output to show which UMS manages each SPID. For more information about how you can use UMS IDs, see "Tip: Improve Performance by Inspecting UMS IDs" below.