Home > Performence Tuning > Steps in diagnosing and resolving latch contention issues in multi-CPU systems

Steps in diagnosing and resolving latch contention issues in multi-CPU systems


Contention is the word that we need to concentrate in resolving the performance issue, page latches contention is common in multi-CPU systems due to the nature of when multiple threads concurrently attempt to acquire incompatible latches to the same in-memory structure. In general there is a miss-representation that latches means locks, but not both are different to each other. Latch is used to provide consistency of in-memory structures, this means they are automatic on the SQL Server when multi-CPU system is used to allow maximum concurrency and provide maximum performance.

Script To Analyze current wait buffer latches for both PAGEIOLATCH_ or PAGELATCH_ types:

SELECT

oswt.session_id, oswt.wait_type , er.last_wait_type AS last_wait_type , oswt.wait_duration_ms , oswt.blocking_session_id, oswt.blocking_exec_context_id, resource_description FROM sys.dm_os_waiting_tasks oswt JOIN sys.dm_exec_sessions es ON oswt.session_id = es.session_id JOIN sys.dm_exec_requests er ON oswt.session_id = er.session_id WHERE es.is_user_process = 1 AND oswt.wait_type <> ‘SLEEP_TASK’ ORDER BY oswt.wait_duration_ms desc

Advertisements
Categories: Performence Tuning
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: