Top 10 Wait State Issues Reporter

Question

Post reply

Top 10 Wait State Issues Reporter

Rudy Panigas

SSChampion

Points: 10702
More actions
November 21, 2012 at 10:45 pm

#270430

Comments posted to this topic are about the item Top 10 Wait State Issues Reporter
Rudy

Viewing 15 posts - 1 through 15 (of 34 total)

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

Seed Vicious SSC Veteran Points: 297 More actions · Answer 1

Hi Rudy, excelent script!

but, you have a little bug at the en of the second comment... /* instead */ ;P

Rudy Panigas SSChampion Points: 10702 More actions · Answer 2

Thanks for the comments! 🙂

Could please tell me where the problem is? i don't see the problem.

Thanks,

Rudy

Rudy Panigas SSChampion Points: 10702 More actions · Answer 3

Hello again,

I found the problem. Change the /* to */ on the second comment at the end of the line. If not the complete code will be commented out.

Sorry for the type-o.

Thanks,

Rudy

Arthur Kirchner Ten Centuries Points: 1074 More actions · Answer 4

I am also delighted, thanks a lot!

here a little correction

'LOCK - Waiting to acquire a Schema Modify lock' WHERE dbo.[#results].[wait_type] = 'LCK_M_SCH_S'

... should be Share.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 5

Arthur Kirchner (11/22/2012)
I am also delighted, thanks a lot!
here a little correction
'LOCK - Waiting to acquire a Schema Modify lock' WHERE dbo.[#results].[wait_type] = 'LCK_M_SCH_S'
... should be Share.

Thanks for looking at the script. The LCK_M_SCH_S section is MODIFY not a SHARE as per Microsoft http://support.microsoft.com/kb/822101 The "_M_" stands for MODIFY.

Please let me know where you have found your information from.

Again thanks for your help,

Rudy

Arthur Kirchner Ten Centuries Points: 1074 More actions · Answer 6

I am sorry,

I trusted Pinal Dave on this one, without crosschecking MS:

http://blog.sqlauthority.com/2011/02/15/sql-server-lck_m_xxx-wait-type-day-15-of-28/

who wrote

"LCK_M_SCH_S - Occurs when a task is waiting to acquire a Schema Share lock."

an correctly it's

SCH_S <=> Schema stability

SCH_M <=> Schema modification

Rudy Panigas SSChampion Points: 10702 More actions · Answer 7

Arthur Kirchner (11/22/2012)
I am sorry,
I trusted Pinal Dave on this one, without crosschecking MS:
http://blog.sqlauthority.com/2011/02/15/sql-server-lck_m_xxx-wait-type-day-15-of-28/
who wrote
"LCK_M_SCH_S - Occurs when a task is waiting to acquire a Schema Share lock."
an correctly it's
SCH_S <=> Schema stability
SCH_M <=> Schema modification

Hi Arthur,

The reason I wrote this script is hopefully clearify the meaning of the wait stats as it can be quite confussing. So I just looked at Microsoft for answers but they too have has some errors in their discriptions too. So you (and Dave) could be right. If you find more information on this, please let me know as I want this script to help DBAs not confuse them.

Really don't understand why this topic has to be so confusing. :hehe:

Big thanks!

Rudy

Caruncles Hall of Fame Points: 3299 More actions · Answer 8

I'm not in the same league with you guys as far as what this all means. I apologize for any dumb questions, but is this reporting CURRENT activities or is it a history? If a history, over what time period?

Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."

Rudy Panigas SSChampion Points: 10702 More actions · Answer 9

Caruncles (11/22/2012)
I'm not in the same league with you guys as far as what this all means. I apologize for any dumb questions, but is this reporting CURRENT activities or is it a history? If a history, over what time period?

Hey no apologies required!! We are all here to learn and help when we can 🙂

As for reporting (current or historial), see below as per Microsoft (http://msdn.microsoft.com/en-us/library/ms179984(v=sql.105).aspx)

"Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches"

But threads had to have executed so in a sense it is historical.

Hope this help,

Rudy

PS. Please continue to ask question regardless as we all learn from them 🙂

Rudy

Caruncles Hall of Fame Points: 3299 More actions · Answer 10

Thanx Rudy! I see this as being helpful for us. I've been troubleshooting some timing-out errors lately. We're in our peak sales period so our system is getting hammered.

Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."

Tony Trus Say Hey Kid Points: 684 More actions · Answer 11

Thanks for the script. I had been using Glenn Berry's one from a year ago and I like how this one does some nice data manipulation so its neat and organized with descriptions.

On a side note, anybody know the best way to keep formatting when doing a copy of the code from this site? When I select this wait script and paste it into notepad locally (don't have sql installed on my family machine) the formatting turns to jibberish. My current workaround is to first paste to Word and then copy and paste to notepad and that resolves. Sure would like an easier route. Cheers and happy Thanksgiving to all the folks here in USA

Rudy Panigas SSChampion Points: 10702 More actions · Answer 12

Tony Trus (11/22/2012)
Thanks for the script. I had been using Glenn Berry's one from a year ago and I like how this one does some nice data manipulation so its neat and organized with descriptions.
On a side note, anybody know the best way to keep formatting when doing a copy of the code from this site? When I select this wait script and paste it into notepad locally (don't have sql installed on my family machine) the formatting turns to jibberish. My current workaround is to first paste to Word and then copy and paste to notepad and that resolves. Sure would like an easier route. Cheers and happy Thanksgiving to all the folks here in USA

Thanks for the comments.

As for the copy /paste issues, try using a different web browser.

Rudy

Tony Trus Say Hey Kid Points: 684 More actions · Answer 13

Thanks, Chrome was able to not clobber formatted so I used that instead of IE.

Something else I just noticed. I get quite different results from Glenn's version versus this. I know the metrics are measured differently (Glenn's is in percentage) however it did make me wonder. For what its worth I have not dug into the logic. His code was a bit shorter and looked like this:

WITH Waits AS

(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

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

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'))

SELECT W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

Rudy Panigas SSChampion Points: 10702 More actions · Answer 14

Hi Tony,

The code you are showing will produce percentages. My code shows information directly from SQL server along with the comments.

I didn't change the numbers to percentage yet but could be included in my next version.

Thanks,

Rudy