Spotlight on SQL Server 10.0.3 - Getting Started Guide

Top SQL Statements Drilldown

Note: This feature is not available for SQL Server 2000.

Use the Top SQL Statements drilldown to diagnose workload issues by displaying SQL statements that match particular criteria.

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.

The Top SQL Statements drilldown can be used as an alternative to the SQL Analysis drilldown.

To see the Top SQL Statements drilldown

1. Select a SQL Server connection from the Spotlight browser.

2. Click Monitor | Top SQL Statements.

   

Show all

To investigate...	Look at the...
Top SQL statements	Top SQL Statements drilldown	Top SQL Statements grid 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. Tip: 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. SQL Statement Shows the SQL statement of the currently selected row. 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. Query Plan 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.

Filter SQL statements

Refine the results displayed in the Top SQL Statements grid.

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.

Tip: 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."

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.

Related Topics

Spotlight on SQL Server Drilldowns (page 1)

About Spotlight Charts (page 1)

About Spotlight Grids (page 1)