|
set transaction isolation level read uncommitted;
set lock_timeout 20000;
set implicit_transactions off;
if @@trancount > 0 commit transaction;
set language
us_english;
set cursor_close_on_commit off;
set
query_governor_cost_limit 0;
set numeric_roundabort off;
set deadlock_priority low;
set nocount on;
----------------------------------------------------------------------------------------------
--
Test for index
contention.
----------------------------------------------------------------------------------------------
declare
@dbid int
select @dbid = db_id()
-------------------------------------------------- return the top 10
indexes per DB that have page latch wait
select top
10
[DatabaseName]=db_name(),
[ObjectID]=s.object_id,
[TableName]=object_name(s.object_id),
[IndexName]=i.name,
[Partition]=s.partition_number,
page_latch_wait_count,
page_latch_wait_in_ms,
[AvgPageLatchWaitInMs]=case
page_latch_wait_count when 0 then 0 else cast((1.0 *
page_latch_wait_in_ms) / page_latch_wait_count as numeric(15,2)) end,
page_io_latch_wait_count,
page_io_latch_wait_in_ms,
[PageWaitCnt]=page_latch_wait_count+page_io_latch_wait_count
from sys.dm_db_index_operational_stats (@dbid,
NULL, NULL, NULL) s
left outer join sys.indexes i on
i.object_id = s.object_id and i.index_id = s.index_id
where
objectproperty(s.object_id,'IsUserTable') = 1
and
objectproperty(s.object_id,'IsMSShipped') = 0
--and
s.page_latch_wait_count > 1
--order by [PageLatchWaitCount]
desc
order by [PageWaitCnt] desc, [page_latch_wait_count] desc,
[page_io_latch_wait_count] desc
----------------------------------------------------------------------------------------------------------------
select
db_name(database_id) AS dbname, [ObjectName] =
object_name(object_id),
* from sys.dm_db_index_operational_stats(NULL,
NULL, NULL, NULL)
order by page_io_latch_wait_count desc
--------------------------------------------------- return the top 10
indexes per DB that have page lock wait
select top
10
db_name() AS DatabaseName,
s.object_id,
object_name(s.object_id) AS TableName,
i.name AS
IndexName,
s.partition_number AS
PartitionName,
page_lock_count,
page_lock_wait_count,
case page_lock_count
when 0 then 0 else cast((100.0 * page_lock_wait_count) / page_lock_count
as numeric(15,2)) end AS PageLockPercent,
page_lock_wait_in_ms,
[AvgPageLockWaitms]=case
page_lock_wait_count when 0 then 0 else cast((1.0 * page_lock_wait_in_ms)
/ page_lock_wait_count as numeric(15,2)) end
from
sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL)
s
left outer join sys.indexes i on i.object_id =
s.object_id and i.index_id = s.index_id
where
objectproperty(s.object_id,'IsUserTable') = 1
and
objectproperty(s.object_id,'IsMSShipped') = 0
and
s.page_lock_wait_count > 1
order by [PageLockPercent] desc
--------------------------------------------------- return the top 10
indexes that have row lock wait
select top 10
db_name() AS
DatabaseName,
[ObjectID]=s.object_id,
object_name(s.object_id) AS TableName,
i.name AS
IndexName,
s.partition_number AS
PartitionName,
row_lock_count,
row_lock_wait_count,
case row_lock_count
when 0 then 0 else cast((100.0 * row_lock_wait_count) / row_lock_count as
numeric(15,2)) end AS RowLockPercent,
row_lock_wait_in_ms,
case row_lock_wait_count when 0
then 0 else cast((1.0 * row_lock_wait_in_ms) / row_lock_wait_count as
numeric(15,2)) end AS AvgRowLockWaitms
from
sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL)
s
left outer join sys.indexes i on i.object_id =
s.object_id and i.index_id = s.index_id
where
objectproperty(s.object_id,'IsUserTable') = 1
and
objectproperty(s.object_id,'IsMSShipped') = 0
and
s.row_lock_wait_count > 1
order by [RowLockPercent]
desc |