Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

locks issue Expand / Collapse
Author
Message
Posted Tuesday, December 04, 2012 3:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 3, Visits: 164
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
Posted Thursday, December 06, 2012 7:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:29 AM
Points: 153, Visits: 585
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 »

Add to briefcase

Permissions Expand / Collapse