Lock Detail (PD_LD)
- Organization of this page
Function
The Lock Detail (PD_LD) record indicates detailed information related to database locks. One record is created for each database lock. This is a multi-instance record.
If you collect the Lock Details (PD_LD) record when a large number of transactions or access locks have occurred in the Microsoft SQL Server, the following events might occur.
-
Space in the Store database increases.
-
Collection takes time, and until the collection has been completed, the collection of other records is skipped.
-
A large amount of memory is consumed.
For example, when number of locks temporarily increases during backup of the Microsoft SQL Server databases, you can set the upper limit by using the LIMIT_PD_LD_NUMBER item in the instance information to reduce system load.
For details about the LIMIT_PD_LD_NUMBER item, see 2.1.4(2)(a) Set up instance information.
To reduce system load, consider not collecting the Lock Detail (PD_LD) record, or substituting one of the following records that can collect information about the number of locks: Database Detail (PD_DD) record, Server Detail (PD) record, and Server Locks Detail (PD_LOCK) record.
Default and changeable values
|
Item |
Default value |
Changeable |
|---|---|---|
|
Collection Interval |
60 |
Y |
|
Collection Offset |
0 |
Y |
|
Log |
No |
Y |
|
LOGIF |
(Blank) |
Y |
|
Over 10 Sec Collection Time |
Yes |
N |
ODBC key field
-
PD_LD_DB_NAME
-
PD_LD_DBID
-
PD_LD_SPID
Lifetime
From the start until release of a lock
Record size
-
Fixed part: 681 bytes
-
Variable part: 928 bytes
Fields
|
View name (Manager name) |
Description |
Summary |
Format |
Delta |
Data source |
|
|---|---|---|---|---|---|---|
|
Microsoft SQL Server version 2014 or earlier |
Microsoft SQL Server version 2016 or later |
|||||
|
Blocking Flag(BLOCKING) |
This flag indicates whether or not the lock is a blocking lock. The following values are valid:
|
-- |
word |
No |
master..syslockinfo.req_status |
sys.dm_tran_locks.request_status |
|
DB Name (DB_NAME) |
Database name related to lock resource |
-- |
string(257) |
No |
master..syslockinfo.db_name(rsc_dbid) |
db_name(sys.dm_tran_locks.resource_database_id) |
|
DBID (DBID) |
Database ID related to lock resource |
-- |
ulong |
No |
master..syslockinfo.rsc_dbid |
sys.dm_tran_locks.resource_database_id |
|
Demand Flag (DEMAND) |
This flag indicates whether or not the lock is a request lock. The following values are valid:
|
-- |
word |
No |
master..syslockinfo.rsc_type, master..syslockinfo. |
sys.dm_tran_locks.resource_type, sys.dm_tran_locks.request_mode |
|
Lock Type (TYPE) |
Lock type (lock request mode and lock resource type) |
-- |
string(80) |
No |
master..syslockinfo.req_mode |
sys.dm_tran_locks.resource_type, sys.dm_tran_locks.request_mode |
|
Orphan Flag (ORPHAN) |
This flag indicates whether or not the lock is an isolated lock. The following values are valid:
|
-- |
word |
No |
master..sysprocesses |
sys.dm_exec_sessions |
|
Page # (PAGE) |
Number of pages allocated to the lock resource |
-- |
ulong |
No |
master..syslockinfo.rsc_text |
sys.dm_tran_locks.resource_description |
|
Program (PROGRAM) |
Name of the application program that is requesting the lock |
-- |
string(257) |
No |
master..sysprocesses.program_name |
sys.dm_exec_sessions.program_name |
|
Record Time (RECORD_TIME) |
Interval end time (GMT format) |
-- |
time_t |
No |
Agent Collector |
|
|
Record Type (INPUT_RECORD_TYPE) |
Record type (always LD) |
-- |
char(8) |
No |
Agent Collector |
|
|
SPID (SPID) |
Process ID that is requesting the lock |
-- |
word |
No |
master..syslockinfo.req_spid |
sys.dm_tran_locks.request_session_id |
|
Start Time (START_TIME) |
Interval start time (GMT format) |
-- |
time_t |
No |
Agent Collector |
|
|
Table (TABLE) |
Table name if the lock resource is a table or row |
-- |
string(257) |
No |
sys.all_objects.name in the row that satisfies the condition master.sys.dm_tran_locks.resource_associated_entity_id = master.sys.all_objects.object_id for each database. |
|
|
User (USER) |
Logon name of the user who issued the command |
-- |
string(61) |
No |
master..sysprocesses, master..syslogins |
sys.dm_exec_sessions, sys.server_principals |