LANGUAGES: C# | VB.NET
ASP.NET VERSIONS: 1.0 | 1.1
Microsoft SQL Profiler
Optimize Your SQL Server Operations
By Ken McNamee
I am always amazed by the number of developers I run into who either haven t heard of SQL Profiler, or have heard of it and simply never used it. In short, SQL Profiler is a tool that allows you to discover and diagnose problems with a SQL Server 2000 database. Running a trace using SQL Profiler will identify all the SQL statements being run, the stored procedures being called, and the tables being accessed as well as the amount of time and CPU usage consumed by these operations. In addition to performance metrics, SQL Profiler can be used to identify which machines and/or users are executing statements. The amount of data that can be generated by SQL Profiler can be overwhelming. However, SQL Profiler also allows you to fine tune the results by selecting only relevant data columns and defining filters that limit the results.
General Tracing Options
You begin a SQL Profiler trace by connecting to a SQL Server 2000 server and fine tuning the trace using the Trace Properties dialog box (see Figure 1). This is the most important dialog box in SQL Profiler because it allows you to completely control which operations get traced, what data gets collected, and where the tracing data gets stored.
Figure 1: Use SQL Profiler s Trace Properties dialog box to define which SQL operations to trace, how to trace them, and where to store the results.
SQL Profiler can use either pre-defined or ad hoc trace templates to do its thing. Several handy templates are included with SQL Profiler. The SQLProfilerSP_Counts template tracks the stored procedures that are executed, as well as how many times they have been run. This trace gives you a better idea where to focus your optimization efforts. SQLProfilerTSQL_Replay is an amazingly useful template that allows you to capture all the data necessary so that the trace can be replayed again. The benefit to this template is that you can get a performance baseline for your application or a set of functionality. Then you can make performance adjustments and replay the trace to compare the results. You can even run the trace on a SQL Server different from the one it was recorded against, as long as certain requirements are met.
Another tracing option is the ability to save the trace results to a file or even a SQL Server table, thus allowing you to maintain a reportable tracing history. Finally, on the General tab, you can set the stop time for the trace. This allows you to somewhat run the trace unattended, although there are better and more convenient ways to schedule traces, such as by setting up a schedule to execute the sp_trace_create stored procedure.
Events and Filters
Tracing events are ordered by categories such as Cursors, Locks, Stored Procedures, and Transactions, just to name a few. An example of an event is when a stored procedure has completed executing, a cursor has opened, or a deadlock has occurred. You can also track such things as failed user logins, event log activity, and when a table scan has begun. There are many more events, but most of the time you, as a developer, will be concerned with the execution of SQL statements, stored procedures, and table access. The most important metric you will most likely want to know is the duration of the operation and how taxing it was on the server s resources. Knowing this information will allow you to begin improving these numbers by tweaking such things as joins, indexes, and even table structures. One important thing to remember is to only track the events in which you are absolutely interested. Tracking events can put a performance strain on SQL Server and possibly skew your metrics if you overburden it. Also, you should try to not use SQL Profiler against a local SQL Server instance if you can help it, because this will most definitely skew the results.
If you are running a trace against a live production database or a non-production database that others are using, it may be beneficial to filter the trace information returned to only operations that your testing is initiating. This is where the Filters tab in the Trace Properties dialog box comes in. It allows you to only show operations that meet certain criteria. You can filter by such things as SQL or NT username, machine name, database name, duration time, error conditions, and table or stored procedure names. Even a simple unfiltered trace can fill up SQL Profiler with hundreds, or thousands, of result items in a matter of seconds, so you will quickly appreciate the flexibility of the filtering abilities. Properly filtered, your tracing results may look something like the trace results window shown in Figure 2. This shows two operations: one as a plain SQL statement, and the exact same statement compiled as a stored procedure. The metrics demonstrate the performance gain of the stored procedure because it took less time to execute, used less CPU time, and made less data reads.
Figure 2: SQL Profiler s trace results window is where you can view the performance metrics related to the SQL operations that triggered the tracing events.
As you can see, SQL Profiler is a very powerful tool for debugging and diagnosing problems with your SQL Server code. In my opinion, a database programmer who doesn t use SQL Profiler is like a Visual Studio.NET developer who doesn t make use of its respective debugging capabilities. SQL Profiler is a tool with a deep array of options. Most of the time you ll not need to do anything other than a simple trace, but you should try to investigate as many of its features as possible. Some day those features may just turn a stomach-churning five-hour debugging session into a five-minute no brainer.
Ken McNamee is a Senior Software Developer with Vertigo Software, Inc., a leading provider of software development and consulting services on the Microsoft platform. Prior to this, he led a team of developers in re-architecting the Home Shopping Network s e-commerce site, http://www.HSN.com, to 100% ASP.NET with C#. Readers can contact him at [email protected].