Based on popular demand from my colleagues and you, the kind reader, I am currently writing a series of posts on the SQL Server Profiler. The first part was an introduction attempting to answer the question – What is a SQL Trace? Permissions, space requirements and launching the Profiler. The second part dealt with Profiler Templates, Template Types & creating custom templates. Today, we will attempt to answer the following questions:

1. How to “execute” a basic Profiler trace?
2. Which are some of the usability features that may be of use during trace execution?
3. Can the Profiler be manipulated via the keyboard?

Creating a basic Profiler trace

To execute a Profiler trace, the first step is, obviously to launch the Profiler. This can be done by following the steps in my first post – What is a SQL Trace? Permissions, space requirements and launching the Profiler.

Next, go to File –> New Trace to launch the “Trace Properties” window once successful authentication takes place.

General Properties

Trace Information (# 1 and 2)

You can provide a name to the Profiler trace for easier future reference. You can also choose the template type to suit the exercise. For this test though, let’s choose the custom template created in my previous post – Profiler Templates, Template Types & creating custom templates.

Note that depending upon the connection created during authentication, the system shows the Trace Provider name, Provider Type and Version.

Saving the trace output (#3)

Normally, a profiler trace is shown to the user on the grid. However, depending upon the number of events and data columns being captured, there is a performance overhead associated to this method. It is much better instead to dump the trace data to a trace file.

When saving to a file, the user has the option to set a maximum file size to ensure that the file does not grow out of control. When “Enable file rollover” is checked, if the max. file size is reached, the existing file is closed and a new trace file is created. If the “Enable file rollover” is unchecked, the profiler stops capturing events when the max. file size is achieved.

As an alternate to saving trace data to a file, you may also want to dump the data to a table on any Microsoft SQL Server instance. This can be achieved by checking the “Save to Table” checkbox. Also, just as file-sizes can be limited, the number of rows in the trace table can be limited by specifying the max. row count in the “Set maximum rows (in thousands):”.

(Tip: It is advisable to store the trace data to another instance of Microsoft SQL Server to avoid interference with the activity being monitored.)

The “Server processes trace data” must be used with caution. This causes the service running the trace to process trace data instead of the client application. The drawback is that server performance may be affected.

Time-bound tracing (#4)

To have the SQL Server Profiler automatically stop tracing after at certain time, check the “Enable trace stop time” and specify the required stop date and time.

Event Selection

This page allows you to do the following:

1. Review the Events & data columns to capture
2. Add or Remove the Events to capture
3. Add or Remove data columns to capture
4. Apply one or more filters to one or more data columns
5. Specify data columns to Group the results on and also specify the order in which data columns should be shown to the user
6. Brief description of the event class selected (changes when event class selection changes)
7. Brief description of the data column (changes as the user hovers over the data column)

A step-by-step guide on customizing the template using the controls specified by #2, 3, 4 and 5 is available in my previous post – Profiler Templates, Template Types & creating custom templates.

Executing a trace

Once the Profiler trace is defined, one just needs to click on the “Run” button in the “Template Properties” window. The SQL Server Profiler will now start showing events to the user depending upon the chosen template.

1. Basic controls – start, pause & stop
2. Launch SQL Server Management Studio
3. Launch the Performance Monitor
4. Clear the trace data grid
5. Review the trace properties (The “Trace Properties” window opens, but in read-only mode if the trace is running or is paused)
6. Find a string within any of the data columns

Auto-scroll – a nice usability feature

Most of the usability features (launching of SSMS, PerfMon and the flexibility of the “Find” window) are quite straight-forward. However, there is one feature that I like the most. Look at the toolbar on the profiler, and note the icon with the grid and a little, blue down arrow:

This icon is the “Auto-scroll” button. When selected, the grid scrolls to the most recently collected data row as more and more data starts collecting on the grid. When not selected, the grid needs to be scrolled manually.

Typically, if I am trying to capture and analyze a trace for a particular database and host and want to see things as they happen, I prefer the enable “Auto-scroll”. If I am saving data to a file or a table, I would most probably be looking at the data later on and would therefore have the “Auto-scroll” turned off.

Can the Profiler be Manipulated via the Keyboard?

Of course, it can! Here’s where you can find a list of all keyboard shortcuts – http://msdn.microsoft.com/en-us/library/ms174164.aspx

In my next post…

SQL 2012 is just around the corner. Most of the community members are probably evaluating SQL 2012. For those who are not yet ready, but will ultimately move to SQL 2012 must most definitely be moving to SQL Server 2008 R2. We will see how to use the Profiler to identify which objects are in most dire need of replacement for certification against a release of SQL Server.

We would be approaching the end of the series and would therefore start looking at other scenarios where the Profiler can be used.

References

• Keyboard Shortcuts
• How to create a trace (SQL Server Profiler)?

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!