Spotlight on SQL Server Enterprise 11.7 - Release Notes

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:

• Do not have a status of sleeping, background or dormant.
• Are blocked.
• Have a command type of "WAITFOR".

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.

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.

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.

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).

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.

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.

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.

• From the Column list, choose the column you want to base your filter on.

• From the Condition list, choose a condition to apply to the column. You can use the percent sign (%) wildcard with the is like/is not like condition.

  You cannot use wildcard characters for the Database name when connected to a SQL Server 2000 instance.

• In the Value field, type a value to filter events by.
• Select Filter results.

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.

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.

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

1. Click Top SQL

   

   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.

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.