sys.dm_tran_locks (Transact-SQL)

sys.dm_tran_locks (Transact-SQL)

 

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

Returns information about currently active lock manager resources in SQL Server 2016. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

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_tran_locks.

Column nameData typeDescription
resource_typenvarchar(60)Represents the resource type. The value can be one of the following: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, or ALLOCATION_UNIT.
resource_subtypenvarchar(60)Represents a subtype of resource_type. Acquiring a subtype lock without holding a nonsubtyped lock of the parent type is technically valid. Different subtypes do not conflict with each other or with the nonsubtyped parent type. Not all resource types have subtypes.
resource_database_idintID of the database under which this resource is scoped. All resources handled by the lock manager are scoped by the database ID.
resource_descriptionnvarchar(256)Description of the resource that contains only information that is not available from other resource columns.
resource_associated_entity_idbigintID of the entity in a database with which a resource is associated. This can be an object ID, Hobt ID, or an Allocation Unit ID, depending on the resource type.
resource_lock_partitionIntID of the lock partition for a partitioned lock resource. The value for nonpartitioned lock resources is 0.
request_modenvarchar(60)Mode of the request. For granted requests, this is the granted mode; for waiting requests, this is the mode being requested.
request_typenvarchar(60)Request type. The value is LOCK.
request_statusnvarchar(60)Current status of this request. Possible values are GRANTED, CONVERT, WAIT, LOW_PRIORITY_CONVERT, LOW_PRIORITY_WAIT, or ABORT_BLOCKERS. For more information about low priority waits and abort blockers, see the low_priority_lock_wait section of ALTER INDEX (Transact-SQL).
request_reference_countsmallintReturns an approximate number of times the same requestor has requested this resource.
request_lifetimeintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
request_session_idintSession ID that currently owns this request. The owning session ID can change for distributed and bound transactions. A value of -2 indicates that the request belongs to an orphaned distributed transaction. A value of -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.
request_exec_context_idintExecution context ID of the process that currently owns this request.
request_request_idintRequest ID (batch ID) of the process that currently owns this request. This value will change every time that the active Multiple Active Result Set (MARS) connection for a transaction changes.
request_owner_typenvarchar(60)Entity type that owns the request. Lock manager requests can be owned by a variety of entities. Possible values are:

TRANSACTION = The request is owned by a transaction.

CURSOR = The request is owned by a cursor.

SESSION = The request is owned by a user session.

SHARED_TRANSACTION_WORKSPACE = The request is owned by the shared part of the transaction workspace.

EXCLUSIVE_TRANSACTION_WORKSPACE = The request is owned by the exclusive part of the transaction workspace.

NOTIFICATION_OBJECT = The request is owned by an internal SQL Server component. This component has requested the lock manager to notify it when another component is waiting to take the lock. The FileTable feature is a component that uses this value.

 Note: Work spaces are used internally to hold locks for enlisted sessions.
request_owner_idbigintID of the specific owner of this request.

When a transaction is the owner of the request, this value contains the transaction ID.

When a FileTable is the owner of the request, request_owner_id has one of the following values.

 

-4 : A FileTable has taken a database lock.

-3 : A FileTable has taken a table lock.

Other value : The value represents a file handle. This value also appears as fcb_id in the dynamic management view sys.dm_filestream_non_transacted_handles (Transact-SQL).
request_owner_guiduniqueidentifierGUID of the specific owner of this request. This value is only used by a distributed transaction where the value corresponds to the MS DTC GUID for that transaction.
request_owner_lockspace_idnvarchar(32)Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. This value represents the lockspace ID of the requestor. The lockspace ID determines whether two requestors are compatible with each other and can be granted locks in modes that would otherwise conflict with one another.
lock_owner_addressvarbinary(8)Memory address of the internal data structure that is used to track this request. This column can be joined the with resource_address column in sys.dm_os_waiting_tasks.
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.

A granted request status indicates that a lock has been granted on a resource to the requestor. A waiting request indicates that the request has not yet been granted. The following waiting-request types are returned by the request_status column:

  • A convert request status indicates that the requestor has already been granted a request for the resource and is currently waiting for an upgrade to the initial request to be granted.

  • A wait request status indicates that the requestor does not currently hold a granted request on the resource.

Because sys.dm_tran_locks is populated from internal lock manager data structures, maintaining this information does not add extra overhead to regular processing. Materializing the view does require access to the lock manager internal data structures. This can have minor effects on the regular processing in the server. These effects should be unnoticeable and should only affect heavily used resources. Because the data in this view corresponds to live lock manager state, the data can change at any time, and rows are added and removed as locks are acquired and released. This view has no historical information.

Two requests operate on the same resource only if all the resource-group columns are equal.

You can control the locking of read operations by using the following tools:

A resource that is running under one session ID can have more than one granted lock. Different entities that are running under one session can each own a lock on the same resource, and the information is displayed in the request_owner_type and request_owner_id columns that are returned by sys.dm_tran_locks. If multiple instances of the same request_owner_type exist, the request_owner_id column is used to distinguish each instance. For distributed transactions, the request_owner_type and the request_owner_guid columns will show the different entity information.

For example, Session S1 owns a shared lock on Table1; and transaction T1, which is running under session S1, also owns a shared lock on Table1. In this case, the resource_description column that is returned by sys.dm_tran_locks will show two instances of the same resource. The request_owner_type column will show one instance as a session and the other as a transaction. Also, the resource_owner_id column will have different values.

Multiple cursors that run under one session are indistinguishable and are treated as one entity.

Distributed transactions that are not associated with a session ID value are orphaned transactions and are assigned the session ID value of -2. For more information, see KILL (Transact-SQL).

The following table lists the resources that are represented in the resource_associated_entity_id column.

Resource typeResource descriptionResource_associated_entity_id
DATABASERepresents a database.Not applicable
FILERepresents a database file. This file can be either a data or a log file.Not applicable
OBJECTRepresents a database object. This object can be a data table, view, stored procedure, extended stored procedure, or any object that has an object ID.Object ID
PAGERepresents a single page in a data file.HoBt ID. This value corresponds to sys.partitions.hobt_id. The HoBt ID is not always available for PAGE resources because the HoBt ID is extra information that can be provided by the caller, and not all callers can provide this information.
KEYRepresents a row in an index.HoBt ID. This value corresponds to sys.partitions.hobt_id.
EXTENTRepresents a data file extent. An extent is a group of eight contiguous pages.Not applicable
RIDRepresents a physical row in a heap.HoBt ID. This value corresponds to sys.partitions.hobt_id. The HoBt ID is not always available for RID resources because the HoBt ID is extra information that can be provided by the caller, and not all callers can provide this information.
APPLICATIONRepresents an application specified resource.Not applicable
METADATARepresents metadata information.Not applicable
HOBTRepresents a heap or a B-tree. These are the basic access path structures.HoBt ID. This value corresponds to sys.partitions.hobt_id.
ALLOCATION_UNITRepresents a set of related pages, such as an index partition. Each allocation unit covers a single Index Allocation Map (IAM) chain.Allocation Unit ID. This value corresponds to sys.allocation_units.allocation_unit_id.

The following table lists the subtypes that are associated with each resource type.

ResourceSubTypeSynchronizes
ALLOCATION_UNIT.BULK_OPERATION_PAGEPre-allocated pages used for bulk operations.
ALLOCATION_UNIT.PAGE_COUNTAllocation unit page count statistics during deferred drop operations.
DATABASE.BULKOP_BACKUP_DBDatabase backups with bulk operations.
DATABASE.BULKOP_BACKUP_LOGDatabase log backups with bulk operations.
DATABASE.CHANGE_TRACKING_CLEANUPChange tracking cleanup tasks.
DATABASE.CT_DDLDatabase and table-level change tracking DDL operations.
DATABASE.CONVERSATION_PRIORITYService Broker conversation priority operations such as CREATE BROKER PRIORITY.
DATABASE.DDLData definition language (DDL) operations with filegroup operations, such as drop.
DATABASE.ENCRYPTION_SCANTDE encryption synchronization.
DATABASE.PLANGUIDEPlan guide synchronization.
DATABASE.RESOURCE_GOVERNOR_DDLDDL operations for resource governor operations such as ALTER RESOURCE POOL.
DATABASE.SHRINKDatabase shrink operations.
DATABASE.STARTUPUsed for database startup synchronization.
FILE.SHRINKFile shrink operations.
HOBT.BULK_OPERATIONHeap-optimized bulk load operations with concurrent scan, under these isolation levels: snapshot, read uncommitted, and read committed using row versioning.
HOBT.INDEX_REORGANIZEHeap or index reorganization operations.
OBJECT.COMPILEStored procedure compile.
OBJECT.INDEX_OPERATIONIndex operations.
OBJECT.UPDSTATSStatistics updates on a table.
METADATA.ASSEMBLYIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ASSEMBLY_CLR_NAMEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ASSEMBLY_TOKENIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ASYMMETRIC_KEYIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.AUDITIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.AUDIT_ACTIONSIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.AUDIT_SPECIFICATIONIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.AVAILABILITY_GROUPIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CERTIFICATEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CHILD_INSTANCEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.COMPRESSED_FRAGMENTIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.COMPRESSED_ROWSETIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CONVERSTATION_ENDPOINT_RECVIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CONVERSTATION_ENDPOINT_SENDIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CONVERSATION_GROUPIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CONVERSATION_PRIORITYIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CREDENTIALIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CRYPTOGRAPHIC_PROVIDERIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DATA_SPACEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DATABASEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DATABASE_PRINCIPALIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DB_MIRRORING_SESSIONIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DB_MIRRORING_WITNESSIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DB_PRINCIPAL_SIDIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ENDPOINTIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ENDPOINT_WEBMETHODIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.EXPR_COLUMNIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.EXPR_HASHIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.FULLTEXT_CATALOGIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.FULLTEXT_INDEXIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.FULLTEXT_STOPLISTIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.INDEX_EXTENSION_SCHEMEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.INDEXSTATSIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.INSTANTIATED_TYPE_HASHIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.MESSAGEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.METADATA_CACHEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PARTITION_FUNCTIONIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PASSWORD_POLICYIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PERMISSIONSIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PLAN_GUIDEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PLAN_GUIDE_HASHIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PLAN_GUIDE_SCOPEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.QNAMEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.QNAME_HASHIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.REMOTE_SERVICE_BINDINGIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ROUTEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SCHEMAIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SECURITY_CACHEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SECURITY_DESCRIPTORIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SEQUENCEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVER_EVENT_SESSIONSIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVER_PRINCIPALIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVICEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVICE_BROKER_GUIDIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVICE_CONTRACTIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVICE_MESSAGE_TYPEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.STATSIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SYMMETRIC_KEYIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.USER_TYPEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.XML_COLLECTIONIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.XML_COMPONENTIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.XML_INDEX_QNAMEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.

The following table provides the format of the resource_description column for each resource type.

ResourceFormatDescription
DATABASENot applicableDatabase ID is already available in the resource_database_id column.
FILE<file_id>ID of the file that is represented by this resource.
OBJECT<object_id>ID of the object that is represented by this resource. This object can be any object listed in sys.objects, not just a table.
PAGE<file_id>:<page_in_file>Represents the file and page ID of the page that is represented by this resource.
KEY<hash_value>Represents a hash of the key columns from the row that is represented by this resource.
EXTENT<file_id>:<page_in_files>Represents the file and page ID of the extent that is represented by this resource. The extent ID is the same as the page ID of the first page in the extent.
RID<file_id>:<page_in_file>:<row_on_page>Represents the page ID and row ID of the row that is represented by this resource. Note that if the associated object ID is 99, this resource represents one of the eight mixed page slots on the first IAM page of an IAM chain.
APPLICATION<DbPrincipalId>:<upto 32 characters>:(<hash_value>)Represents the ID of the database principal that is used for scoping this application lock resource. Also included are up to 32 characters from the resource string that corresponds to this application lock resource. In certain cases, only 2 characters can be displayed due to the full string no longer being available. This behavior occurs only at database recovery time for application locks that are reacquired as part of the recovery process. The hash value represents a hash of the full resource string that corresponds to this application lock resource.
HOBTNot applicableHoBt ID is included as the resource_associated_entity_id.
ALLOCATION_UNITNot applicableAllocation Unit ID is included as the resource_associated_entity_id.
METADATA.ASSEMBLYassembly_id = AIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ASSEMBLY_CLR_NAME$qname_id = QIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ASSEMBLY_TOKENassembly_id = A, $token_idIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ASSYMMETRIC_KEYasymmetric_key_id = AIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.AUDITaudit_id = AIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.AUDIT_ACTIONSdevice_id = D, major_id = MIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.AUDIT_SPECIFICATIONaudit_specification_id = AIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.AVAILABILITY_GROUPavailability_group_id = AIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CERTIFICATEcertificate_id = CIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CHILD_INSTANCE$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.COMPRESSED_FRAGMENTobject_id = O , compressed_fragment_id = CIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.COMPRESSED_ROWobject_id = OIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CONVERSTATION_ENDPOINT_RECV$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CONVERSTATION_ENDPOINT_SEND$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CONVERSATION_GROUP$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CONVERSATION_PRIORITYconversation_priority_id = CIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CREDENTIALcredential_id = CIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.CRYPTOGRAPHIC_PROVIDERprovider_id = PIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DATA_SPACEdata_space_id = DIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DATABASEdatabase_id = DIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DATABASE_PRINCIPALprincipal_id = PIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DB_MIRRORING_SESSIONdatabase_id = DIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DB_MIRRORING_WITNESS$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.DB_PRINCIPAL_SID$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ENDPOINTendpoint_id = EIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ENDPOINT_WEBMETHOD$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.FULLTEXT_CATALOGfulltext_catalog_id = FIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.FULLTEXT_INDEXobject_id = OIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.EXPR_COLUMNobject_id = O, column_id = CIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.EXPR_HASHobject_id = O, $hash = HIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.FULLTEXT_CATALOGfulltext_catalog_id = FIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.FULLTEXT_INDEXobject_id = OIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.FULLTEXT_STOPLISTfulltext_stoplist_id = FIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.INDEX_EXTENSION_SCHEMEindex_extension_id = IIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.INDEXSTATSobject_id = O, index_id or stats_id = IIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.INSTANTIATED_TYPE_HASHuser_type_id = U, hash = HIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.MESSAGEmessage_id = MIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.METADATA_CACHE$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PARTITION_FUNCTIONfunction_id = FIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PASSWORD_POLICYprincipal_id = PIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PERMISSIONSclass = CIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.PLAN_GUIDEplan_guide_id = PIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA. PLAN_GUIDE_HASH$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA. PLAN_GUIDE_SCOPEscope_id = SIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.QNAME$qname_id = QIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.QNAME_HASH$qname_scope_id = Q, $qname_hash = HIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.REMOTE_SERVICE_BINDINGremote_service_binding_id = RIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.ROUTEroute_id = RIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SCHEMAschema_id = SIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SECURITY_CACHE$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SECURITY_DESCRIPTORsd_id = SIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SEQUENCE$seq_type = S, object_id = OIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVERserver_id = SIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVER_EVENT_SESSIONSevent_session_id = EIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVER_PRINCIPALprincipal_id = PIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVICEservice_id = SIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVICE_BROKER_GUID$hash = H1:H2:H3Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVICE_CONTRACTservice_contract_id = SIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SERVICE_MESSAGE_TYPEmessage_type_id = MIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.STATSobject_id = O, stats_id = SIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.SYMMETRIC_KEYsymmetric_key_id = SIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.USER_TYPEuser_type_id = UIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.XML_COLLECTIONxml_collection_id = XIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.XML_COMPONENTxml_component_id = XIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
METADATA.XML_INDEX_QNAMEobject_id = O, $qname_id = QIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.

The following XEvents are related to partition SWITCH and online index rebuild. For information about syntax, see ALTER TABLE (Transact-SQL) and ALTER INDEX (Transact-SQL).

  • lock_request_priority_state

  • process_killed_by_abort_blockers

  • ddl_with_wait_at_low_priority

The existing XEvent progress_report_online_index_operation for online index operations was extended by adding partition_number and partition_id.

A. Using sys.dm_tran_locks with other tools

The following example works with a scenario in which an update operation is blocked by another transaction. By using sys.dm_tran_locks and other tools, information about locking resources is provided.

USE tempdb;  
GO  
  
-- Create test table and index.  
CREATE TABLE t_lock  
    (  
    c1 int, c2 int  
    );  
GO  
  
CREATE INDEX t_lock_ci on t_lock(c1);  
GO  
  
-- Insert values into test table  
INSERT INTO t_lock VALUES (1, 1);  
INSERT INTO t_lock VALUES (2,2);  
INSERT INTO t_lock VALUES (3,3);  
INSERT INTO t_lock VALUES (4,4);  
INSERT INTO t_lock VALUES (5,5);  
INSERT INTO t_lock VALUES (6,6);  
GO  
  
-- Session 1  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
  
BEGIN TRAN  
    SELECT c1  
        FROM t_lock  
        WITH(holdlock, rowlock);  
  
-- Session 2  
BEGIN TRAN  
    UPDATE t_lock SET c1 = 10  

The following query will display lock information. The value for <dbid> should be replaced with the database_id from sys.databases.

SELECT resource_type, resource_associated_entity_id,  
    request_status, request_mode,request_session_id,  
    resource_description   
    FROM sys.dm_tran_locks  
    WHERE resource_database_id = <dbid>  

The following query returns object information by using resource_associated_entity_id from the previous query. This query must be executed while you are connected to the database that contains the object.

SELECT object_name(object_id), *  
    FROM sys.partitions  
    WHERE hobt_id=<resource_associated_entity_id>  

The following query will show blocking information.

SELECT   
        t1.resource_type,  
        t1.resource_database_id,  
        t1.resource_associated_entity_id,  
        t1.request_mode,  
        t1.request_session_id,  
        t2.blocking_session_id  
    FROM sys.dm_tran_locks as t1  
    INNER JOIN sys.dm_os_waiting_tasks as t2  
        ON t1.lock_owner_address = t2.resource_address;  

Release the resources by rolling back the transactions.

-- Session 1  
ROLLBACK;  
GO  
  
-- Session 2  
ROLLBACK;  
GO  

B. Linking session information to operating system threads

The following example returns information that associates a session ID with a Windows thread ID. The performance of the thread can be monitored in the Windows Performance Monitor. This query does not return session IDs that are currently sleeping.

SELECT STasks.session_id, SThreads.os_thread_id  
    FROM sys.dm_os_tasks AS STasks  
    INNER JOIN sys.dm_os_threads AS SThreads  
        ON STasks.worker_address = SThreads.worker_address  
    WHERE STasks.session_id IS NOT NULL  
    ORDER BY STasks.session_id;  
GO  

sys.dm_tran_database_transactions (Transact-SQL)
Dynamic Management Views and Functions (Transact-SQL)
Transaction Related Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft