The SQL Activity drilldown provides access to charts and grids that show both current and recent activity details for the SQL Server.
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.
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:
Shows the batch of SQL statements last executed or currently executing by the selected session.)
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.
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. | ||||||||||||||||||||||||||||||||
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 more information, see Spotlight diagnostic user groups.
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.
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.
You can use 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 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.
The best way to see deadlocks in Spotlight is to use the Playback Database (Playback) 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. To filter the data displayed Click Change Filter to refine the display of data. Click Add.
You can edit or delete a filter by selecting it in the filter list and clicking the appropriate button. To turn off filtering, clear the Filter results check box.
| ||||||||||||||||||||||||||||||||
Custom Counters | Custom Counters page |
Shows data collected for any custom counters that have been configured. Shows data collected for any SQL Server and Windows custom counters that have been configured. Data collected for any custom counters that have been configured is shown in the chart and grids.
See also Configure | Custom Counters and Add alarms to custom counters. | ||||||||||||||||||||||||||||||||
Diagnose workload issues by displaying SQL statements that match particular criteria. | Top SQL Statements page |
Shows the top SQL statements according to the criteria you have set. Select a statement in the grid to see the complete SQL statement and the query plan in XML. The columns in the Top SQL Statements grid correspond to the data returned by sys.dm_exec_query_stats. For more information on sys.dm_exec_query_stats, see the Transact-SQL Reference section in Microsoft SQL Server Books Online. To filter the results
Shows the SQL statement of the currently selected row.
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.
|
|
TIP: For more information on Spotlight charts and grids see Spotlight charts and Spotlight grids |
|
Note: For information on unknown values or missing values or too many "0" values, see Data display and collection problems - SQL Server connection. |
© 2022 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy