locks issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » locks issue

locks issue

Rate Topic

Display Mode

Topic Options

Author

Message

vashi

Posted Tuesday, December 04, 2012 3:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 10:33 AM
Points: 1, Visits: 66

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 ?

Post #1392723

Ramkumar (LivingForSQLServer)

Posted Thursday, December 06, 2012 7:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 08, 2013 8:24 AM
Points: 153, Visits: 563

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

Post #1393516

« Prev Topic | Next Topic »

Permissions