Logging query text without Profiler

Posted on

Question :

I’m in the process of porting an old, semi-broken ASP.NET/SQL Server application to PHP/PostgreSQL. Among the torments^H^H^H^H^H^H^H^Hchallenges I’m faced with is that the reporting is done by a series of binary executables, because apparently I’ve been a bad person and need to be punished. So, I don’t have access to the queries in order to port them.

That’s fine, I thought. I can run the query profiler and log all SELECT statements that occur during the nightly report generation.

Except it turns out that the original developer shaved the cost down by running SQL Server Workgroup Edition, which doesn’t include Profiler.

I was considering using triggers to get these queries, but I think I can’t use triggers on SELECT statements. Reverse engineering the queries is possible, but given the complexity of the database (multiple, often recursive, relationships between tables, loads of similarly-named tables where several haven’t been updated in years, and so on) I would prefer to have canonical queries to reference.

How should I go about this? Is there any way to get Profiler running in Workgroup Edition? Is there a way to enable query logging without the profiler? I’m not worried about performance because this app doesn’t get particularly heavy use and it’s running on a machine that’s two sizes beefier than the app requires.

Answer :

Several options:

  1. You could use SQL Server Profiler against the Workgroup Edition server from a different client tools installation of SQL Server Developer, Standard, Enterprise or Evaluation Edition.

  2. Use the semi-documented trace flag 4032, as explained by Tom LaRock.

  3. Evaluate third party tools like the free DataWizard SQL Performance Profiler, xSQL Profiler (free for one instance), or ExpressProfiler (BSD Licence).

My preference would probably be option (1), downloading Evaluation Edition if necessary.

If you tell Profiler not to filter out its own queries, you’d probably see it calling these stored procedures:

As described by the documentation, you execute the procedures in this order:

  1. Create a trace by using sp_trace_create.
  2. Add events with sp_trace_setevent.
  3. (Optional) Set a filter with sp_trace_setfilter.
  4. Start the trace with sp_trace_setstatus.
  5. Stop the trace with sp_trace_setstatus.
  6. Close the trace with sp_trace_setstatus.

You will end up with a .trc file that you can then open in SQL Profiler or read with fn_trace_gettable.

Here’s an example script to start a trace:

declare @rc int
declare @TraceID int

-- create the trace
exec @rc = sp_trace_create @TraceID output, 0, N'trace-filename'

-- set which events to capture
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on

-- start the trace
exec sp_trace_setstatus @TraceID, 1

Use the built-in dynamic management views. AFAIK, they work just fine in Workgroup Edition.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC


Leave a Reply

Your email address will not be published. Required fields are marked *