How often do you find yourself with the need to know what statements are occurring on your SQL Server?  Well, if you're like me the answer is "ALL THE TIME".  Constantly Im asked about processes running on the server if things are slow.  Even more importantly, I like to capture data from the system to use for trending purposes and to proactively diagnose performance problems before someone comes and tells me about it.  Luckily, SQL Server comes with tools to capture such data, and to save it for later use.  The tool is SQL Server Profiler, and it is wonderful for setting up trace files to save data so that you can report on the happenings on the server.

To start a new SQL Server Profiler session, you can click on the Tools menu and select it from the downdown (so long as you're not using SQL Server Express edition - Profiler does not come with Express).

Selecting Profiler from the menu brings up the SQL Server Profiler application.  At this point, you'll want to create a New Trace.  This can be done from the File menu, or by pressing Control + N.

A Bit about Traces
A trace is a SQL Server object that captures information regarding certain events that occur on the database instance.  The information traces gather can be as simple as catching stored procedure executions to as complex as catching deadlock graphs.  Whats great about them is that you can select certain columns of data as well as filter for certain events.  These traces are absolutely imperative for any SQL Server DBA to use, and are extremely handy for diagnosing problems on the server, such as performance problems.  SQL Server Profiler is a graphical user interface (GUI) for creating a grouping of traces.  It makes it super easy to define them, alter them, and save them.  However, if you want to define your own custom trace files through TSQL, I show you how in this article.

To run a new trace, you'll first need to connect to an instance of SQL Server.  If you're using 2005 or 2008 a screen similar to the one below should appear.  You can type your instance name (your server name if it is the default instance) or select your instance from the dropdown box.

Once I am connected to my server, I can define the Events, Columns and Filters I want to use in my trace.  SQL Profiler comes with a handy set of predefined trace templates that you can use to capture common data.  For the most part, these are great if you have a limited set of needs for your trace file. However, if you want more specific information you're going to need to define your own trace or alter some of the predefined templates. 

In the trace properties screen I have the option to save the trace data to a file or to a table, or both.  If you want to save the data you capture, here is a good place to do it.

Clicking on the Events Selection tab I can set filters for my trace events.  For example, in the screenshot below I am setting criteria to only capture events where the event is intitiated by the LoginName "thedude".  More realistic filters may include looking for logical Reads > 1000 or a duration of a query longer than 2 seconds.

In the same screen I can decide which columns of data are important to me.  If I am capturing information regarding statements running, stored proc execution, etc then I want to know such informaiton as the name of the procedure, statement running, duration, reads, writes, etc.  I can use this information to find those procedures which may be resource hogs.

I can also easily organize the order in which the columns appear.  I can also group them, which I appreciate doing after I've captured the trace as it allows me to group the durations and reads of statements to easily identify longer running statements.

Below is a sample output of what statements were captured on my system while I was running the trace file.  Notice the amount of detail that is showed through the trace.  I am easily able to capture the Reads, CPU, SQL Statement, and Duration of the statement just through the use of the trace file.

Once you have your trace defined the way you want it, you have a number of options as to how you want to save it.  You can save the results from the trace to a File (flat or XML) or a Table.  I like to use the Table option since it allows me to view the data in a format to which I am accustomed to using, and so I can easily query it how I want.  What is also great about the Save option is that I can save the definition of the trace file so that I can use it later.  From there I can view the TSQL which comprises the trace so that I can save it later.  I use this method when I want to quickly define a trace, and then create a stored procedure that runs on system start up to start the trace.  Doing so make sure I can automatically capture events without having to go through the manual interaction to do so.

Conclusion
Today I took a broad overview of what SQL Server Profiler is, what it does, and what you can use it for.  I hope it is a great place to get started opening the window to the world on your SQL Server.  However, care must be taken when using the tool.  If you try to capture too many events then you're risking slowing down your server because there is overhead in capturing the events.  In a later writing, I plan to dig a little more deeply into these issues, and how you can use Profiler to diagnose some common performance problems on your server.