Spotlight on SQL Server 10.0.3 - Getting Started Guide

Turn on and configure SQL Analysis

1. Click Configure | SQL Analysis

   

2. Choose one of the following from the connection list:
   • To change the setting for an individual connection, select the connection name.
     • Select Override the default settings.
     • Select Enable SQL Analysis.
   • To change the setting for all connections, select Default Settings and select Override the default settings.
3. Click Data Collection.

4. Configure the following fields on the Data Collection page:

   Option	Description
   SQL Analysis is scheduled to collect every n minutes	Note: This feature is available only in Spotlight on SQL Server Enterprise. How often Spotlight collects SQL Analysis data. The default is six hours. To change how often Spotlight collects SQL Analysis data Click the link. Select Override the default settings for the collection 'SQL Analysis'. Under ‘Store reporting data in the Spotlight Statistics Repository’, click the Store data link. Set the collection schedule. Data can be collected at regular intervals or at a specific time on one or more days.
   Use continuous Rowset trace	Select to use Rowset trace to collect SQL Analysis data. Rowset trace is convenient though may affect throughput on busy servers. It should be used with care.
   Use sampling Server Side trace	Select to use sampling Server Side trace to collect SQL Analysis data. Server Side trace is more suitable for busy servers than Rowset trace. Server Side trace requires additional configuration.
   Run trace for or until trace file reaches	Server Side trace will run until one of these conditions is met. Specify the maximum amount of time Server Side trace should run for. Specify the maximum size of the trace file.
   Server trace file location (on the SQL Server host)	Type a location for the server trace file. The path is relative to the SQL Server instance. For example, C:\Trace Files.
   Retrieve data through SQL Server	Select to process trace files on the SQL Server instance. This option has the potential to affect throughput on busy servers. Use this option when there are permission or firewall settings preventing the Diagnostic Server machine from retrieving files from the SQL Server machine. This is the default option.
   Retrieve data from the file system	Select to process trace files on the Diagnostic Server. The Diagnostic Server machine must have the appropriate permissions required to connect to the SQL Server instance. This option is not available when configuring default settings.
   Trace file location (from Diagnostic Server host)	Type the location of the server trace files on the SQL Server instance. The location should be a shared folder the Diagnostic Server can connect to. For example, \\computername\Trace Files. This option is not available when configuring default settings.

Real-Time Data and Expensive SQL

You can use the SQL Analysis grid when load testing in a testing environment. It shows SQL Analysis data in real time.

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.

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

2. Click Monitor | SQL Activity.

   

3. Select SQL Analysis.

4. Do the following:

   To identify	Sort the grid by
   SQL that consumed the most CPU	Total CPU
   The average CPU consumed by a particular statement	Average CPU
   SQL that was executed the most	Execution Count
   SQL with the highest logical I/O	Average Reads

Tips:

• Use filters to refine the data shown in the grid. See "Filter Displayed and Collected Data" (page 1) for more information.
• Some columns are hidden by default. To view hidden columns, right-click the grid header row and select Organize Columns.

• Results are cleared when you leave the SQL Analysis grid. Click Clear Grid to clear the grid manually.

  

• To wrap a column onto multiple lines, right-click the data content of the grid and select Properties | Options.

  Note: Word wrap may degrade the performance of Spotlight.

• If Quest SQL Optimizer is installed, you can use it to tune the non-conforming SQL that has been identified. To do this, click Optimize SQL.

Filter Displayed and Collected Data

To filter the data displayed

Start from the data displayed in the SQL Analysis grid

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.
• To edit or delete a filter select it in the filter list and click the appropriate button.

To filter the data collected

1. Click Configure | SQL Analysis.

   

2. Choose one of the following from the connection list:
   • To change the setting for an individual connection, select the connection name.

     • Select Override the default settings.

       Select Enable SQL Analysis.

   • To change the setting for all connections, select Default Settings and select Override the default settings.

3. Ensure the Filters page is selected.

   • Click Add.

     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" operators. The other operators apply only to numeric values.

   Tips: 

   • Use the Arrow buttons to change the order of the filters.
   • 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.
4. You can specify the number and type of SQL Executions retrieved from the SQL Server instance
   1. Select the Advanced page.
   2. In the Aggregate the top n SQL statements field, enter the maximum number of SQL executions to be retrieved from the SQL Server instance.
   3. 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 IO.

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

Analyze Wait Statistics

When troubleshooting SQL Server performance issues, one area you can focus your investigation on is waits. Long wait times can be an indicator of performance bottlenecks so identifying where waits are occurring may assist you in your tuning efforts.

Spotlight not only provides an easy way to see if your SQL server has a problem with waits but also identifies on which particular resource waits are occurring. Spotlight takes the raw data from SQL Server and automatically calculates the rate of wait over time so you have a more current perspective.

To start your investigation into waits, go to the SQL Activity drilldown and click the Wait Statistics tab.

The Waits Detail grid shows detailed waits statistics for the SQL Server instance being monitored. It shows all wait types in SQL Server and breaks down the statistics into signal time and resource wait time. (SQL Server provides only wait time and signal wait time.) By default, the Waits Detail grid is sorted by Wait Time Rate (ms/s) which allows you to immediately see which wait types are being waited on right now.