Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
If you’re a developer who works with SQL Server, you probably already know
about SQL Profiler, the graphical tool that lets you capture and analyzer SQL
Server events in real time. If you’re not familiar with this tool, check out my
previous article, “Introduction to SQL
Profiler”. This time
around, I’m going to drill a bit deeper into this useful tool, offering you ten
more bits of SQL Profiler that you might not have noticed already.
1. Using Existing Templates
When you fire up SQL Profiler and tell it to create a new trace, it
pre-selects some events, data columns, and filters for you. Before you start
fine-tuning this selection, you should know that the SQL Server team has already
saved some useful starting points for you in the form of trace templates.
Instead of creating a new trace, select File, Open, Trace Template. SQL Server
ships with trace templates for various purposes including simply counting stored
procedures, tuning your SQL statements, and measuring the duration of T-SQL
statements.
2. Creating Your Own Templates
Of course, the built-in trace templates won’t be perfect for everything you
want to do. Sooner or later you’ll find yourself carefully crafting a SQL
Profiler trace that has the exact combination of events, filters, and data
columns that you need to diagnose some common problem within your own
organization. When you do, you can stash this combination away for the future as
a trace template of your own. Just stop the trace and select File, Save As,
Trace Template and assign it a memorable name. The next time you need the same
combination you can open up the saved template and have it instantly available.
3. Saving to a Table
One of the nice things about the SQL Profiler engine is that you can either
capture data for interactive analysis right on screen, or save it for later
inspection – and you don’t have to decide up front which of those things you’re
going to do. Better yet, you can save a trace in the most natural possible
place: right in a SQL Server table! When you’ve created a trace that warrants
keeping around for future analysis, stop the trace and select File, Save As,
Trace Table. You’ll be presented with the Connect to SQL Server dialog box so
that you can choose the server where you want to save the trace (this doesn’t
have to be the same server that you’re profiling). Then select a database and
either select an existing table or type the name of a new table, as shown in
Figure 1. Click OK to save the trace.
4. Replaying a Trace
Saving your traces to a table enables one of the other exciting features of
SQL Profiler: replaying traces. Select File, Open, Trace Table and choose a
server to connect to that has a saved trace table. Open the trace table. Now
you’ll find that the commands on the Replay menu are active. Choose Replay,
Start and SQL Profiler will let you choose a server to be the target of the
replayed activity. Figure 2 shows the options that you can set for a replay.
Why would you want to replay a trace? Suppose you’re debugging a problem with
one of your servers – say, clients are deadlocking when running a particular set
of queries. You can run SQL Profiler to capture a trace of the client activity
that includes the deadlocks, and then rework the stored procedures on the server
that you think are causing the deadlock. Replay the stored trace, and you can
see whether your fixes were effective in preventing the problem from happening
again.
To replay a trace, SQL Server must have certain event classes and data
columns in the trace. The easiest way to make sure you have the minimum set in
your trace is to start with the SQLProfilerTSQL_Replay trace template, which is
one of the ones that’s installed with SQL Server.
5. Using Breakpoints
When you’re replaying traces, you can use some standard debugging tools to
view selected events in slow motion. Place the cursor on any line in the trace
and then click F9 to set a breakpoint (or click F9 a second time to clear an
existing breakpoint). Then you can click F5 to run the trace to the next
breakpoint. At that point you can use the F10 key to execute the trace one
statement at a time. Alternatively, you can use Ctrl+F10 to execute all
statements up to the current cursor location.
6. Locating Deadlock Causes
If you’re having intermittent deadlock problems, it can be tough to figure
out where they’re coming from. This is especially true if your server is busy:
how do you even spot the deadlocks doing by? SQL Profiler can help you here. Set
up a trace and in the Events selection expand the Locks group, then select the
Lock:Deadlock and Lock:Deadlock Chain events. If you monitor for these two
events, SQL Profiler will produce a trace that contains details on just the
deadlocks on your server. Record whatever identifying information you want – for
example, the application name, logon name, and so on – and you’ll be well on
your way to figure out where the culprits are.
7. Auditing Logins
How about tracking the user activity on your SQL Server? You can use SQL
Profiler for this too. Again, the key lies in properly choosing the events that
you profile. Set up a trace that monitors the Security Audit:Audit Login and
Security Audit:Audit Logout events, and send it to a trace table. Then you’ll
have a persistent record right in your database of who was using the database
and when they were using it.
8. Watching for Table Scans
Another good use of SQL Profiler is to find queries that are causing table
scans on your server. You want SQL Server to be using indexes to find the data
that your users require – not to be looking through every row! To check, turn on
a trace with the MISC:Execution Plan event. This will capture the query
execution plan for every query. Then look over these plans for any that include
a “Table Scan” or “Clustered Index Scan” (which indicates that the server is
scanning all rows in the index, not that it’s using the index to find a
particular row). You can then examine those particular queries in more detail to
see whether adding additional indexes to your database’s tables could make them
more efficient in the future.
9. Using Keyboard Shortcuts
Like any other Windows application, SQL Profiler supports keyboard shortcuts
for more efficient use. If you use this tool frequently, you’ll probably want to
memorize some of the most useful ones:
- Ctrl+Shift+Delete to clear the current trace window
- Ctrl+F to open the Find dialog box
- F3 to find the next match
- Shift+F3 to find the previous match
- Ctrl+N to open a new trace
- F5 to start a replay
- Shift+F5 to stop a replay
- F9 to toggle a breakpoint
- F10 to single-step
- Ctrl+F10 to run to the cursor
10. Choosing Data Columns
Finally, remember that you don’t always have to accept the default data
columns for your traces. You might find that SQL Server suggests too much or too
little data for your tastes. In many cases, the NT User Name and application
name will be irrelevant for troubleshooting SQL issues, and you can remove them
from the data to avoid cluttering up the display. On the other hand, if you’re
tracing events for multiple databases, you’ll probably want to throw in the
database name. There’s a lot of other information available, from the duration
of the event to the name of the object affected by the current statement, so
take a look at the list before you just blindly click OK.
And Yes, There’s More
As you should be able to tell by now, SQL Profiler is a vital tool for
diagnosing SQL Server issues of all types. When you’re trying to figure out
what the heck is going on, especially with a heavily-loaded server, a
well-chosen trace can help you pick out just the key events that you need to
diagnose a problem. Spend some time to get acquainted with the events that
you can trace and the data that you can capture, and you’ll find many uses
for this tool in the future.
Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his recent book, Coder to Developer from Sybex. When
he’s not writing code, Mike putters in the garden on his farm in eastern
Washington state.
