Profiling on SQL Server Express

I’m working on a new web app using ASP.NET MVC 3 and Entity Framework 4.1. One thing lacking in EF 4.1 is the ability to monitor the actual queries sent to the SQL database, like you could do with Linq2SQL’s DataContext.Log.

The machine I’m working on has SQL Server Express. The Express version doesn’t come with the SQL Profiler. So without Profiler and without a log function on Entity Framework’s DbContext, what’s a developer to do?

Well, as it turns out, profiling is part of the SQL Server (any version, including Express), the SQL Profiler application is just one way to access the profiling functionality. Some people even argue it’s better to do the profiling on the server itself, as opposed to through SQL Profiler.

Starting the trace

How do you start? The easiest way to start is (you’re not going to like this) by using SQL Profiler. That’s what I did (on another machine). It’s explained on the website linked above, here are the steps:

1. Open up Profiler and create a new trace.
2. Select Save to File and select a location (it doesn’t matter where, we will be changing this). Select Enable File Rollover.
3. Choose your events and columns from the Events Selection tab.
4. Run the trace and then stop it right away.
5. From the File menu, choose Export > Script Trace Definition > For SQL Server 2005 (or whichever is appropriate in your environment) and save the script to file.
6. Open your file in SSMS, making sure you’re connected to the instance you want to profile.

This will yield a SQL script similar to the following:

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 10/13/2011  01:10:39 PM         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'c:\SqlTrace\Trace', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 12, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

When you execute the above SQL in SQL Server Management Studio (SSMS), it will return one row of data with a column named “TraceID” (an integer). You’ll need this TraceID to stop the trace later on, so take note of it.

Stopping the trace

You can interact with the trace using the sp_trace_setstatus stored procedure. To stop the trace, execute the following SQL in SSMS and replace “@TraceID” with the number returned from the previous query.

exec sp_trace_setstatus @TraceID, 0

Delete the trace definition

Stopping the trace doesn’t delete it. By deleting I don’t mean to delete the trace file (like “c:\SqlTrace\Trace” in the query above), but deleting this trace’s definition from the SQL server. The following query will delete the trace from the SQL server (again, replace “@TraceID” with the trace ID you got from executing the first query mentioned above):

exec sp_trace_setstatus @TraceID, 2

Read from the trace

If the above went well, you will now have a trace file (like c:\SqlTrace\Trace.trc). If you open it in NotePad, you’ll notice it’s not in a plain text or other readable format. So how can you read from the trace without needing SQL Server Profiler? Pretty simple, using fn_trace_gettable. Execute the following query in SSMS to read from the trace file:

SELECT *
FROM ::fn_trace_gettable('c:\SqlTrace\Trace.trc', default)

Oops, forgot the Trace ID?

Don’t worry, run the fn_trace_getinfo function. The first column lists all existing TraceIDs.

SELECT * FROM ::fn_trace_getinfo(0)

And that’s it! You can now profile any SQL Server version, be it Express, Standard or Enterprise, without using SQL Profiler.