Is it possible to automate SQL Server 2008 profiler?

Question

There was a post regarding useful SQL tricks. Here I was going to mention the SQL Server Profiler tool, as it has helped me write less SQL. I would write SQL that would interrogate, understand or second guess the databases business logic.

Profiler is very useful, especially where application code has embedded SQL and you want to work out what it's doing, in the shortest time possible. (Also you may not know which source code version is used in the application's enviroment, or even worse, where there is no source code available!).

I was wondering if the profiler has an API I could hook into?

This would be very useful when we want to quickly check what SQL is called, within the system, by setting an environment variable/flag (PROFILER_ON=TRUE, for example). Then the system can kick off SQL profiler, setting various trace properties then writing out to a log or table - which could be viewed by the support team.

I want to write a component to switch profiler on and monitor the production environment (at quiet times) so can't really alter the codebase (both app code and SQL stored procs).

Answer 1

You can use System stored procedure to automate profiler. Check out the details of doing the same at http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

Answer 2

What you can do is set up your "perfect" trace and script it out as SQL. Then execute it to run the trace and save output to a file or table.

Now, you could wrap the SQL to create the trace in another stored proc that can be run via SQL Agent/idle threshold, but use fn_trace_getinfo to see if one if running already.

You can not automate it in SSMS or use an environment variable: the code, the job, the alerts etc all must exist on the server.

You can put the scripted trace into a batch file and run it via osql/sqlcmd but it has to be invoked manually.

There is no profiler API: all it does is run stored procs, just like any solution will have to.

Why can't you add monitoring SQL code hat is independant of the app code?

You can't use SMO either... only to read traces

Edit: a trace always requires SQL to be run against the server

Answer 3

You can utilize the Microsoft.SqlServer.Management.Trace namespace objects; they provide an API against the same functionality that SQL Profiler provides. This is an alternative against scripting your own sql against the database. However, just like SQL Profiler, what these objects do under the covers is to execute the SQL Trace stored procs (or variations thereof) to create, run and manage the traces.

If you use 1 profiler instance to profile another (or use a profiler to profile itself by removing the "NOT LIKE 'SQL Profiler...'" filter criteria from the trace (I think it's an Application Name filter) then you'll see exactly what it is that profiler is doing is the same as what you would do in SQL or what the SMO objects will do from an API.

Answer 4

GBN's got it right here; All SQL Profiler does is execute stored procedures on your SQL Server: these procedures start, configure, and stop traces. What that means is that instead of trying to automate the profiler, you just execute the same tracing procedures, and you'll get the same behavior.

To start and stop traces automatically, you can use SQL Profiler to create a script; that script can be executed from a batch file. So you'd do something like this;

• Open SQL Profiler
• Set up your desired trace in the trace profile; select the events you are interested in and the file you want to trace to.
• Start the trace, then stop it.
• Go to File | Export | Script Trace Definition... and save a .sql file

This .sql file contains code you can run; if you open it, you'll see this kind of code at the top;

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @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, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
This creates a trace that writes to a file.

This is creating a trace and attaching events to the trace.

So now, to start your trace, you can schedule the execution of this file. The batch file will look something like;

osql -E -S MACHINE\INSTANCE -i "c:\my-trace-definition.sql"