December 4, 2012 at 3:10 pm
DESCRIPTION:The SQL Server performance counter 'Lock Waits/sec' (instance '_Total') of object 'SQLServer:Locks' is now above the threshold of 0.70 (the current value is 0.80).
what should i do ?
December 6, 2012 at 7:19 am
Vashi,
Need more inputs to understand the issue better (Hardware, SQL Configurations, DB Size, OLTP load, ETL etc)
Is this value exceeds consistantly or only specific time?
Most probably this is side effect of performance issues in queries/stored procs.
Suggest you to seek DBAs/Tuning specialists to for detailed analysis.
Run Glenn Berry's dmvs to understand server/db/object level pain points
here are some sample lock specific queries which will help you to narrow down the painful database and objects.
select db_name(database_id),
sum(row_lock_count)
from sys.dm_db_index_operational_stats(null,null,null,null)
group by db_name(database_id)
order by sum(row_lock_count) desc
select db_name(database_id),
sum(page_lock_count)
from sys.dm_db_index_operational_stats(null,null,null,null)
group by db_name(database_id)
order by sum(page_lock_count) desc
select db_name(database_id),
object_name(object_id),
row_lock_count,
row_lock_wait_count,
row_lock_wait_in_ms,
page_lock_count,
page_lock_wait_count,
page_lock_wait_in_ms
from sys.dm_db_index_operational_stats(db_id(<database_name>),null,null,null)
order by page_lock_count desc
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply