Submit a ticketCall us

Quickly Address Software Vulnerabilities
Patch Manager is an intuitive patch management software which extends the capabilities of WSUS and SCCM to not only patch Windows® servers and workstations, and Microsoft® applications, but also other 3rd-party applications which are commonly exploited by hackers. Learn More.

 

Home > Success Center > Database Performance Analyzer (DPA) > Database Performance Analyzer Getting Started Guide > Investigate performance issues > Identify blocking sessions and deadlocks

Identify blocking sessions and deadlocks

Created by Melanie Boyd, last modified by Melanie Boyd on Sep 29, 2016

Views: 56 Votes: 0 Revisions: 4
DPA_GS_Home.png

DPA provides information to help you determine if blocking sessions and deadlocks are affecting performance, and to investigate the root cause of these issues. See the following sections:

Identify blockers causing the longest waits

Are blocking sessions causing performance problems in your environment? Use the Blocking tab to identify the root blockers, find out which SQL statements are being blocked, and determine which blocking sessions are responsible for the longest overall waits. DPA shows the aggregated wait time for each blocker, which helps you focus your tuning efforts on blockers with the largest impact.

To view information about blocking sessions, click a database instance name on the DPA homepage to display the Trends charts. If necessary, click a bar on the chart to drill down to the time period you're interested in. Then click the Blocking tab below any Trends chart to view correlated information about blockers during that time period. The size of each segment in a bar provides a visual indicator of the waits that session caused.

File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/030/gs-blockers-overview.png

Find the last activity of an idle blocker

Idle blockers can be difficult to diagnose because they are currently not performing any activity in the database. To help you find and fix the root problem, use DPA to determine what that session was doing before it became idle.

From the DPA homepage, click the name of a database instance. Click a bar on the Top SQL Statements chart to drill into a day, and then click a bar to drill into an hour. DPA displays information about the type of waits experienced during that hour.

File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/030/gs-idle-blockers-hour.png

Click the Blockers tab above the chart to see a list of the blockers for that time period. You can expand a blocker to see information about the waits it caused. Each idle blocker row has a Find Last Activity link on the right.

File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/030/gs-idle-blockers-list.png

To find out what a blocking session was doing before it went idle, click Find Last Activity. The Find Last Activity dialog tells you when the last activity occurred.

File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/030/gs-idle-blockers-last-activity.png

Click a button to view the activity. The Timeslice tab shows a bar representing the last SQL statement executed by the idle blocker. You can drill in to investigate further. If you clicked Annotate and View Activity, the SQL statement is automatically annotated to make it easy to find in the future.

File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/030/gs-idle-blockers-annotater.png

Investigate deadlocks on SQL Server instances

Deadlocks occur when two sessions have a lock on different resources, and each session needs the other's resource to complete its task. One session (the victim) eventually releases its lock and doesn't complete its task. The transaction time that the victim spent in contention is a good measure of the impact that the deadlock had on performance.

For monitored SQL Server database instances, DPA provides detailed information about deadlocks, including the Victim Impact (how long the deadlocked transactions ran). Click the Deadlocks tab below any Trends chart to see the latest deadlocks for that time period.

File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/030/gs-deadlock-list.png

Click the link in the Time column to open the Deadlock Details page, which includes the following sections:

  • The Deadlock Summary section shows high-level information, including the Total Victim Impact.
  • The Victims section shows details about the queries that were rolled back.
  • The Survivor section shows details about the query that was completed.
  • The Deadlocked Resources section shows the type of lock and the lock mode. Click the links for expert advice.

File:Success_Center/New_Articles/DPA_Getting_Started_Guide/060/030/gs-deadlock-details.png

Last modified
17:05, 29 Sep 2016

Tags

Classifications

Public