What next after the wait types are gathered

Question

What next after the wait types are gathered

Pyman

SSChampion

Points: 13830
More actions
November 1, 2011 at 8:10 am

#250221

Hi All,
I was able to gather the wait_types base don the waitime, % and running %. I see big numbers here in my output. How should I approach to troubleshooting once I have these stats. I also have my metrics captured.
Any inputs/references will be of great help.
Thanks much.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

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

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

Sapen (11/1/2011)
Hi All,
I was able to gather the wait_types base don the waitime, % and running %. I see big numbers here in my output. How should I approach to troubleshooting once I have these stats. I also have my metrics captured.
Any inputs/references will be of great help.
Thanks much.

Your really not giving us enough information to help you. This is like the manager of a baseball team saying, "I know the batting averages of my players, and I have other stats now what?"

what is it you are trying to accomplish? Is there something about your wait stats that doesnt look right? Is there a performance problem being reported?

Pyman SSChampion Points: 13830 More actions · Answer 2

Sorry for that...Yes, I am having performance issues.

Please find the wait types attached.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

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

Here's the whitepaper on the waits and queues tuning methodology:

http://sqlcat.com/sqlcat/b/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 4

This is another excellent resource: http://msdn.microsoft.com/en-in/library/dd672789.aspx

I really encourage you to read both resources to get a firm understanding of tuning process.

That said, based on the little information you have shared, I can give you some insight, though really I'm speaking in broad generalizations since more information would really be helpful...

The high ASYNC_NETWORK_IO often points to a bottleneck outside of sql server- sometimes an actual network performance proble, but moew frequently the client application itself. For example, if SQL Server returns 100000 rows, and your application does some processing of each row one by one as it comes in, unders some conditions sql server has to wait for the client app to process. Large resultsets can be particularly interesting in troubleshooting the problem. There is a good write up here: http://blogs.msdn.com/b/joesack/archive/2009/01/09/troubleshooting-async-network-io-networkio.aspx

I use the following as part of my tuning toolset:

--which queries in the cache use the most IO

select top 50

(total_logical_reads/execution_count) as avg_logical_reads,

(total_logical_writes/execution_count) as avg_logical_writes,

(total_physical_reads/execution_count) as avg_phys_reads,

Execution_count,

statement_start_offset as stmt_start_offset,

sql_handle,

plan_handle,text

from sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

order by

(total_logical_reads + total_logical_writes) Desc

--which queries in the cache have the longest elapsed time (last elapsed time):

SELECT DISTINCT TOP 20

t.TEXT QueryName,

s.execution_count AS ExecutionCount,

s.max_elapsed_time AS MaxElapsedTime,

ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,

s.creation_time AS LogCreatedOn,

ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

ORDER BY s.max_elapsed_time DESC

GO

--which queries in the cache have the longest elapsed time (total elapsed time):

SELECT DISTINCT TOP 20

t.TEXT QueryName,

s.execution_count AS ExecutionCount,

s.max_elapsed_time AS MaxElapsedTime,

ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,

s.creation_time AS LogCreatedOn,

ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec, s.total_elapsed_time as totaltime

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

--ORDER BY s.max_elapsed_time DESC

order by s.total_elapsed_time desc

-- what is actively waiting on what

SELECT st.text AS [SQL Text],

w.session_id,

w.wait_duration_ms,

w.wait_type, w.resource_address,

w.blocking_session_id,

w.resource_description FROM sys.dm_os_waiting_tasks AS w

INNER JOIN sys.dm_exec_connections AS c ON w.session_id = c.session_id

CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(c.most_recent_sql_handle))

AS st WHERE w.session_id > 50

AND w.wait_duration_ms > 0

Pyman SSChampion Points: 13830 More actions · Answer 5

Thanks for the info NJ-DBA and thanks Gail for the url.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams