Spotlight on SQL Server 10.0.3 - Getting Started Guide

Filter SQL Analysis Data

Filter what is displayed

Refine the data displayed in the SQL Analysis - Workload view and the SQL Analysis grid (SQL Activity drilldown) using local filters. Local filters do not affect the data retrieved from the SQL Server instance, they affect what data is displayed in the view and grid. The local filters for the grid and view are set independently of each other.

To filter the data displayed

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

2. Click Monitor | SQL Activity.

   

3. Select the SQL Analysis - Workload view or SQL Analysis.

4. Click Change Filter.

   

5. Click Add.
6. From the Column list, choose the column you want to base your filter on.
7. 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.
8. In the Value field, type a value to filter events by.
9. Select Filter results.

Tips:

• You cannot use wildcard characters for the Database name when connected to a SQL Server 2000 instance.
• To turn off filtering, clear the Filter results checkbox.
• You can edit or delete a filter by selecting it in the filter list and clicking the appropriate button.

Filter what is collected

Use filters to reduce the amount of SQL Analysis data Spotlight collects. Specify what data is retrieved from the SQL Server instance.

To filter the data collected

1. Click Configure | SQL Analysis.

2. Choose one of the following from the connection list:

   • To change filtering for an individual connection, select the connection name.
   • To change filtering for all connections, select Default Settings.
3. On the Filters page, click Add.

4. Set the filter properties.

   Tips:

   • You can use the percent sign (%) and underscore (_) as wildcards.
   • You cannot use wildcard characters in the Database name field when connected to a SQL Server 2000 instance.
   • When filtering on a string field such as DatabaseName or ApplicationName, use only the "LIKE" and "NOT LIKE" conditions. The other conditions apply only to numeric values.
5. Use the Arrow buttons to change the order of the filters.

Tips:

• Edit or delete a filter by selecting it in the filter list and clicking the appropriate button.
• Filtering affects the average, minimum, maximum, and last values displayed in the grid, as calculations are performed only on data retrieved from the SQL Server instance.
• To turn off filtering, on the Filters page, click Clear. This removes all filters.

To specify the number and type of SQL Executions retrieved from the SQL Server instance

1. On the Advanced page, in the Aggregate the top n SQL statements field, enter the maximum number of SQL executions to be retrieved from the SQL Server instance.
2. From the Sorting by list, specify the criterion you want Spotlight to use to determine what the "top" SQL statements are. Choose from Average Duration, Average CPU, or Average Reads and Writes.

Spotlight aggregates the data collected and then stores only the aggregated data.