SQL Profiler Tips and Tricks
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.