The SQL Activity drilldown provides access to charts and grids that show both current and recent activity details for the server you are diagnosing.
To see the SQL Activity drilldown
Click Monitor | SQL Activity.
To investigate...
Look at the...
SQL sessions including session details and locks
Sessions page
Shows all SQL Server sessions, each row representing a single session. Note that many applications create multiple connections to SQL Server.
In SQL Server 2005 or later, to show only those sessions that are currently executing, click Active only.
In SQL Server 2000, clicking Active only shows sessions that:
- Do not have a status of sleeping, background or dormant.
- Are blocked.
- Have a command type of "WAITFOR".
Tips:
- Some columns may be hidden by default. To view them, right-click the grid headings and select Organize Columns.
SQL Server sessions can be closed. Select the session and click Kill this session. This option is available only to members of the Spotlight Diagnostic Administrators group.
- You can view more details for a session by clicking it in this grid. Additional pages are then displayed in the lower half of the drilldown.
- The data displayed in this grid can be Filtered. Filtering is the method Spotlight uses to restrict displays to a manageable or relevant set of data. You can view or change the filter by right-clicking the grid and choosing View/Edit Filter.
- To find a particular session, right-click and select Find.
- Data in the Last Wait Type and Last Wait Resource columns is reported directly from SQL Server and, for performance reasons, is not resolved to actual resource names during normal data collection. To resolve the data to a more readable form, click the
button.
Shows the batch of SQL statements last executed or currently executing by the selected session.)
Tips:
Use Quest SQL Optimizer (if installed) to tune the displayed SQL by clicking Optimize SQL.
- You can copy SQL text from the SQL Statement page by right-clicking and selecting the appropriate option from the shortcut menu.
Note: This feature is not available for SQL Server 2000.
Shows the query execution plan for the selected session in XML. To open the plan in SQL Server Management Studio (if installed) and view the query execution plan in graphical format, click View Plan.
Uses SQL Server trace functionality to show the SQL events and activity generated by the selected session.
For performance reasons, Spotlight limits the number of records that can be viewed in this grid to 500. When the limit is reached, the oldest records are discarded as new ones are collected.
Tips:
- Opening the Session Trace page initiates a new session that does a trace on the selected session. The grid displays No Data until such time that the session executes some SQL. The grid is populated with the SQL that occurs from the time the Session Trace page is opened.
- You can use Quest SQL Optimizer (if installed) to tune the SQL of the selected session. To do this, select a row in the grid and click Optimize SQL.
Note: To use the Session Trace functionality the version of SQL Server Management Tools installed on the Diagnostic Server is required to match the latest version of SQL Server monitored. When monitoring SQL Server 2012, SQL Server 2012 Management Tools are required.
Shows all locks held or requested by the selected session.
If a lock is one on which this session is waiting, the Status column will show it as being blocked. This column also highlights locks that are blocking other sessions.
Shows CPU and I/O information about the session selected in the Sessions grid.
SQL Server activity
Summary page
Shows various statistics relating to how SQL Server data is being accessed and updated. These counters are collected from the SQL Server Access Methods Manager.
The following counters can be displayed by clicking the arrow next to the chart name:
Page Splits - This shows the rate at which pages are being split into two because there is not enough free space in the page to accommodate an inserted or updated record. A high page-split rate can indicate you need to rebuild indexes, possibly with a higher Padding Factor.
Page Allocations - Shows the rate at which pages are being allocated to tables or indexes. Indicates how fast tables are expanding.
Page Deallocations - Shows the rate at which pages are being deallocated from tables or indexes. Indicates how fast tables are shrinking.
Freespace Scans - The number of scans initiated to search for free space to insert a new record.
Forwarded Records - The rate at which SQL Server is retrieving forwarded records. When a row in a table that does not have a clustering index is updated - and if the modified row no longer fits on the old page - SQL Server moves the row to a new page and leaves a forwarding pointer in the old page. This means the non-clustered indexes do not have to be modified to reflect the new row location, but will cause subsequent retrieval of this row via non-clustered indexes to require an extra I/O. A high Forwarded Records rate can indicate you need to reorganize your tables (unload/reload) or define clustered indexes.
Ghosted Records - The rate at which SQL Server encounters Ghosted Records during scans. When a record is deleted from a table, SQL Server improves concurrency by not physically removing the row from index Leaf levels. Instead the row is simply marked as deleted (ghosted). At some later point, a housekeeping process asynchronously removes these rows from the leaf level. Until the records are removed, SQL Server must skip the ghosted records during leaf-level scans.
Table Lock Escalations - The rate at which locks are being escalated to the table level.
Shows the number of pages physically read from, and written to, disk by SQL Server.
SQL Server keeps a copy of its most recently used database pages in the Buffer Cache. When a connection needs to reference a database page, SQL Server performs a Logical I/O. This involves checking the Buffer Cache to see if the requested page is already in memory. If the page is not already in there, it will be read from disk, causing a Physical I/O.
Shows the Hit Rates for the main SQL Server caches.
A Hit Rate indicates how much work (I/O or compilations) is being saved by caching information in memory.
Spotlight calculates its hit rates using a differential sampling method. Unlike most SQL Server monitors, it is not reporting the average hit rate since SQL Server started.
Shows the amount of CPU being used by SQL Server against the total being used by all processes in Windows.
Shows the rate at which various events are occurring in SQL Server.
This information will give an idea of how busy the SQL Server is at any time.
Shows SQL Server session information.
The Connected Sessions chart contains an area graph showing the number of SQL Server sessions over time. If you have a non-zero "User Connections" parameter set on the server being monitored, the Y-axis is scaled to the value you specified, which allows you to see how close you are to this limit.
A Session is a single connection to SQL Server. Some applications establish multiple sessions to SQL Server. If there are multiple applications running on a target computer that are all communicating with SQL Server, they will also have created multiple SQL Server sessions.
Where waits are occurring and what is being waited for
Wait Statistics page
Shows the total wait time in milliseconds broken down for the for the following wait types:
- Wait (CLR)
- Wait (I/O)
- Wait (LATCH)
- Wait (LOCK)
- Wait (MISC)
- Wait (OTHER)
- Wait (WAITFOR)
Use this chart to look for trends in wait types over time.
Shows detailed wait statistics for the SQL Server instance currently being monitored.
Locks and requests that are currently occurring on the SQL Server instance
Locks page
Shows information about all locks currently held or requested. You can view session details by selecting an entry in the grid and clicking View Session Details.
The Locks grid is not refreshed automatically. To refresh the Locks grid, click Refresh.
A SQL Server session can be closed by selecting it and clicking Kill Session. This will terminate the entire connection (SPID), not just the lock you selected. This option is available only to members of the Spotlight Diagnostic Administrators group.
For performance reasons, Spotlight limits the number of records that can be viewed in this grid. By default, only 1000 locks will be displayed. If there are more locks than this, then no information will be displayed.
The data displayed in this grid can be Filtered. Filtering is the method Spotlight uses to restrict displays to a manageable or relevant set of data. You can view or change the filter for this grid by clicking Change Filter.
Note: Selecting any of the Locks filtering options means that the selected locks are included in the grid, along with other types of locks. It does not mean that only the selected locks are shown in the Locks grid.
Shows the following lock statistics broken down by the lock type:
Waits per Second - The rate at which lock requests cannot be satisfied immediately and require the caller to wait before being granted the lock.
Average Wait Time - The average wait time (in milliseconds) for each lock request that resulted in a wait.
Lock Requests per Second - The number of lock requests and lock conversion requests per second.
Timeouts per Second - The number of lock timeouts per second. By default, SQL Server never times out locks (they will wait forever). However many applications issue a SET LOCK_TIMEOUT statement to cause SQL Server to timeout their locks after the specified interval. This statistic shows how often these timeouts are being exceeded. The Details sub-page of the Sessions drilldown can be used to view the Lock Timeout (@@LOCK_TIMEOUT) values for each connection.
Deadlocks per Second - The number of lock requests per second that resulted in a deadlock. A deadlock occurs when session A requests a lock that conflicts with a lock already held by session B, and then session B requests a lock that conflicts with one session A holds. SQL Server chooses one session as the deadlock victim, cancels that connection and rolls back any updates they had done.
Tip: To switch between lock statistics, click the arrow next to the chart name.
Shows statistics on Latch requests.
A Latch is like a mini-lock that is used internally by SQL Server to serialize access to certain resources. Latches do not participate in transactions and are typically held for very short durations.
Current lock conflicts
Blocking page
Shows all connections that are currently either waiting on locks held by others or are causing others to wait, highlighting who is waiting on whom and the resources involved. You can view session details by selecting an entry in the grid and clicking View Session Details.
The hierarchy in this tree diagram represents the blocking chains. It shows who is blocking whom and makes it easy to see who is at the head of the chain and is the root cause of all the blocking.
SQL Server sessions can be closed by clicking Kill Session. This will terminate the entire connection (SPID), not just the lock you selected.
The tree will show one entry for each session that is blocked, and one for each session that is blocking another but is not blocked itself. Sessions at the top of the tree (those that do not have a "parent" in the tree) are at the head of the blocking chain.
The Blocking grid is not refreshed automatically. To refresh the Blocking grid, click Refresh.
Note: You can use Quest SQL Optimizer (if installed) to tune the SQL for the selected session. To do this, select a row in the grid and click Optimize SQL.
Shows the number of SQL Server sessions that were involved in blocks over time. See how often and for how long there were lock conflicts in SQL Server.
It shows the number of sessions that were waiting on locks held by others (Blocked Processes).
It also shows the number of sessions that were not blocked, but were blocking others. These are termed Lead Blockers and correspond to sessions in the Blocking grid that do not have a parent in the Blocking chain (at level 1 in the tree).
Deadlocks
Deadlocks page
Note: This feature is not available for SQL Server 2000.
Shows the date of the deadlock and the SPID of the process that SQL Server terminated to resolve the deadlock.
Select an item in the list to see information about the deadlock in XML. From here you can view a graphical representation of the deadlock in SQL Server Management Studio (if installed), by clicking View Plan.
Tip: The best way to see deadlocks in Spotlight is to use the History Browser to view the Deadlocks list at the time the deadlock occurred. As SQL Server resolves deadlocks quickly, they appear for only a short time when Spotlight is in live mode.
Current I/O statistics for each SQL Server file
I/O by File page
Shows current SQL Server I/O statistics for each SQL Server database file.
Shows SQL Server I/O statistics for each SQL Server database file.
The following counters can be displayed by clicking the arrow next to the chart name:
Wait per I/O - The wait time per I/O (in milliseconds) for the selected database file.
Current I/O rate - The current rate at which SQL Server is performing physical read and write operations to this file (Reads rate plus Writes rate).
Current Read Rate - The current rate at which SQL Server is performing physical read operations from this file.
Current Write Rate - The current rate at which SQL Server is performing physical write operations to this file.
Total I/O - The number of kilobytes read from or written to this file since SQL Server started (KB Read plus KB Written).
Total Wait Time - The total number of milliseconds that SQL Server has spent waiting for I/O operations on this file since SQL Server started.
Current Wait Time - The number of milliseconds that SQL Server has spent waiting for I/O operations on this file since the last time data for this page was collected.
Expensive SQL
SQL Analysis page
Shows SQL being executed, in real time, that conforms to the performance criteria configured on the SQL Analysis Dialog.
You can use the SQL Analysis grid when load testing in a testing environment. For example, if you are trying to answer the question “I am simulating application workload in my test environment and I want to look at a breakdown of SQL statements in real-time. I am interested in what SQL statements are consuming the most average CPU right now.”, you would want to look at an aggregate of SQL statement executions (workload) in real-time. The SQL Analysis grid in the SQL Activity drilldown allows you to do exactly that.
Tips:
- To identify SQL that consumed the most CPU, sort the grid by Total CPU
- To identify the average CPU consumed by a particular statement, sort the grid by Average CPU
- To identify SQL that was executed the most, sort the grid by Execution Count
- To identify SQL with the highest logical I/O, sort the grid by Average Reads
- Use filters to refine the data shown in the grid. See "Filter SQL Analysis Data" (page 1) for more information.
- Values in the "Duration" columns are in microseconds (usec).
- Not all columns are available for all event classes. Some columns will be blank for certain event classes.
- Some columns are hidden by default. To view hidden columns, right-click Organize Columns.
- To wrap a column onto multiple lines, right-click the data content of the grid and select Properties | Options. Turning on the word wrap option may degrade the performance of Spotlight.
- If Quest SQL Optimizer is installed, you can use it to tune non-conforming SQL that has been identified. To do this, select a row in the grid and click Optimize SQL.
Results are cleared when you leave the SQL Analysis page. Click Clear Grid to clear the grid manually.
Custom Counters page
Shows data collected for any custom counters that have been configured.
Note: The chart shows only those counters that return numeric values.
Shows data collected for any SQL Server and Windows custom counters that have been configured.
Tip: When looking at charts, click an item in the legend to highlight its series (line) in the chart.
Session grid