The SQL Profiler is a great tool for monitoring and
analyzing SQL Server Performance. I use it all the time to watch the detailed
actions of a stored procedure, trigger or user-defined function (UDF) that I am
developing. It can also be used to monitor aggregate performance of an entire
SQL Server instance, a single database, or to isolate performance problems.
When you are interested in overall performance, using SQL Profiler over time,
by that I mean every day, improves your knowledge of your system and its
performance characteristics and provides the information you need to spot
trends and changes of behavior.
SQL Profiler is a graphical interface to SQL Server’s trace capability
but it is not the only way to run a trace. Calling a group of system stored
procedures, whose names all begin with the characters "sp_trace_",
can also create them. In fact, SQL Profiler uses these stored procedures
behind the scenes to do the tracing that you request.
Traces can be sent to the SQL Profiler window, a database table
or a sequential disk file. However, tracing is not resource free. In fact, it
can consume considerable resources, particularly if you send the trace to SQL
Profiler. Since the most important time to monitor is the time of maximum
server load, you do not want the act of measurement to slow the system
unnecessarily. For most applications that run in a business environment, peak
activity occurs in the middle of the afternoon.
The disk file is by far and away the fastest destination for
a trace and it interferes the least with the performance of SQL Server.
However, for analysis purposes, a table is the best destination. Once the data
is in a table, the information can be sliced and diced to your heart’s
content. Most importantly, trend reporting, using data from multiple days, is
possible.
This article presents a technique for writing a stored
procedure that creates a trace. To achieve the best performance the proc sends
its output to a file on disk. A second step moves the data from the trace file
into a SQL table for analysis. The second step doesn’t have to be performed
exactly when the data is recorded. In fact, it’s usually loaded overnight when
server utilization is low and then followed by any standard reports. By using
the file as the trace destination and loading the data overnight, this solution
does the best to minimize the peak-hour resources required for monitoring.
Although you could manually use the SQL Profiler to create a
trace at the same time every day and send it to a file, it is difficult to be
consistent and this is a task that is easily automated. By using the stored
procedure and a SQL Job that runs the stored procedure, you can run the exact
same trace at the same time of day, every day. This kind of data gathering
provides consistent information for effective performance analysis.
The most tedious part of the process is creating the stored
procedure that executes the trace. Fortunately, SQL Profiler will write almost
all of it for you. With a few modifications, it is easy to turn its script
into a stored procedure that can be run on a schedule.
