Microsoft SQL Server comes with a host of development and administrator features that warrant a separate IDE – the SQL Server Management Studio (SSMS). While the SSMS allows a developer or an administrator to write and debug T-SQL code and manage and maintain SQL Server instance(s), there is a requirement for a diagnostic tool that can be used for:

1. Troubleshooting
2. Debugging T-SQL code
3. Performance monitoring & analysis
4. Audit and review activities occurring against an instance of the Microsoft SQL Server
5. Correlate performance counters for advanced troubleshooting

SQL Trace

All of this is achieved by “tracing” the activities of the SQL Server Database engine. Such an instance is called a “SQL Trace”. A SQL Trace is most commonly described by using the following terms. We will be using these as we talk more about the SQL Server Profiler

• Trace = A collection of events & data returned by the SQL Server database engine
• Events = An Event is the occurrence of an event within the SQL Server Database engine
• Event Class = A type of event that can be traced. The event class contains all of the data columns that can be reported by an event
• Event Category = A group of related Event Classes
• Data column = An attribute of an event
• Filter = Criteria that limit the events collected on a trace

Permissions

Any user requiring to run a SQL Trace using SQL Server Profiler must have the ALTER TRACE permissions on the SQL Server instance. Talking about permissions, it is important to remember that such users will be able to see sensitive information and therefore such access must be restricted to members of the db_owner fixed database role, or members of sysadmin fixed server role.

Access to trace data – Minimum disk space requirements

For efficient access to the trace data, SQL Server Profiler uses the path specified by the TEMP environment variable to store the trace data. It is required that SQL Server Profiler has a minimum 10MB of free space. If by chance, the free space drops below 10MB, all SQL Server Profiler functions stop.

Launching the SQL Server Profiler

Now that we have the basics, let’s see the 3 ways by which we can invoke SQL Server Profiler.

Via Command Prompt

This has got to be the most used method to launch the SQL Server Profiler. One can go to the “Run” prompt or launch the command prompt and type the following based on the version of Microsoft SQL Server Profiler that one wants to launch:

• profiler90 = Profiler for Microsoft SQL Server 2005
• profiler = Profiler for Microsoft SQL Server 2008/2008 R2/SQL 2012 (code named: “Denali”)

Via SQL Server Management Studio (SSMS)

There are 2 ways in which the Profiler is launched from the SSMS:

Via the toolbar

1. Go to Tools –> SQL Server Profiler

NOTE: No SPID filtering is performed.

Via the Query editor window

1. In the Query editor window, right-click
2. Choose “Trace Query in SQL Server Profiler” (As an alternate to the above steps, you can use the keyboard short-cut: Ctrl+Alt+P)

NOTE: Note that the SPID being traced by the Profiler is automatically set to the SPID of the Query editor window.

Via Activity Monitor

1. Launch the Activity Monitor (refer my post: Activity Monitor – Underappreciated Features of Microsoft SQL Server)
2. Under the “Process” pane, select the activity of interest
3. Right-click and choose “Trace Process in SQL Server Profiler”

NOTE: Note that the the SPID being traced by the Profiler is automatically set to the SPID selected in the Processes pane.

In my next post:

In my next post, I will be looking at Profiler Templates, Template types and how to create customized templates.

References:

I have tried to provide the reader with the essentials to get started with SQL Server Profiler. The reader may refer the following Books On Line pages for advanced reading.

• Typical Uses of SQL Server Profiler
• SQL Trace Terminology
• Permissions Required to Run SQL Server Profiler
• Improving Access to Trace Data

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!