How to open the SQL Activity drilldown
From the Spotlight Client
- Select the connection from the left Connections pane.
- Click Monitor | SQL Activity from the ribbon.
About the SQL Activity drilldown
Sessions page
Investigate SQL sessions including session details and locks.
Session grid
Shows all SQL Server sessions, each row representing a single session. Many applications create multiple connections to SQL Server. When there are more than 2000 connections only those connections that are active are shown.
Shows those sessions that are currently running when Active Sessions is selected. Shows system sessions when System Sessions is selected.
Tips for using the Session grid in the Spotlight Client
- Some columns may be hidden by default. To view them, right-click the grid headings and select Organize Columns.
- To close a SQL Server session, right click and select Kill this session. This option is available only to members of the Spotlight Diagnostic Administrators group. For more information, see Spotlight diagnostic user groups.
- The screen refreshes automatically when set to
Resume Refresh. When you wish the screen data to pause as is / freeze, click the icon to Pause Refresh. Click the icon again to Resume Refresh. - The status column of the grid is color coded for readability.
- 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.
SQL
Shows the batch of SQL statements that last ran or currently running by the selected item.
From this window, you can right-click to:
| Copy | Copy the SQL text contained in the window to the Windows clipboard. |
| Optimize SQL | Use SQL Optimizer (if installed) to tune the displayed SQL. |
| Open in External Editor | Open the SQL text in an external editor. The editor set as preferred for .SQL files on your computer is used. |
Plan
The Optimizer Plan Visualisation control provides an interactive and intuitive way to view the selected statement and query execution plan.
Session Trace
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.
- 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 runs some SQL. The grid is populated with the SQL that occurs from the time the Session Trace page is opened.
- You can use 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.
- 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.
Session Locks
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.
Session Details
Shows CPU and I/O information about the session selected in the Sessions grid.
Summary page
Investigate SQL Server activity.
Access Methods chart
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.
SQL Server I/O chart
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.
Cache Hit Rates chart
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.
CPU Utilization chart
Shows the amount of CPU being used by SQL Server against the total being used by all processes in Windows.
Call Rates chart
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.
Connected Sessions chart
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 page
Investigate locks and requests that are currently occurring on the SQL Server instance.
Locks grid
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 end 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.
Locks Type chart
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.
Latches chart
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.
Blocking page
Blocking in SQL Server occurs when a session that was performing some task is unable to progress because it must wait on a resource that is currently being used by another session. The resource that is being waited on can be either a physical structure like a lock on a table or an internal SQL Server resource such as a latch.
Excessive blocking can be a major cause of poor application performance since it reduces the throughput of the system. Often a user of an application does not realize that they are waiting on a resource held by another user. From their point of view, it often seems like their application has stopped responding.
When diagnosing blocking, you want to start your investigation by answering the following questions:
- Who is waiting on what?
- How long have they been waiting?
- What SQL was running while they were waiting?
Spotlight not only alerts you to blocking issues in your database but also helps you answer the above questions so you can quickly resolve the problem causing the blocking.
The Blocking drilldown shows details about the current blocks and in particular, answers the questions stated above.
Blocking grid
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 makes it easy to see which session is causing the blocking. In addition, the resource on which the sessions are waiting is displayed. The time spent waiting and the SQL that last ran are also displayed.
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 screen refreshes automatically. To pause Auto Refresh click . Click again to re-start auto refresh.
SQL Server sessions can be closed by clicking Kill Session. This will end the entire connection (SPID), not just the lock you selected.
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.
Number of Blocked Processes chart
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 page
Investigate Deadlocks.
Deadlock list
Shows the date of the deadlock and the SPID of the process that SQL Server stopped 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 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.
I/O by File page
Investigate current I/O statistics for each SQL Server file.
I/O by File grid
Shows current SQL Server I/O statistics for each SQL Server database file.
I/O by File chart
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.
Custom Counters page
Investigate Custom Counters.
Custom Counters chart
Shows data collected for any custom counters that have been configured.
Custom Counters grids
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. Note that the chart shows only those counters that return numeric values.
You can see Custom Counter data over a selected time range. See the Custom Counters report in Spotlight Reports for more information.
Query Execution Statistics page
Diagnose workload issues by displaying SQL statements that match particular criteria.
Query Execution Statistics grid
Shows the top query execution statistics according to the criteria you have set.
Select a statement in the grid to see an interactive view of the query execution plan.
The columns in the Query Execution Statistics 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
Click Query Execution Statistics.
Select values as appropriate.
| Option | Description |
|---|---|
| Statement type | Select how you want to filter the SQL statements. |
| Where index name is | Specify the name of the index. This option is available only when Show SQL statements using index is selected. |
| In the top n | Select how many statements to sample. |
| By | Specify the criteria that Spotlight should use to determine the top statements. |
When configured correctly, the filter should read like a sentence. For example, “Show all SQL statements using SCAN operators in the top 50 SQL statements by average worker time.”
Note that to reduce the load when querying the SQL Server, Spotlight determines the top statements using the ‘by’ criteria before applying the other filters. For example, if you want to find statements using a particular index, and the top n filter was set to top 50 statements by average worker time, Spotlight first finds the top 50 statements by average worker time and then looks for the use of the specified index within this top 50.
Example
| Filter | Description | Use this filter… |
|---|---|---|
| Show all SQL statements | Shows all SQL statements in the top n statements | To see all top n statements by a particular criteria. |
| Show SQL statements using parallelism | Shows SQL Statements in the top n using parallelism | If you have high amounts of CX_PACKET waits and want to see which statements have parallel plans. |
| Show SQL statements using index | Shows SQL statements in the top n using the index entered in the text box | If you want to know which statements use a particular index. |
| Show SQL statements using SCAN operators | Shows SQL statements in the top n using SCAN operators | If you have low page life expectancy or high I/O and want to see which statements are doing scans. |
| Show SQL statements using large number of rows | Shows SQL statements in the top n using large number of rows | If you have high I/O and want to see which statements are using a large number of rows. |