locks issue

  • 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 ?

  • 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

    http://www.sqlservercentral.com/blogs/glennberry/2011/10/18/october-2011-sql-server-2008-diagnostic-information-queries/

    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