5/17/2012 9:36:50 AM
Tom
Posts: 14
|
Today, I was troubleshooting some tempdb contention, and it occurred to me that there doesn't seem to be a way to get a listing of the current waits, similar to Adam Machanic's sp_whoisactive, or a listing of the waits over time.
Am I missing something?
|
|
0
• permalink
|
5/17/2012 11:43:08 AM
Steve Wright
Moderator
Posts: 72
|
Tom,
Performance Advisor provides multiple ways to view wait stats for your server. From the dashboard view all current waits are shown in live mode in graphical format, grouped into friendly classes. Irrelevant wait types are filtered for efficient analysis. If you mouse over any of the charts, a pop up provides the native wait types for that class and the length of that particular wait type. At the query level, the current wait type for running Top SQL is shown under the Top SQL tab.
There are several methods to review historical wait stats as well. Just as in live mode, the Performance Advisor dashboard provides complete historical visibility into these metrics. In this view they are further distilled into categories, but again you can mouse over the chart for a drill down to the specific native wait types. Additionally our SQL Server Wait Stats Analysis report, found under the Reports > Performance Advisor > Performance menu in the SQL Sentry Client, provides a breakdown of your wait stats over any specified timeframe at the Category, Class, and native Wait Type levels. This includes a further distinction between resource and signal (CPU) waits just as in Sample mode on the dashboard.
That said, whenever discussing wait stats I always feel compelled to add that, while wait stats are a good resource to begin investigating any performance bottlenecks, they seldom provide the complete picture. At times they can even lead you down the wrong path. That's why wait stats are just part of the comprehensive view SQL Sentry provides into your server's performance. It's always important to distinguish between "What are you waiting on?" vs. "Why are you waiting?" I've written a blog post regarding this topic at http://steve.blogs.sqlsentry.net/2011/04/wait-stats-and-rest-of-story.html.
Let us know if you have any other questions.
--
Steve Wright
SQL Sentry Support
|
|
0
• permalink
|
7/12/2012 7:48:03 AM
EdLawton
Posts: 2
|
Hello
Can I use SQL Sentry to perform data collection and analysis for tempDb contention such as this:
With Tasks As (Select session_id, wait_type, wait_duration_ms, blocking_session_id, resource_description, PageID = Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) From sys.dm_os_waiting_tasks Where wait_type Like 'PAGE%LATCH_%' And resource_description Like '2:%') Select session_id, wait_type, wait_duration_ms, blocking_session_id, resource_description, ResourceType = Case When PageID = 1 Or PageID % 8088 = 0 Then 'Is PFS Page' When PageID = 2 Or PageID % 511232 = 0 Then 'Is GAM Page' When PageID = 3 Or (PageID - 1) % 511232 = 0 Then 'Is SGAM Page' Else 'Is Not PFS, GAM, or SGAM page' End From Tasks;
As described here:
http://www.sqlsoldier.com/wp/sqlserver/breakingdowntempdbcontentionpart2
Thanks
Ed
|
|
0
• permalink
|
7/12/2012 12:09:24 PM
GregGonzalez
Moderator
Posts: 146
|
Hi Ed,
You can generally get to the root of most tempdb (or other db) page latch contention issues by dragging to highlight the spike in page latch waits on the PA dashboard in history view, then right-click and Jump To->Disk Activity to ascertain which databases and files are experiencing the high latency and/or volume during the spike.
You can then Jump To->Top SQL or Blocking SQL to view only the queries or blocks captured at that time. Blocking SQL shows the wait types, resources, databases, etc. Note that depending on how quickly the associated queries are running, if they aren't being picked up here you may need to adjust down the Minimum Block Duration under Blocking SQL Source settings, which can be done globally or at the server level. The block detection query runs every 15 seconds by default.
On either Top SQL or Blocking SQL click "View" to open the query plan along with captured statements. This should bring to light any queries that may be using tempdb either explicitly via temp tables, and/or operations that make use of tempdb such as hash joins and sorts.
Further:
- You can Jump To->Query Plans to view the plans, then sort desc by Sort or Hash Match to find any plans with those op types that may spill, although bear in mind this may not be helpful for allocation-related contention scenarios.
- If the queries experiencing contention are actively running and exceed the Top SQL filter threshold (set under Top SQL Source settings), you will see them along with the wait type and resource in the "Running Queries" section, which is refreshed every 5 seconds by default.
- The Blocking by SQL Server report has a "Top Wait Resources" section which shows the top 10 wait types, databases, applications, etc. over any time frame, for one or more servers.
- If you want even more details, you can query our blocking tables directly using a query similar to the one you referenced, eg:
SELECT
ESC.ObjectName AS ServerName
,BCD.WaitText
,BCD.WaitResource
,BCD.WaitResourceFriendly
,BCD.DatabaseName
,BCD.HostName
,BCD.ProgramName
,BCD.LoginName
,BCD.CommandText
,BCD.ObjectName
,SUM(BCD.WaitTime) AS TotalWaitTime
FROM BlockChainDetail AS BCD
INNER JOIN BlockChain AS BC
ON BCD.BlockChainID = BC.ID
INNER JOIN EventSourceConnection AS ESC
ON BC.EventSourceConnectionID = ESC.ID
GROUP BY ESC.ObjectName, BCD.WaitText, BCD.WaitResource, BCD.WaitResourceFriendly, BCD.DatabaseName, BCD.HostName, BCD.ProgramName, BCD.LoginName, BCD.CommandText, BCD.ObjectName
HAVING (BCD.WaitText LIKE N'page%latch%') AND (BCD.WaitResource LIKE N'2:%')
ORDER BY SUM(BCD.WaitTime) DESC
I hope this is helpful,
-greg
|
|
0
• permalink
|