Freigeben über

What happens to my orphan Profiler traces?

  • Artikel

Last week I was delivering a performance workshop to 10 people from different customers and one of them raised the following question:

“Say that I’ve launched Profiler, pointed it to a given instance of SQL and set everything up to collect a given set of events/columns. For whatever reason, that instance of Profiler either crashes unexpectedly or hangs and therefore stops processing any further events produced by the database engine. What will the server do with that trace session which has been left orphan with no client on the other end willing to consume any events produced?”

We ran a quick and simple experiment by just following the steps he had described and, to our surprise, we realized that the trace session remained set up and active on the server side even after we had killed the instance of Profiler.exe.

Re-running the query a few times against sys.traces didn’t show any difference. I mean, the trace still showed up as active in the results.

Today, I had a chance to look into the code to understand how was this implemented, and what could be the effect and impact leaving such a trace with a dead reader on the other end could have over the system being traced. The story goes like this…

During the creation of the first SQL Trace trace, SQL will create a hidden scheduler and it will enqueue a Trace Management Task. Among other duties, that task is responsible for, every one minute, trying to remove any expired traces. A expired trace being that whose stop time is lower than current system’s time (traces you have scheduled to finish at a given time) and those of rowset type (the ones created by Profiler) which are not active (i.e. are not consuming further events), have had to drop at minimum one event, and that first event drop occurred at least 10 minutes ago.

Comments

  • Anonymous
    November 02, 2011
    No entiendo por qué no elimina la traza huérfana la tarea de limpieza. No está activa : se entiende que no consume eventos?¿ y que por lo menos ha consumido uno hace más de 10 minutos.