November 1, 2011 at 8:10 am
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
November 1, 2011 at 8:22 am
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?
November 1, 2011 at 8:45 am
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
November 1, 2011 at 8:49 am
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
November 1, 2011 at 11:28 am
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
November 1, 2011 at 1:11 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply