Investigate where waits are occurring and what is being waited for

When troubleshooting SQL Server performance issues, one area you can focus your investigation on is waits. Long wait times can be an indicator of performance bottlenecks so identifying where waits are occurring may assist you in your tuning efforts.

Spotlight not only provides an easy way to see if your SQL server has a problem with waits but also identifies on which particular resource waits are occurring. Spotlight takes the raw data from SQL Server and automatically calculates the rate of wait over time so you have a more current perspective.

How to open the Wait Statistics drilldown

From the Spotlight Client

  1. Select the connection from the left Connections pane.
  2. Click Monitor | Wait Statistics from the ribbon.
    Wait Statistics drilldown for SQL Server

About the Wait Statistics drilldown

Wait Time Trend chart

Shows the total wait time in milliseconds broken down for the for the following wait types:

  • Wait (Other)
  • Wait (Log)
  • Wait (CLR)
  • Wait (IO)
  • Wait (Network)
  • Wait (Memory)
  • Wait (Latch)
  • Wait (Remote Provider)
  • Wait (CPU)
  • Wait (Lock)
  • Wait (XTP)

Use this chart to look for trends in wait types over time.

Waits Details grid

Shows detailed wait statistics for the SQL Server instance currently being monitored. It shows all wait types and breaks down the statistics into signal time and resource wait time. By default, the Waits Detail grid is sorted by Wait Time Rate (ms/s) which allows you to immediately see which wait types are being waited on right now.