Some of the types of lock Wait that can be consulted in SQL performance tuning

When there are performance problems in our system, we often avoid investigating various types of lock wait, such as row lock wait, page lock wait, page IO Latch wait, and so on. To find out the possible abnormal waiting, for performance optimization to do a certain reference. The specific query statements are shared as follows,

/*******************************************************************************************

Row Lock Wait

*******************************************************************************************/

SELECT ' [' + db_name (ddios.[ DATABASE_ID]) + ']. [' + su. [name] + ']. [‘

+ O.[name] + '] ' as [statement],
I.[name] as ' index_name ',
Ddios. [Partition_number],
Ddios. [Row_lock_count],
Ddios. [Row_lock_wait_count],
CAST (100.0 * ddios.[ Row_lock_wait_count]
/(Ddios. [Row_lock_count]) As DECIMAL (5, 2)) as [%_times_blocked],
Ddios. [Row_lock_wait_in_ms],
CAST (1.0 * ddios.[ Row_lock_wait_in_ms]
/Ddios. [Row_lock_wait_count] As DECIMAL (15, 2))
As [Avg_row_lock_wait_in_ms]
From Sys.dm_db_index_operational_stats (db_id (), NULL, NULL, NULL) Ddios
INNER JOIN sys.indexes i on Ddios. [object_id] = i.[object_id]
and i.[index_id] = Ddios. [index_id]
INNER JOIN sys.objects o on Ddios. [object_id] = o.[object_id]
INNER JOIN sys.sysusers su on o.[schema_id] = su. [UID]
WHERE ddios.row_lock_wait_count > 0
and OBJECTPROPERTY (ddios.[ OBJECT_ID], ' isusertable ') = 1 and object_name (ddios.[ OBJECT_ID]) like ' Pos_transmst '
and i.[index_id] > 0
ORDER by Ddios. [Row_lock_wait_count] DESC,
Su. [Name],
O.[name],
I.[name]

/*******************************************************************************************

--page Lock Wait

*******************************************************************************************/

SELECT object_name (ddios.object_id, ddios.database_id) as object_name,

I.name as Index_name,

DDIOS.OBJECT_ID,
Ddios.partition_number,
Ddios.page_lock_wait_count,
Ddios.page_lock_wait_in_ms,
case where ddmid.database_id is NULL and then ' N '
ELSE ' Y '
END as Missing_index_identified
From Sys.dm_db_index_operational_stats (db_id (), NULL, NULL, NULL) Ddios
INNER JOIN sys.indexes i on ddios.object_id = i.object_id
and ddios.index_id = i.index_id
Left OUTER JOIN (SELECT DISTINCT
database_id,
object_id
From Sys.dm_db_missing_index_details
) as Ddmid on ddmid.database_id = ddios.database_id
and ddmid.object_id = ddios.object_id
WHERE Ddios.page_lock_wait_in_ms > 0 and object_name (ddios.[ OBJECT_ID]) like ' Pos_transmst '
ORDER by Ddios.page_lock_wait_count DESC;

/*******************************************************************************************

--page IO Latch Wait

*******************************************************************************************/
SELECT ' [ ' + db_name () + ']. [' + Object_schema_name (ddios.[ OBJECT_ID])
+ '].[' + object_name (ddios.[ OBJECT_ID]) + '] ' as [object_name],
I.[name] as index_name,
Ddios.page_io_latch_wait_count, /em>
Ddios.page_io_latch_wait_in_ms,
(ddios.page_io_latch_wait_in_ms/ddios.page_io_latch_ Wait_count)
as Avg_page_io_latch_wait_in_ms
from Sys.dm_db_index_operational_stats (db_id (), NULL, NULL, NULL) Ddios
INNER JOIN sys.indexes i on ddios.[ OBJECT_ID] = i.[object_id]
and i.index_id = ddios.index_id
WHERE ddios.page_io_latch_wait_ Count > 0 and object_name (ddios.[ OBJECT_ID]) like ' pos_transmst '
and OBJECTPROPERTY (i.object_id, ' isusertable ') = 1
ORDER by Ddios.page_io_latch_wait_count desc,
avg_page_io_latch_wait_in_ms desc

/*******************************************************************************************

Samezhao

Some of the types of lock Wait that can be consulted in SQL performance tuning