SQL Server Profiler How-To Guide by Mike Prince

A Developers Guide to SQL Profiler
By Mike Prince

SQL Profiler is powerful tool for any MS SQL database developer. Adding it to your toolset opens up a whole new world of debugging and performance tuning capabilities by providing a deep insight into what your code, and database, is actually doing. Whether you are developing with hand-coded T-SQL, an OR Mapper (LLBL Gen, etc.), or a component that generates database commands for you, the Profiler can help diagnose many mysteries and help pinpoint performance problems.

How does it work? It's pretty simple in concept. The Profiler receives a copy of all statements issued to the database after they have executed. This allows you to see the SQL statment and it's duration, CPU, and I/O used during runtime.

Let's take a quick peek at the Profiler. If you are using SQL 2005, you will find it under Start > Programs > Microsoft SQL Server 2005 > Performance Tools. If you are using SQL 2000, go to Start > Programs > Microsoft SQL Server and look for Profiler. I'll be using screen shots from SQL 2005 but they will still be relevant if you are using SQL 2000.

Possibly the most intimidating thing is simply opening the app. After you connect to your database, it's basically a blank slate. You eventually find File > New Trace but then you are presented with a somewhat cryptic interface. This is about as far as many developers go, but let's push on through and see what it has to offer. The easiest way to get going is to select 'Tuning' from the 'Use the template:" drop down box. This template will show you all stored procs and T-SQL statements as they run against the database in real-time. After selecting 'Tuning', click Run or File > Run Trace. Don't worry about the other fields on this form right now as they won't be needed for doing the basics.

Now that you have the profiler running you will see a single line that says 'Trace Start'. If you are running this against a busy development server or perhaps a production server then you may see hundreds of records a second. This tool can actually slow down a busy database server a bit so be careful about running it against a production machine during peak times. If you need to slow down the onslaught of records, stop the trace and click on the Properties button in the toolbar. Click on the Events Selection tab and look for Column Filters and you will see the screen shot below. Select DatabaseName and expand the 'Like' item in the tree and type in AdventureWorks and then click OK. Now press the green run button or select File > Run Trace. If you do not have AdventureWorks installed just follow along using your own database and change the sample queries as necessary. In SQL 2000, you will need to figure out your database id by running this query and then filtering by DatabaseID:

 
	/* for SQL 2000 */
	use AdventureWorks 
	select db_id()

We're in business now. The profiler is running, we've applied a filter so that we can see just want we need to. Now what? In this case, we'll fire a couple of test queries directly against the database, but in real life, here is where you would fire up the app that is not doing what you want or expect. For this example, let's open up SQL Server Management Studio (SQL 2005) or SQL Query Analyzer (SQL 2000) then connect to the AdventureWorks database and run the following query:

 
	select * from Person.Address where City like 'Orlando'

Return to the Profiler and you should see something similar to the screen below. The most important columns will be the TextData and the Duration. In this case, TextData is exactly what we entered into the query window. In other cases, this will be the stored proc or T-SQL statement as generated by some component in your application. The Duration is in milliseconds and in this case it is 4ms.

Hopefully this helps in understanding the power of this application. I develop mostly web applications and tend to fire up SQL Profiler nearly every day for one purpose or another. The next time you are puzzled by how something works, or doesn't work, in your database application, fire it up and get a clear view of the traffic between your app and your database. There are a ton of other events and columns that can help you with various problems so go ahead and dive in. You won't be sorry.