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 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.