SQL Average Lock Wait Time is Elevated and is Critica

Question

Post reply

SQL Average Lock Wait Time is Elevated and is Critica

Diyas

SSCarpal Tunnel

Points: 4851
More actions
September 15, 2011 at 8:06 am

#390415

What is the solution for this problem
SQL Average Lock Wait Time is Elevated and is Critical

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

What does this return?

/*

Next query by Paul Randal http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx

*/

WITH Waits AS

(SELECT

wait_type,

wait_time_ms / 1000.0 AS WaitS,

(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,

signal_wait_time_ms / 1000.0 AS SignalS,

waiting_tasks_count AS WaitCount,

100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN (

'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',

'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',

'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',

'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',

'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',

'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR')

)

SELECT

W1.wait_type AS WaitType,

CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,

CAST (W1.WaitS * 1000 / W1.WaitCount AS DECIMAL(14, 2)) AS AvgWait_MS,

CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,

CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,

W1.WaitCount AS WaitCount,

CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.RowNum <= W1.RowNum

GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage

HAVING SUM (W2.Percentage) - W1.Percentage < 97 -- percentage threshold

AND W1.WaitCount > 0;

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Edit: Removed. I'm not willing to help people who have no manners.

Div: This is a volunteer site, we don't owe you anything. Telling someone to 'shut their mouth' is hardly professional and certainly not good manners.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This is worth noting to all who may have wanted to help (from his cross-post).

Div Goud (9/15/2011)
hi nin
give solution to the respective que otherwise shutdown to your mouth

Diyas SSCarpal Tunnel Points: 4851 More actions · Answer 4

Ninja's_RGR'us (9/15/2011)
This is worth noting to all who may have wanted to help (from his cross-post).
Div Goud (9/15/2011)
hi nin
give solution to the respective que otherwise shutdown to your mouth

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

Closing this thread as a duplicate.

Please do not attack other members when they post. Regardless of your time critical situation, this is a volunteer forum. If you do not like someone's post, please report it or ignore it.