Skip to content

sys-dm-os-threads-transact-sql.md

Latest commit

 

History

History
91 lines (77 loc) · 6.93 KB

sys-dm-os-threads-transact-sql.md

File metadata and controls

91 lines (77 loc) · 6.93 KB

title: "sys.dm_os_threads (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/13/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.reviewer: "" ms.suite: "sql" ms.technology: system-objects ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:

  • "dm_os_threads_TSQL"
  • "sys.dm_os_threads"
  • "dm_os_threads"
  • "sys.dm_os_threads_TSQL" dev_langs:
  • "TSQL" helpviewer_keywords:
  • "sys.dm_os_threads dynamic management view" ms.assetid: a5052701-edbf-4209-a7cb-afc9e65c41c1 caps.latest.revision: 35 author: stevestein ms.author: sstein manager: craigg monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

sys.dm_os_threads (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

Returns a list of all [!INCLUDEssNoVersion] Operating System threads that are running under the [!INCLUDEssNoVersion] process.

Note

To call this from [!INCLUDEssSDWfull] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_threads.

Column name Data type Description
thread_address varbinary(8) Memory address (Primary Key) of the thread.
started_by_sqlservr bit Indicates the thread initiator.

1 = [!INCLUDEssNoVersion] started the thread.

0 = Another component started the thread, such as an extended stored procedure from within [!INCLUDEssNoVersion].
os_thread_id int ID of the thread that is assigned by the operating system.
status int Internal status flag.
instruction_address varbinary(8) Address of the instruction that is currently being executed.
creation_time datetime Time when this thread was created.
kernel_time bigint Amount of kernel time that is used by this thread.
usermode_time bigint Amount of user time that is used by this thread.
stack_base_address varbinary(8) Memory address of the highest stack address for this thread.
stack_end_address varbinary(8) Memory address of the lowest stack address of this thread.
stack_bytes_committed int Number of bytes that are committed in the stack.
stack_bytes_used int Number of bytes that are actively being used on the thread.
affinity bigint CPU 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.
Priority int Priority value of this thread.
Locale int Cached locale LCID for the thread.
Token varbinary(8) Cached impersonation token handle for the thread.
is_impersonating int Indicates 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_lock int Operating system status of whether the thread is waiting on the loader lock.
fiber_data varbinary(8) Current Win32 fiber that is running on the thread. This is only applicable when [!INCLUDEssNoVersion] is configured for lightweight pooling.
thread_handle varbinary(8) Internal use only.
event_handle varbinary(8) Internal use only.
scheduler_address varbinary(8) Memory address of the scheduler that is associated with this thread. For more information, see sys.dm_os_schedulers (Transact-SQL).
worker_address varbinary(8) Memory address of the worker that is bound to this thread. For more information, see sys.dm_os_workers (Transact-SQL).
fiber_context_address varbinary(8) Internal fiber context address. This is only applicable when [!INCLUDEssNoVersion] is configured for lightweight pooling.
self_address varbinary(8) Internal consistency pointer.
processor_group smallint Applies to: [!INCLUDEssKilimanjaro] through [!INCLUDEssCurrent].

Processor group ID.
pdw_node_id int Applies to: [!INCLUDEssSDWfull], [!INCLUDEssPDW]

The identifier for the node that this distribution is on.

Permissions

On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDEssSDS_md], requires the VIEW DATABASE STATE permission in the database.

Examples

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

The following query is used to find workers, along with time used for execution, that are running threads not started by [!INCLUDEssNoVersion].

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 [!INCLUDEmsCoName][!INCLUDEssNoVersion] 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;

See Also

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