Table of Contents Previous Next


11 SQL Profiling and Analysis : 11.1 Creating a New SQL Trace

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 at a later time. You can use SQL Profiler to create and store up to 15 named traces; use menu options to create and manage traces.
You can use the Create trace... dialog to define a SQL Trace for any database on which SQL Profiler has been installed and configured. installed and configured. To access the dialog, highlight the name of the database in the PEM client tree control; navigate through the Management menu to the SQL Profiler pull-aside menu, and select Create trace....
C:\Users\susan\AppData\Local\Temp\vmware-susan\VMwareDnD\10287ac6\sp_create_new_trace.png
Use the fields on the Trace options tab (see Figure 11.1) to specify details about the new trace:
Click in the User filter field to specify the roles whose queries will be included the trace; optionally, check the box next to *Select All* to include queries from all roles.
Click in the Database filter field to specify which databases to trace; optionally, check the box next to Select All to include queries against all databases.
Specify a trace size in the Maximum Trace File Size field; SQL Profiler will terminate the trace when it reaches approximately the size specified.
Specify Yes in the Run Now field to start the trace when you select the Create button; select No to enable fields on the Schedule tab.
C:\Users\susan\AppData\Local\Temp\vmware-susan\VMwareDnD\149132e7\sp_create_new_trace_schedule.png
Use the fields on the Schedule tab (see Figure 11.2) 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.
Specify Yes in the Repeat? field to indicate that the trace should be repeated every day at the times specified; select No to enable fields on the Periodic job options tab.
C:\Users\susan\AppData\Local\Temp\vmware-susan\VMwareDnD\4b67997b\sp_create_new_trace_periodic_job.png
Fields on the Periodic job options tab (see Figure 11.3) specify scheduing details about a recurring trace. Use fields in the Days section to specify the days on which the job will execute:
Click in the Week days field to select the days of the week on which the trace will execute.
Click in the Month days field to select the days of the month on which the trace will execute.
Click in the Months field to select the months in which the trace will execute.
Use fields in the Times section to specify a time schedule for the trace execution:
Click in the Hours field to select the hours at which the trace will execute.
Click in the Minutes field to select the hours at which the trace will execute.
When you've completed the Create trace... dialog, click Create to start the newly defined trace or to schedule the trace for a later time.
C:\Users\susan\Desktop\sp_open_existing_trace_sptab.png
To view a previous trace, highlight the name of the profiled database in the PEM client tree control; navigate through the Management menu to the SQL Profiler pull-aside menu, and select Open trace.... You can also use the SQL Profiler toolbar menu to open a trace; select the Open trace... option. The Open trace... dialog opens as shown in Figure 11.5.
C:\Users\susan\AppData\Local\Temp\vmware-susan\VMwareDnD\9599b1f0\sp_open_existing_trace.png
Highlight an entry in the trace list and click Open to open the selected trace. The selected trace opens in the SQL Profiler tab.
Click the Filter icon to open the Trace Filter dialog and create a rule (or set of rules) that define a filter. Each rule will screen the events within 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 the Open button; to define a new filter, click the Add (+) icon to add a row to the table displayed on the General tab and provide rule details:
Use the Type drop-down listbox to specify the trace field that the filter rule will apply to.
Use the Condition drop-down listbox to specify the type of operator that SQL Profiler will apply to the Value when it filters the trace:
o
Select Matches to filter events that contain the specified Value.
o
Select Does not match to filter events that do not contain the specified Value.
o
Select Is equal to to filter events that contain an exact match to the string specified in the Value field.
o
Select Is not equal to to filter events that do not contain an exact match to the string specified in the Value field.
o
Select Starts with to filter events that begin with the string specified in the Value field.
o
Select Does not start with to filter events that do not begin with the string specified in the Value field.
o
Select Less than to filter events that have a numeric value less than the number specified in the Value field.
o
Select Greater than to filter events that have a numeric value greater than the number specified in the Value field.
o
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.
o
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 that SQL Profiler will search for.
When you've finished defining a rule, click the Add (+) icon to add another rule to the filter. To delete a rule from a filter, highlight the rule and click the Delete icon.
Click the Save button to save the filter definition to a file without applying the filter; to apply the filter, click OK. Select Cancel to exit the dialog and discard any changes to the filter.
To delete a trace, highlight the name of the profiled database in the PEM client tree control; navigate through the Management menu to the SQL Profiler pull-aside menu, and select Delete trace(s).... You can also use the SQL Profiler toolbar menu to delete a trace; select the Delete trace(s)... option. The Delete traces... dialog opens as shown in Figure 11.6.
C:\Users\susan\AppData\Local\Temp\vmware-susan\VMwareDnD\16123d67\sp_delete_trace.png
C:\Users\susan\AppData\Local\Temp\vmware-susan\VMwareDnD\141a3b77\sp_deleted_trace.png
To view a list of scheduled traces, highlight the name of the profiled database in the PEM client tree control; navigate through the Management menu to the SQL Profiler pull-aside menu, and select Scheduled traces.... You can also use the SQL Profiler toolbar menu to the list; select the Scheduled traces... option.
C:\Users\susan\AppData\Local\Temp\vmware-susan\VMwareDnD\06130d85\sp_trace_details.png
The Scheduled traces... dialog (see Figure 11.8) displays a list of the traces that are awaiting execution. Click the edit button to the left of a trace name to access 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 the trace 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 execution of the trace.
The Created field displays the date and time that the trace was defined.

11 SQL Profiling and Analysis : 11.1 Creating a New SQL Trace

Table of Contents Previous Next