SQL Profiler and SQL Trace

The death knell may have been sounded for SQL Profiler and SQL Trace, but it still may be a while before we completely stop using them. I was looking at the functionality provided with the for Server side traces and concluded that the feature was never really completed! For instance comparing SQL trace to Perfmon/System Monitor here are a few things that SQL Trace doesn’t do.

With Perfmon you can create counter collections and then start/stop those collections manually or through a schedule. With SQL Profiler/SQL Trace this functionality has never been included. The usual way to schedule a trace is to write/script the trace definition and then include that in a SQL Agent job step. Unfortunately many SQL Trace definitions will be too large to be included in a job step so you may end up creating a stored procedure that will create the trace and call that.

SQL Server trace definitions don’t survive a restart of SQL Server unless you write your own method to re-create them.

If you stop and re-start a Perfmon counter collection, it will automatically append a number to the file name, if you stop a SQL Server Trace and the re-start, then the same filename is used. This forces you to drop the trace and re-create it each time you wish to create a new file.

It’s a wonder how such a pivotal tool such as SQL Trace/Profiler has ever been left in such a state. It hasn’t really been significantly enhanced since the original release (The SQL 2005 interface rejig and perfmon file imports excepted!). If Profiler was released today I suspect it would be on Codeplex and not part of the product.

With Extended Events slated to replace SQL Trace it was a bit worrying that the initial release with SQL 2008 the only way to manage them was with T-SQL, later a SSMS add in was released on Codeplex, and the beginnings of a management feature is present in Denali CTP1, but nothing like  the functionality of SQL Profiler. This is a bit disappointing as the potential for extended events could be so much greater than SQL Trace/SQL Profiler.