sys.dm_os_threads (Transact-SQL)

sys.dm_os_threads (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns a list of all SQL Server Operating System threads that are running under the SQL Server process.

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_threads.

Column nameData typeDescription
thread_addressvarbinary(8)Memory address (Primary Key) of the thread.
started_by_sqlservrbitIndicates the thread initiator.

1 = SQL Server started the thread.

0 = Another component started the thread, such as an extended stored procedure from within SQL Server.
os_thread_idintID of the thread that is assigned by the operating system.
statusintInternal status flag.
instruction_addressvarbinary(8)Address of the instruction that is currently being executed.
creation_timedatetimeTime when this thread was created.
kernel_timebigintAmount of kernel time that is used by this thread.
usermode_timebigintAmount of user time that is used by this thread.
stack_base_addressvarbinary(8)Memory address of the highest stack address for this thread.
stack_end_addressvarbinary(8)Memory address of the lowest stack address of this thread.
stack_bytes_committedintNumber of bytes that are committed in the stack.
stack_bytes_usedintNumber of bytes that are actively being used on the thread.
affinitybigintCPU mask on which this thread is running. This depends on the value configured by the ALTER SERVER CONFIGURATION SET PROCESS AFFINITY statement. Might be different from the scheduler in case of soft-affinity.
PriorityintPriority value of this thread.
LocaleintCached locale LCID for the thread.
Tokenvarbinary(8)Cached impersonation token handle for the thread.
is_impersonatingintIndicates whether this thread is using Win32 impersonation.

1 = The thread is using security credentials that are different from the default of the process. This indicates that the thread is impersonating an entity other than the one that created the process.
is_waiting_on_loader_lockintOperating system status of whether the thread is waiting on the loader lock.
fiber_datavarbinary(8)Current Win32 fiber that is running on the thread. This is only applicable when SQL Server is configured for lightweight pooling.
thread_handlevarbinary(8)Internal use only.
event_handlevarbinary(8)Internal use only.
scheduler_addressvarbinary(8)Memory address of the scheduler that is associated with this thread. For more information, see sys.dm_os_schedulers (Transact-SQL).
worker_addressvarbinary(8)Memory address of the worker that is bound to this thread. For more information, see sys.dm_os_workers (Transact-SQL).
fiber_context_addressvarbinary(8)Internal fiber context address. This is only applicable when SQL Server is configured for lightweight pooling.
self_addressvarbinary(8)Internal consistency pointer.
processor_groupsmallintApplies to: SQL Server 2008 R2 through SQL Server 2016.

Processor group ID.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Requires VIEW SERVER STATE permission on the server.

Upon startup, SQL Server starts threads and then associates workers with those threads. However, external components, such as an extended stored procedure, can start threads under the SQL Server process. SQL Server has no control of these threads. sys.dm_os_threads can provide information about rogue threads that consume resources in the SQL Server process.

The following query is used to find workers, along with time used for execution, that are running threads not started by SQL Server.

System_CAPS_ICON_note.jpg Note


For conciseness, the following query uses an asterisk (*) in the SELECT statement. You should avoid using the asterisk (*), especially against catalog views, dynamic management views, and system table-valued functions. Future upgrades and releases of MicrosoftSQL Server may add columns and change the order of columns to these views and functions. These changes might break applications that expect a particular order and number of columns.

SELECT *  
  FROM sys.dm_os_threads  
  WHERE started_by_sqlservr = 0;  

Dynamic Management Views and Functions (Transact-SQL)
sys.dm_os_workers (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft