Using SQL Profiler v10

Suggest edits

The SQL Profiler extension allows a user to locate and optimize inefficient SQL code. Microsoft's SQL Server Profiler is very similar to PEM’s SQL Profiler in operation and capabilities.

SQL Profiler works with PEM to allow you to profile a server's workload. You can install and enable the SQL Profiler extension on servers with or without a PEM agent. However, you can run traces only in ad hoc mode on unmanaged servers and you can schedule them only on managed servers.

SQL Profiler captures and displays a specific SQL workload for analysis in a SQL trace. You can start and review captured SQL traces immediately or save captured traces for review later. You can use SQL Profiler to create and store up to 15 named traces.

Permissions for SQL profiler

To access the SQL profiler tool on PEM, there are two prerequisites:

  1. The user logged in to the PEM GUI must either be a superuser or a member of group pem_comp_sqlprofiler within the PEM server database. For more information, see pem groups.
  2. The user configured for the database server in the server tree (Username), must be a superuser on the database server that the trace is being run on (monitored server).

Creating a trace

You can use the Create Trace dialog box to define a SQL trace for any database on which SQL Profiler was installed and configured. To open the dialog box, select the database in the PEM client tree and select Tools > Server > SQL Profiler > Create trace.

Use the Trace options tab to specify details about the new trace:

  • Provide a name for the trace in the Name field.
  • Use the User filter field to specify the roles whose queries to include in the trace. Select Select All to include queries from all roles.
  • Use the Database filter field to specify the databases to trace. Select Select All to include queries against all databases.
  • Specify a trace size in the Maximum Trace File Size field. SQL Profiler terminates the trace when it reaches approximately the size specified.
  • Select Yes in the Run Now field to start the trace when you select Create. Select No to enable fields on the Schedule tab.

Use the Schedule tab to specify scheduling details for the new trace:

  • Use the Start time field to specify the starting time for the trace.
  • Use the End time field to specify the ending time for the trace.
  • Select Yes in the Repeat? field to repeat the trace every day at the times specified. Select No to enable fields on the Periodic job options tab.

Use the Periodic job options tab to specify scheduing details about a recurring trace. Use the Days section to specify the days when the job executes:

  • Use the Week days field to select the days of the week for the trace to execute.
  • Use the Month days field to select the days of the month for the trace to execute.
  • Use the Months field to select the months when the trace executes.

Use the Times section to specify a schedule for the trace execution. Use the Hours and Minutes fields to specify the time when the trace executes.

After you complete the Create Trace dialog box, select Create to start the trace or to schedule the trace.

If you execute the trace immediately, the trace results appear in the PEM client.

Opening an existing trace

To view a previous trace, select the profiled database in the PEM client tree and select Management > SQL Profiler > Open trace. The Open Trace dialog box opens.

Select an entry in the trace list and select Open to open the selected trace in the SQL Profiler tab.

Filtering a trace

A filter is a named set of one or more rules, each of which can hide events from the trace view. When you apply a filter to a trace, the hidden events aren't removed from the trace but are excluded from the display.

Select Filter to open the Trace Filter dialog box and create a rule or set of rules that define a filter. Each rule screens the events in the current trace based on the identity of the role that invoked the event or the query type invoked during the event.

To open an existing filter, select Open. To define a new filter, select Add (+) to add a row to the table displayed on the General tab and provide rule details:

  • Use the Type list to specify the trace field that the filter rule applies to.
  • Use the Condition list to specify the type of operator for SQL Profiler to apply to the value when it filters the trace:
    • Select Matches to filter events that contain the specified value.
    • Select Does not match to filter events that don't contain the specified value.
    • Select Is equal to to filter events that contain an exact match to the string specified in the Value field.
    • Select Is not equal to to filter events that don't contain an exact match to the string specified in the Value field.
    • Select Starts with to filter events that begin with the string specified in the Value field.
    • Select Does not start with to filter events that don't begin with the string specified in the Value field.
    • Select Less than to filter events that have a numeric value less than the number specified in the Value field.
    • Select Greater than to filter events that have a numeric value greater than the number specified in the Value field.
    • Select Less than or equal to to filter events that have a numeric value less than or equal to the number specified in the Value field.
    • Select Greater than or equal to to filter events that have a numeric value greater than or equal to the number specified in the Value field.
  • Use the Value field to specify the string, number, or regular expression to search for.

After you finish defining a rule, select Add (+) to add another rule to the filter. To delete a rule from a filter, select the rule and select Delete.

Select Save to save the filter definition to a file without applying the filter. To apply the filter, select OK.

Deleting a trace

To delete a trace:

  1. Select the profiled database in the PEM client tree.
  2. Select Management > SQL Profiler > Delete trace(s).
  3. In the Delete Traces dialog box, select the icon to the left of a trace name to mark one or more traces for deletion.
  4. Select Delete. The PEM client acknowledges that the selected traces were deleted.

Viewing scheduled traces

To view a list of scheduled traces, select the profiled database in the PEM client tree. Select Management > SQL Profiler > Scheduled traces.

The Scheduled Traces dialog box displays a list of the traces that are awaiting execution. Select Edit to the left of a trace name to see detailed information about the trace:

  • The Status field lists the status of the current trace.
  • The Enabled? switch displays Yes if the trace is enabled, No if it is disabled.
  • The Name field displays the name of the trace.
  • The Agent field displays the name of the agent responsible for executing the trace.
  • The Last run field displays the date and time of the last execution of the trace.
  • The Next run field displays the date and time of the next scheduled trace.
  • The Created field displays the date and time that the trace was defined.
← Prev

Installing the SQL Profiler extension

↑ Up

Profiling workloads

Next →

Using the Index Advisor